Using DML to Add Records to the Table
Now that you have created a table with DDL statements, you can add some records with DML statements. You can finally see how many rows were processed. You will add a total of five records; the fifth record will not need binding because it takes the default values of the previous bind, which effectively duplicates the fourth record. Go ahead and execute the code in Listing 17.2.

INPUTListing 17.2. Using INSERT to add records.

 1: DECLARE
 2: /* The purpose of this PL/SQL block is to demonstrate the use
 3:    of DML statements by adding a total of four records. This will
 4:    illustrate the use of binding variables and the multiple use
 5:    of accessing the Cursor ID */
 6:
 7:      v_CursorID  NUMBER; -- Variable assigned to value from OPEN_CURSOR
 8:      v_InsertRecords  VARCHAR2(500); -- SQL stored as string to insert
         Ârecords
 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_InsertRecords := `INSERT INTO MyTable(MyRow,MyDesc)
14:            VALUES (:mynum,:mytext)'; -- Write SQL to insert records
15:
16: /*  Define and Insert the First Record */
17:
18:      DBMS_SQL.PARSE(v_CursorID,v_InsertRecords,DBMS_SQL.V7);
19:           /* Perform syntax error checking */
20:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',1);
21:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','One');
22:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
23:           /* Execute the SQL code  */
24:      DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
25:           || v_NUMRows);
26:
27: /*  Define and Insert the Second Record */
28:
29:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',2);
30:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','Two');
31:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
32:           /* Execute the SQL code  */
33:      DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
34:           || v_NUMRows);
35:
36: /*  Define and Insert the Third Record */
37:
38:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',3);
39:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','Three');
40:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
41:           /* Execute the SQL code  */

42:      DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
43:           || v_NUMRows);
44:
45: /*  Define and Insert the Fourth Record */
46:
47:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mynum',4);
48:      DBMS_SQL.BIND_VARIABLE(v_CursorID, `:mytext','Four');
49:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
50:           /* Execute the SQL code  */
51:      DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
52:           || v_NUMRows);
53:
54: /* Duplicate the Fourth Entry! */
55:
56:      v_NumRows := DBMS_SQL.EXECUTE(v_CursorID);
57:           /* Execute the SQL code  */
58:      DBMS_OUTPUT.PUT_LINE(`The number of records just processed is: `
59:           || v_NUMRows);
60:
61: EXCEPTION
62:      WHEN OTHERS THEN
63:                RAISE; -- raise if some other unknown error
64:
65:      DBMS_SQL.CLOSE_CURSOR(v_CursorID); -- Close the cursor
66:      COMMIT;
67: END; -- End PL/SQL block

After you have executed the block, you should see

OUTPUT The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1 The number of records just processed is: 1

ANALYSIS You have just inserted five complete records, duplicating the fourth record!

Now review the steps for DML statements. In line 12, you first open the cursor with OPEN_CURSOR. You then assign the value of v_InsertRecords to the SQL required to insert the records in line 13. You will notice the placeholder variables as identified by the colon in front of the placeholder names in line 14. Your next step is to parse the statement for syntax error checking, as shown in line 18. After parsing the statement, you need to bind the placeholder variables as in lines 20 through 21. You are ready to execute the statement in line 22 and finally close the cursor in line 65 after all records are inserted. This identical process is repeated four more times to add four more records. Again, if you were to create a stored function or procedure, you should be able to pass as the parameter simply the record to insert, which could be variable names, for true dynamic SQL! Creating this type of function demonstrates the practicality of dynamic SQL with the DBMS_SQL package.

Using the DBMS_SQL Package with Queries

This section demonstrates how to execute queries with the DBMS_SQL package. The processing method is very similar to DDL and DML processing. The following steps are required:

1. Open the cursor.
2. Parse the statement.
3. Bind input variables (if required).
4. Define the output variables.
5. Execute the statement.
6. Fetch the rows.
7. Store the results from fetching the rows into PL/SQL variables.
8. Close the cursor.

Opening a Cursor for Executing Queries
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 Statements for Queries
Parsing is again almost identical to DDL and DML statements. Both have the same syntax and requirement that no semicolon should appear at the end of the SQL code. However, additional requirements when executing queries are

• The SELECT statement can't be embedded in PL/SQL. There should be only one SELECT statement.
• The query should not contain an INTO statement. You can work around this through the use of DEFINE_COLUMNS and COLUMN_VALUES.

The Syntax for the PARSE Procedure

PROCEDURE PARSE(cursor_id IN INTEGER,
                          statement_to_parse IN VARCHAR2,
                          version IN VARCHAR2);

cursor_id is the cursor assigned by the OPEN_CURSOR statement. statement_to_parse is the SQL statement you want to parse. The last parameter, version, is the version type.