Escape Characters in Oracle PL/SQL Queries

Oracle databases reserve some special characters with specific meaning and purpose within Oracle environment. These reserved characters include _ (underscore) wild card character which used to match exactly one character, % (percentage) which used to match zero or more occurrences of any characters and ‘ (apostrophe or quotation mark) which used to mark the value supplied. These special characters will not be interpreted literally when building SQL query in Oracle, and may cause error in results returned especially when performing string search with LIKE keyword. To use these characters so that Oracle can interpret them literally as a part of string value instead of preset mean, escape character has to be assigned.

Oracle allows the assignment of special escape characters to the reserved characters in Oracle can be escaped to normal characters that is interpreted literally, by using ESCAPE keyword.

For example, to select the name of guests with _ (underscore) in it, use the following statement:

SELECT guest_name FROM guest_table WHERE name LIKE ‘%\_%’ ESCAPE ‘\’;

Without specifying the \ (backslash) as escape clause, the query will return all guest names, making the unwanted results problem.

The above syntax will not work on ‘ (quote). To escape this quotation mark and to display the quote literally in string, insert another quote (total 2 quotes) for every quote that want to be displayed. For example:

SELECT ‘This will display line with quote’’s word.’ FROM dual;

Output: This will display line with quote’s word.

Controlling Canvases in Forms Programmatically

This article describes how to control canvases in Forms programmatically. It covers the triggers and built-ins that you can use to manage the properties and behavior of canvases at run-time and describes how to:

     — Control canvases in the forms

     — Manipulate canvases in the forms

     — Manipulate tab canvases

Contents:

1. What are the Built-ins for manipulating canvases?

2. What triggers are required to work with tab-style canvases?

3. What are the tab canvas system variables?

4. What are the built-ins for manipulating tab-style canvases?

5. Other Arguments for Canvas Built-ins

6. Modifying the when-tab-page-changed trigger to change the label on the tab canvas

 

1. What are the Built-ins for manipulating canvases?

The following built-ins are used for manipulating canvases:

Find_canvas returns the internal canvas ID(of datatype CANVAS) of a canvas with the given name.

Find_View returns the internal view ID (of datatype VIEWPORT) of a canvas with the given name.

Get_Canvas_property returns the current value of the specified Canvas property for the given canvas.

Get_View_property returns the current value of the specified View property for the canvas.

Hide_View hides the canvas.

Replace_Content_View replaces the content canvas currently displayed in the window with the specified content canvas.

Scroll_view moves the view of a given canvas to a different position on its canvas. It does not move the window to a different position on the screen.

Set_canvas_property sets the specified Canvas property for the given canvas to a specified value.

Set_view_property sets the specified View property for the given canvas to a specified value.

Show_view makes the given canvas visible at the current display position.

Note: Replace_content_view does not hide the stacked canvas already displayed in the window, whereas show_view and Set_view_property (with the visible property) display the given canvas in front of any stacked canvas.

 

2. What triggers are required to work with tab-style canvases?

The When-tab-page-changed form-level trigger fires when there is explicit item or mouse navigation from one tab page to another in a tab canvas.  In other words, when the user clicks a tab or presses the [control] + [PageUP] or [control] + [PageDown] keys.

This trigger is often used to perform actions when any tab page is changed during item or mouse navigation; for instance, to enable or disable items or to set default or related item values.

Note: This trigger does not fire when the tab page is changed programmatically and does not fire with implicit navigation.

 

3. What are the tab canvas system variables ?

Within the when-tab-page-changed trigger, you can reference the following system variables to determine where you are coming from and going to:

:SYSTEM.TAB_NEW_PAGE returns the name of the tab page to which you are going

:SYSTEM.TAB_PREVIOUS_PAGE returns the name of the tab page that you are coming from

These system variables return only the page name, not the canvas name, so you must name all tab pages uniquely across the form if you need to be able to identify them programmatically.

Note: When changing to another tab page, the cursor does not automatically move to a different item. If you want to move the cursor, you must include a GO_ITEM statement in the when-tab-page-changed trigger. This is intended functionality which allows users to view other tab pages without navigating the cursor and thereby causing item navigating and validation as previously mentioned.

 

4. What are the built-ins for manipulating tab-style canvases?

The following built-ins are used for manipulating tab-style canvases:

Find_tab_page searches the list of tab pages in a given tab canvas and returns a tab page ID when it finds a valid tab page with the given name. You must define a variable of type TAB_PAGE to accept the return value.

Set_tab_page_property sets the tab page properties (ENABLED, LABEL, VISIBILE, VISUAL_ATTRIBUTE) of the specified tab canvas page.

Get_tab_page_property returns property values (CANVAS_NAME,  ENABLED,LABLE, VISIBLE, VISUAL_ATTRIBUTE) for a specified tab page.

Get_canvas_property identifies the topmost tab page;

Set_canvas_property brings a page to the top programmatically.

Get_tab_page_property returns the tab page label or the tab page canvas while

Set_tab_page_property enables changing of the tab page label.

 Here is an example:

DECLARE

       Tp_id tab_page;

BEGIN  

       Tp_id :=find_tab_page(‘canvas2.tabpage1’);

       IF      GET_TAB_PAGE_PROPERTY(tp_id, enabled) = ‘False’    THEN

               SET_TAB_PAGE_PROPERTY(tp_id, enabled, property_true);

       END IF;

END;

 

5. Other Arguments for Canvas Built-ins

If you want to make a tab page the top-most on its underlying tab canvas, you can use the built-in procedure SET_CANVAS_PROPERTY and set the canvas property TOPMOST_TAB_PAGE.  You can also get the top-most

tab page using the built-in function GET-CANVAS-PROPERTY. For example:

GET_CANVAS_PROPERTY(‘canvas_name’, TOPMOST_TAB_PAGE)

SET_CANVAS_PROPERTY(‘canvas_name’, TOPMOST_TAB_PAGE, page_name)

Where page_name is either a constant, in single quotes, or a variable.

 

6. Modifying the when-tab-page-changed trigger to change the label on the tab canvas

This example shows how to modify the when-tab-page-changed trigger to change the label (for example, “Comment”) on the tab page. That is, when “Comment” is topmost page, change its label to, for example,

“Employee XX…”, where XX is the employee’s ID number. When any other tab page is topmost, change the label back to the original tab name (in this case, “Comment”.) You would use the following code in the When-Tab-Page-Changed trigger:

:control.help_tab := ‘Displays ‘|| Initcap(:SYSTEM.TAB_NEW_PAGE) || ‘ information about —‘ ;

IF :SYSTEM.TAB_NEW_PAGE = ‘COMMENT’ THEN

 SET_TAB_PAGE_PROPERTY(‘CANVAS2.COMMENT’, LABEL, ‘Employee ‘ || :EMP.ID);

ELSE

 SET_TAB_PAGE_PROPERTY(‘CANVAS2.COMMENT’, LABEL, ‘Comment’);

END IF;

Note: Where control.help_tab is block_name.item.name, when you click on Comment TAB, its title changes to “Employee” instead. This is to change the TAB label when you navigate to that TAB. Then, when you navigate out of that TAB, the original label returns. As you long as you do not navigate to that tab, its original title is kept and still there.

Basic Concepts of Oracle Forms

Forms functionality is driven by user events (such as pressing a key) and navigation events (such as the cursor about to enter/leave a field). These events are identified by triggers in a form. These triggers fall into several groups:

KEY  – Fires when the corresponding key is pressed
PRE  – Fires prior to an event
WHEN – Fires when an event occurs
ON   – Replaces default event processing
POST – Fires after an event

These triggers include events such as:

  1. PRE and POST triggers for the form block, row, and item.
  2. PRE and POST triggers for row inserts, updates, and deletions.
  3. WHEN triggers fire as a direct result of an event such as the user clicking on a button (WHEN-BUTTON-PRESSED) or the cursor navigating to a new item and readying for user input (WHEN-NEW-ITEM-INSTANCE).
  4. Keys the user can press on their keyboard like the Tab button (KEY-NEXT-ITEM) or F10 (KEY-COMMIT).
  5. ON triggers fire when an event occurs. For example, ON-MESSAGE fires when forms is about to issue a message. This gives the developer an opportunity to trap and replace particular messages with custom messages.

By adding PL/SQL code to a trigger you can:

  1. Alter the way a trigger would ordinarily work. For instance, by creating a KEY-ENTQRY on a field with: null; you will prevent the user from pressing F8.
  2. Supplement the way something works – for instance by creating a KEY-DELREC trigger on a block with code that asks the user if they “really” want to delete that record before issuing the delete_record;.

Trigger Scope
If you create a KEY-EXEQRY on a field with: null; then you will prevent the user from pressing F8 while the cursor is in that field. If, instead, you attach the same trigger to a block, then you will prevent the user from pressing F8 while the cursor is anywhere in that block. If, instead, you attach the same trigger to the form, then you will prevent the user from pressing F8 anywhere within the form.

You can override high level triggers at a lower level. For example, if you have disabled F7 at the form level, you can add a KEY-ENTQRY to a block with: enter_query; that will allow the user to press F7 to enter a query while the cursor is in that block. This means that F7 will work in that block but nowhere else in the form.

Blocks
A most basic concept in a form is blocks. Blocks are basically comprised of 2 types:

  1. Control block – A block not associated with a table. This block is usually a single row block that has no interaction with the database.
  2. Base table block – this is associated with a table. You do not have to code any SQL statements! Forms will automatically:
    A. Query rows of data from the table (execute_query)
    B. Insert a new row below the current row (create_record)
    C. Delete the current row (delete_record)
    D. Update rows (by the user typing values on a queried row)
    E. Handle row locking
    F. Make all these database changes permanent at commit time

Canvases
A canvas is an object that can be displayed on the screen. The canvas may contain buttons, graphics, display items and text items.

The canvas may be smaller or larger than the screen size. One canvas may be stacked on top of another canvas so that the user might see several canvases at the same time.

A large canvas might only be partially visible to the user. This is known as a “view” of the canvas.

If the cursor navigates to an enterable
item on a canvas, then the canvas becomes
visible to the user. However, when the
cursor leaves the items on the canvas,
the canvas will not automatically be
hidden from view unless another canvas
covers it.

File Handling in PL/SQL

Here is a very simple example for how to read from and write to a file with PL/SQL code block.

WRITE TO A FILE

UTL_FILE package is used for writing to a file. To begin writing to a file of a specific directory first you will need to have permission from your DBA.

If you are a DBA, you can create a directory as below and give permission to “user” as below

Code:

CONNECT / AS DBA

CREATE OR REPLACE DIRECTORY STUDENT AS ‘/tmp’;

GRANT read, write ON DIRECTORY STUDENT TO user;

GRANT EXECUTE ON UTL_FILE TO user;

Once permission is granted to you, just follow the code below to write to a file.

Code:

DECLARE

  fHandler UTL_FILE.FILE_TYPE;

BEGIN

  fHandler := UTL_FILE.FOPEN(‘STUDENT’, ‘Details’, ‘w’);

  UTL_FILE.PUTF(fHandler, Hello sir! How are you? \n’);

  UTL_FILE.FCLOSE(fHandler);

EXCEPTION

  WHEN utl_file.invalid_path THEN

     raise_application_error(-20000, ‘Invalid path. Create directory or set UTL_FILE_DIR.’);

END;

/

READ FROM A FILE

UTL_FILE package is again used for reading from a file. To begin reading from a file of a specific directory you will again need to have permission from DBA in the same manner as shown above.

Once you have read permission follow the code below to read from a file.

Code:

DECLARE

  fHandler UTL_FILE.FILE_TYPE;

  buf      varchar2(4000);

BEGIN

  fHandler := UTL_FILE.FOPEN(‘STUDENT’, ‘Details’, ‘r’);

  UTL_FILE.GET_LINE(fHandler, buf);

  dbms_output.put_line(‘DATA FROM FILE: ‘||buf);

  UTL_FILE.FCLOSE(fHandler);

EXCEPTION

  WHEN utl_file.invalid_path THEN

     raise_application_error(-20000, ‘Invalid path. Create directory or set UTL_FILE_DIR.’);

END;

/

Note how FOPEN and FCLOSE are used to open and close the file and GET_LINE and PUTF are used to read from and write to a file.

SQL DELETE, TRUNCATE, DROP Statements

SQL Delete Statement

The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:

DELETE FROM table_name [WHERE condition];

  • table_name — the table name which has to be updated.

NOTE: The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like,

DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like,

DELETE FROM employee;

 

SQL TRUNCATE Statement 

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like,

TRUNCATE TABLE employee;

  

Difference between DELETE and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

 

SQL DROP Statement: 

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like

DROP TABLE employee;

 

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

PL/SQL Enhancements in Oracle Database 10g

Oracle 10g includes many PL/SQL enhancements including:

Calculating month-wise total days between two dates

This script returns the month-wise total number of days between dates given in parameters.

DECLARE

   dt1            DATE;

   dt2            DATE;

   dt             DATE;

   days           NUMBER := 0;

   no_of_months   NUMBER;

BEGIN

   SELECT CEIL (  MONTHS_BETWEEN (TO_DATE (   ’01-‘

                                           || TO_CHAR (TO_DATE (:date1),

                                                       ‘MON-RRRR’

                                                      )

                                          ),

                                  LAST_DAY (TO_DATE (:date2))

                                 )

                * (-1)

               )

     INTO no_of_months

     FROM DUAL;

 

   SELECT TO_DATE (:date1), TO_DATE (:date2)

     INTO dt1, dt2

     FROM DUAL;

 

   dt := dt1;

   DBMS_OUTPUT.put_line (‘—————————-‘);

 

   FOR dy IN 1 .. no_of_months

   LOOP

      IF dy = 1

      THEN

         days := LAST_DAY (dt1) – dt1 + 1;

      ELSIF dy = no_of_months

      THEN

         days := dt2 – TO_DATE (’01-‘ || TO_CHAR (dt2, ‘MON-RRRR’));

      ELSE

         days := TO_NUMBER (TO_CHAR (LAST_DAY (TO_DATE (dt)), ‘DD’));

      END IF;

 

      DBMS_OUTPUT.put_line (TO_CHAR (dt, ‘MON-RR’) || ‘ Days: ‘ || days);

      dt := ADD_MONTHS (dt, 1);

   END LOOP;

 

   DBMS_OUTPUT.put_line (‘—————————-‘);

END;