The Syntax for Binding Variables
The syntax for the NUMBER datatype is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN NUMBER);

The two syntaxes for type VARCHAR2 are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2,
                          out_value_size IN INTEGER);

The two syntaxes for type CHAR2 are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN CHAR2);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN CHAR2,
                          out_value_size IN INTEGER);

The syntax for the DATE datatype is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN DATE);

The two syntaxes for type RAW are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN RAW);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN RAW,
                          out_value_size IN INTEGER);

The syntax for ROWID is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN ROWID);

Finally, there is support for MLSLABEL in Trusted Oracle with

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN MLSLABEL);

cursor_id is the ID returned from OPEN_CURSOR. placeholder_name is the name of the variable preceded by a colon. value is the value to be bound (assigned) to the placeholder variable name. For any of the procedures with out_value_size, this is the output size expressed in bytes for those datatypes.

Executing Anonymous Blocks
This is identical to DDL and DML processing. After you have bound variables, if required, you are ready to execute the anonymous block with the function EXECUTE.

The Syntax for the EXECUTE Function

FUNCTION EXECUTE (cursor_id IN INTEGER) RETURN INTEGER;

cursor_id is the cursor assigned by the OPEN_CURSOR statement. The function also returns an integer that is valid only for DML statements, which returns the number of rows processed. The applicable DML statements are INSERT, UPDATE, and DELETE. This value should be ignored for all other purposes.

Retrieving Values with Anonymous Blocks
The only other variation from queries or DML and DDL statements is the need to retrieve the values of the variables from the buffer. To retrieve the variables, you will use the VARIABLE_VALUE procedure. When the EXECUTE function executes, it stores the values into the buffer. The VARIABLE_VALUE procedure will retrieve these values into PL/SQL variables. This overloaded procedure has various syntaxes for various datatypes.

The Syntax for the VARIABLE_VALUE Procedure The syntax for NUMBER is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT NUMBER);

The syntax for VARCHAR2 is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT VARCHAR2);

The syntax for CHAR is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT CHAR);

The syntax for DATE is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT DATE);

The syntax for RAW is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT RAW);

The syntax for ROWID is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT ROWID);

The syntax for MLSLABEL in Trusted Oracle is

PROCEDURE VARIABLE_VALUE(cursor_id IN INTEGER
                                placeholder_name IN VARCHAR2,
                                output_variable OUT MLSLABEL);

cursor_id is the cursor ID returned from OPEN_CURSOR. placeholder_name is the name of the placeholder. Don't forget to identify these by the preceding colon encapsulated with single quotes. output_variable is the PL/SQL variable to hold the output retrieved from the buffer.

Closing the Cursor for Anonymous Blocks
This is identical to DDL and DML processing. 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 cursor_id is set to NULL.

Using Anonymous PL/SQL Blocks with the DBMS_SQL Package
This section demonstrates using anonymous PL/SQL blocks through the DBMS_SQL package. You will simply display the values you inserted into the table to the screen with values equal to 2. Execute the code in Listing 17.4 to display these values to the screen. Make sure that you have entered SET SERVEROUTPUT ON at the SQL*Plus prompt.

Listing 17.4. Using anonymous blocks.

 1: DECLARE
 2: /* This procedure calls an anonymous block which performs a
 3:    query to look up the description for the row id value = 2.
 4:    This demonstrates the use of an anonymous PL/SQL block
 5:    within PL/SQL  */
 6:
 7:      v_CursorID  NUMBER; -- Variable assigned to value from OPEN_CURSOR
 8:      v_MatchRecord  VARCHAR2(500); -- SQL stored as string to select one
         Ârecord
 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_MatchRecord := `BEGIN -- Start of Anonymous PL/SQL Block
16:                           SELECT MyRow,MyDesc
17:                              INTO :MyRow, :MyText FROM MyTable
18:                              WHERE MyRow = 2; -- Notice has ending semicolon
19:                        END;'; -- End of Anonymous PL/SQL Block
20:
21:      DBMS_SQL.PARSE(v_CursorID,v_MatchRecord,DBMS_SQL.V7);
22:           /* Perform syntax error checking */
23:
24:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:MyRow',v_MyNum);
25:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:MyText',v_MyText,50);
26:
27:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
28:           /* Execute the SQL code  */
29:
30:
31:      DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyRow',v_MyNum);
32:      DBMS_SQL.VARIABLE_VALUE(v_CursorId,':MyText',v_MyText);
33:      /* Defines variables to hold output */
34:
35:      DBMS_OUTPUT.PUT_LINE(v_MyNum || ` ` || v_MyText);
36:
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
2 Two