PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT VARCHAR2,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER);

The formats for CHAR are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT CHAR);

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT CHAR,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER);

The formats for DATE are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT DATE);

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT DATE,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER);

The formats for RAW are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT RAW);

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT RAW,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER);

The formats for ROWID are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT ROWID);

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT ROWID,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER) ;

The formats for MLSLABEL in Trusted Oracle are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT MLSLABEL) ;

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT MLSLABEL,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER) ;

Again, cursor_id is the ID assigned by OPEN_CURSOR. column_order is the order of the columns. column_variable is the variable you declared to represent the column. Optionally, column_error enables you to determine which column caused an error. The value is set to zero if the column has no error, such as truncating a value, and so on. actual_length is the length of the variable in the buffer before it is placed into the associated PL/SQL variable. This parameter is useful only if trying to determine which columns are truncating values and how to handle the errors.

Closing the Cursor for Queries
You can now finally close the cursor with CLOSE_CURSOR, which is the same format as DML and DDL statements. After all processing has been completed, you should close the cursor with the CLOSE_CURSOR procedure to free up those resources.

The Syntax for the CLOSE_CURSOR Procedure

PROCEDURE CLOSE_CURSOR (cursor_id IN OUT INTEGER);

cursor_id is the cursor assigned by the OPEN_CURSOR statement. After the call has been complete, the value of the cursor_id is set to NULL.

Using Queries with the DBMS_SQL Package
This section demonstrates using queries inside PL/SQL through the DBMS_SQL package. You will simply display the values you inserted into the table to the screen. Execute the code in Listing 17.3 to verify that you added the records. Make sure that you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt.

INPUTListing 17.3. Using SELECT to verify inserted records.

 1: DECLARE
 2: /* The purpose of this PL/SQL block is to demonstrate
 3:    executing queries within PL/SQL through the use of the
 4:    DBMS_SQL package.  We will simply display the output to
 5:    screen with the DBMS_OUTPUT package */
 6:
 7:      v_CursorID  NUMBER; -- Variable assigned to value from OPEN_CURSOR
 8:      v_SelectRecords  VARCHAR2(500); -- SQL stored as string to select
         Ârecords

 9:      v_NUMRows  INTEGER; -- Number of rows processed - of no use
10:      v_MyNum INTEGER;
11:      v_MyText VARCHAR2(50);
12:
13: BEGIN
14:      v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
15:      v_SelectRecords := `SELECT * from MyTable'; -- SQL to view records
16:
17:
18:      DBMS_SQL.PARSE(v_CursorID,v_SelectRecords,DBMS_SQL.V7);
19:           /* Perform syntax error checking */
20:
21:      DBMS_SQL.DEFINE_COLUMN(v_CursorID,1,v_MyNum);
22:      DBMS_SQL.DEFINE_COLUMN(v_CursorID,2,v_MyText,50);
23:
24:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
25:           /* Execute the SQL code  */
26: LOOP
27:      IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
28:           EXIT;
29:      END IF;
30:
31:      DBMS_SQL.COLUMN_VALUE(v_CursorId,1,v_MyNum);
32:      DBMS_SQL.COLUMN_VALUE(v_CursorId,2,v_MyText);
33:
34:      DBMS_OUTPUT.PUT_LINE(v_MyNum || ` ` || v_MyText);
35:
36: END LOOP;
37:
38: EXCEPTION
39:      WHEN OTHERS THEN
40:                RAISE; -- raise if some other unknown error
41:
42:      DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
43:
44: END; -- End PL/SQL block

After you have executed the block, you should see

OUTPUT
1 One
2 Two
3 Three
4 Four
4 Four

ANALYSIS It takes some work, but you should have been able to print a SELECT query to the screen. First, you open the cursor with OPEN_CURSOR in line 14. Then, v_SelectRecords is assigned the string for the SQL query to select all records from MyTable in line 15. The SQL statement is then parsed in line 18. No variables need binding in this example, but if you were inserting values into a table, you could have used binding. In lines 21 and 22, you then define the two output variables of v_MyNum and v_MyText, which are the two columns created from Listing 17.1. The query is then executed with DBMS_SQL.EXECUTE in line 24. In line 26, you then enter a loop that will fetch the rows and then display to the screen with

COLUMN_VALUE. The EXIT condition in line 28 occurs when no more rows are found, which is a value of 0. Finally, the cursor is properly closed in line 42.

Using the DBMS_SQL Package with Anonymous
PL/SQL Blocks

This section demonstrates how to execute anonymous PL/SQL blocks with the DBMS_SQL package. The processing method is very similar to processing queries and executing DDL or DML statements. The following steps are required:

1. Open the cursor.
2. Parse the statement.
3. Bind input variables (if required).
4. Execute the statement.
5. Retrieve the results into variables.
6. Close the cursor.

Opening a Cursor for Anonymous Blocks
This is identical to DDL and DML processing. Whenever you execute SQL or PL/SQL, a cursor is opened transparently. When creating dynamic SQL, you must specify every step, including opening the cursor by using OPEN_CURSOR. The syntax for the OPEN_CURSOR function is as follows:

FUNCTION OPEN_CURSOR RETURN INTEGER;

The OPEN_CURSOR function returns an integer, which is the cursor ID. The cursor ID will be retained for use until the cursor is closed. As you can see, leaving a cursor open without closing the cursor using CLOSE_CURSOR wastes valuable resources. You can now perform as many statements as desired by referencing the cursor ID returned.

Parsing Anonymous Blocks
This is identical to DDL and DML processing with one exception. Here, you need to use proper punctuation including the semicolon. You would never use the semicolon in queries or DDL or DML statements.

Binding Variables for Anonymous Blocks
This is identical to DDL and DML processing. Binding enables you to bind a specific variable to a placeholder, which is identified by a colon. Because you can bind all types of PL/SQL variables, the overloaded BIND_VARIABLE procedure can handle many datatypes.