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 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);

As you can see, there is support for almost any conceivable datatype.

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 Statements for Non-Query DDL and DML Statements
After you have bound variables, if required, you are ready to execute the DDL or DML statements 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.

Closing the Cursor for Non-Query DDL and DML 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 cursor_id is set to NULL.

Using DDL to Create a Table
You'll start the review of dynamic SQL by creating a table from within SQL. Although this example is still "fixed," it would be easy to modify the example in the form of a stored function or procedure to allow a user to create any type of table. Enter and then execute the code in Listing 17.1.

INPUT
Listing 17.1. Creating the table called MyTable.

 1: DECLARE
 2: /* The purpose of this PL/SQL block is to create a table
 3:    called MyTable, which has two columns of type INTEGER and
 4:    the second column of type VARCHAR2(50).  This uses the
 5:    DBMS_SQL package to execute DDL statements */
 6:
 7:      v_CursorID  NUMBER; -- Variable assigned to value from OPEN_CURSOR
 8:      v_CreateTableString  VARCHAR2(500); -- SQL stored as string to create
         Âtable
 9:      v_NUMRows  INTEGER; -- Number of rows processed - of no use
10:
11: BEGIN
12:      v_CursorID := DBMS_SQL.OPEN_CURSOR; -- Get the Cursor ID
13:      v_CreateTableString := `CREATE TABLE MyTable(
14:           MyRow INTEGER,
15:           MyDesc VARCHAR2(50))'; -- Write SQL code to create table
16:
17:      DBMS_SQL.PARSE(v_CursorID,v_CreateTableString,DBMS_SQL.V7);
18:           /* Perform syntax error checking */
19:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
20:           /* Execute the SQL code  */
21: 

22: EXCEPTION
23:      WHEN OTHERS THEN
24:           IF SQLCODE != -955 THEN -- 955 is error that table exists
25:                RAISE; -- raise if some other unknown error
26:           ELSE
27:                DBMS_OUTPUT.PUT_LINE(`Table Already Exists!');
28:           END IF;
29:      DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
30: END; -- End PL/SQL block

OUTPUT After you have executed the block, you should see

PL/SQL procedure successfully completed.

ANALYSIS You have just created a table called MyTable. The code in Listing 17.1 sets up three variables:

• v_CursorID—Holds the cursor ID returned by the call to OPEN_CURSOR. This variable is set up in line 7.
• v_CreateTableString—Holds the SQL code required to create the table. This variable is set up in line 8.

• v_NUMRows—Holds the number of rows processed, which is meaningless with the DDL statements, but required by PL/SQL to hold the return value. This variable is set up in line 9.

Next, in line 12, v_CursorID is assigned the cursor ID supplied by OPEN_CURSOR. In line 13, the v_CreateTableString is assigned the necessary SQL code to create the table. Again, it is very important that the SQL code you will execute should not end in a semicolon! The statement is parsed for syntax errors in line 17. No variables need to be bound in this PL/SQL code. The dynamic SQL statement is then executed in line 19, and the cursor is then closed.

If the table already exists, I do not want this procedure to automatically delete the table. I have seen valid production tables get deleted this way. Instead, if the error 955 occurs, which means that the table exists, the program displays the message that the table exists. This exception-handling routine is in lines 22 through 28. After making sure to enter SET SERVEROUTPUT ON, go ahead and execute the code in Listing 17.1 again. You will then see the message that the table already exists. In the exception area, if any other error occurs, I want the error to be raised for you to view because this error would be truly unknown.