[Appendix C] C.14 DBMS

C.14 DBMS_SQL

The DBMS_SQL package offers access to dynamic SQL from within PL/SQL. "Dynamic SQL" means SQL statements are not prewritten into your programs; instead, they are constructed at run time as character strings and then passed to the SQL engine for execution.

C.14.1 The BIND_ARRAY procedure

With PL/SQL8 you can perform bulk selects, inserts, updates and deletes to improve the performance of your application. You accomplish these by associating one or more index-by tables with columns in your cursor. The BIND_ARRAY procedure performs this step for you with this interface:

PROCEDURE DBMS_SQL.BIND_ARRAY (c IN INTEGER,
   name IN VARCHAR2,
   <table_variable> IN <datatype>,
  [,index1 IN INTEGER,
   ,index2 IN INTEGER)]);

The <table_variable> <datatype> pairing may be any of the following:

<number_table> DBMS_SQL.NUMBER_TABLE
<varchar2_table> DBMS_SQL.VARCHAR2_TABLE
<date_table> DBMS_SQL.DATE_TABLE
<blob_table> DBMS_SQL.BLOB_TABLE
<clob_table> DBMS_SQL.CLOB_TABLE
<bfile_table> DBMS_SQL.BFILE_TABLE

C.14.2 The BIND_VARIABLE procedure

The BIND_VARIABLE procedure lets you bind a specific value to a host variable which was placed in the SQL statement as a placeholder. Whenever you include a reference to a bind or host variable in the SQL statement that you pass to the PARSE procedure, you must issue a call to BIND_VARIABLE in order to bind or attach a value to that variable. The overloaded specification for this procedure supports multiple datatypes, as follows:

PROCEDURE DBMS_SQL.BIND_VARIABLE
   (c IN INTEGER,
    name IN VARCHAR2,
    value IN <datatype>);

The <datatype> may be any of the following:

BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
MLSLABEL /* Trusted Oracle only */
NUMBER
VARCHAR2 CHARACTER SET ANY_CS

The dbms_sql package also offers more specific variants of bind_variable for less common datatypes:

PROCEDURE DBMS_SQL.BIND_VARIABLE
   (c IN INTEGER,
    name IN VARCHAR2,
    value IN VARCHAR2 CHARACTER SET ANY_CS,
    [,out_value_size IN INTEGER]);

PROCEDURE DBMS_SQL.BIND_VARIABLE_CHAR
   (c IN INTEGER,
    name IN VARCHAR2,
    value IN CHAR CHARACTER SET ANY_CS,
    [,out_value_size IN INTEGER]);

PROCEDURE DBMS_SQL.BIND_VARIABLE_RAW
   (c IN INTEGER,
    name IN VARCHAR2,
    value IN RAW,
    [,out_value_size IN INTEGER]);

PROCEDURE DBMS_SQL.BIND_VARIABLE_ROWID
   (c IN INTEGER,
    name IN VARCHAR2,
    value IN ROWID;

C.14.3 The CLOSE_CURSOR procedure

The CLOSE_CURSOR procedure closes the specified cursor and sets the cursor handle to NULL. It releases all memory associated with the cursor. The specification for the procedure is:

PROCEDURE DBMS_SQL.CLOSE_CURSOR (c IN OUT INTEGER);

C.14.4 The COLUMN_VALUE procedure

The COLUMN_VALUE procedure retrieves a value from the cursor into a local variable. Use this procedure when the SQL statement is a query and you are fetching rows with EXECUTE_AND_FETCH or FETCH_ROWS. You call COLUMN_VALUE after a row has been fetched to transfer the value from the SELECT list of the cursor into a local variable. For each call to COLUMN_VALUE, you should have made a call to DEFINE_COLUMN in order to define that column in the cursor.

The overloaded specification is:

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT DATE,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT NUMBER,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT VARCHAR2,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

The DBMS_SQL package also offers more specific variants of COLUMN_VALUE for less common datatypes:

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT MLSLABEL,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT CHAR,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT RAW,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

PROCEDURE DBMS_SQL.COLUMN_VALUE
   (c IN INTEGER,
    position IN INTEGER,
    value OUT ROWID,
    [, column_error OUT NUMBER]
    [, actual_length OUT INTEGER ]);

C.14.5 The DEFINE_COLUMN procedure

When you call DBMS_SQL.PARSE to process a SELECT statement, you want to pass values from the database into local variables. To do this you must associate the columns or expressions in the SELECT list with those local variables. You do this with the DEFINE_COLUMN procedure, whose overloaded specification is:

PROCEDURE DBMS_SQL.DEFINE_COLUMN
   (c IN INTEGER,
    position IN INTEGER,
    column IN DATE);

PROCEDURE DBMS_SQL.DEFINE_COLUMN
   (c IN INTEGER,
    position IN INTEGER,
    column IN NUMBER);

PROCEDURE DBMS_SQL.DEFINE_COLUMN
   (c IN INTEGER,
    position IN INTEGER,
    column IN VARCHAR2,
    column_size IN INTEGER);

C.14.6 The EXECUTE function

The EXECUTE function executes the SQL statement associated with the specified cursor. It returns the number of rows processed by the SQL statement if that statement is an UPDATE, INSERT, or DELETE. If the SQL statement is not an UPDATE, INSERT, or DELETE, ignore the value returned by EXECUTE. If the SQL statement is a query, you can now call the FETCH_ROWS function to fetch rows which are retrieved by that query. The specification is:

FUNCTION DBMS_SQL.EXECUTE (c IN INTEGER) RETURN INTEGER;

C.14.7 The EXECUTE_AND_FETCH function

The EXECUTE_AND_FETCH function executes the SELECT statement associated with the specified cursor and immediately fetches the rows associated with the query. The specification is:

FUNCTION DBMS_SQL.EXECUTE_AND_FETCH
   (c IN INTEGER,
    exact_match IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

C.14.8 The FETCH_ROWS function

The FETCH_ROW function corresponds to the FETCH statement for regular PL/SQL cursors. It fetches the next row from the cursor. The specification is:

FUNCTION DBMS_SQL.FETCH_ROWS (c IN INTEGER) RETURN INTEGER;

C.14.9 The IS_OPEN function

The IS_OPEN function returns TRUE if the specified cursor is already open, FALSE otherwise. This function corresponds to the %ISOPEN attribute for regular PL/SQL cursors. The specification is:

FUNCTION DBMS_SQL.IS_OPEN (c IN INTEGER) RETURN BOOLEAN;

C.14.10 The LAST_ERROR_POSITION function

The LAST_ERROR_POSITION function returns the byte offset in the SQL statement where the ERROR occurred. Call this function immediately after a call to EXECUTE or EXECUTE_AND_FETCH in order to obtain meaningful results. The specification is:

FUNCTION DBMS_SQL.LAST_ERROR_POSTITION RETURN INTEGER;

C.14.11 The LAST_ROW_COUNT function

The LAST_ROW_COUNT function returns the total number of rows fetched at that point. The specification is:

FUNCTION DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;

C.14.12 The LAST_ROW_ID function

The LAST_ROW_ID function returns the rowid of the row fetched most recently. The specification is:

FUNCTION DBMS_SQL.LAST_ROW_ID RETURN ROWID;

C.14.13 The LAST_SQL_FUNCTION_CODE function

The LAST_SQL_FUNCTION_CODE function returns the SQL function code for the SQL statement. The specification is:

FUNCTION DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;

C.14.14 The OPEN_CURSOR function

Use this function to open a cursor, which means that the Oracle Server will set aside memory for a cursor data area and return a pointer to that area. The specification is:

FUNCTION DBMS_SQL.OPEN_CURSOR RETURN INTEGER;

C.14.15 The PARSE procedure

The PARSE procedure immediately parses the statement specified. The specification for this procedure is:

PROCEDURE DBMS_SQL.PARSE
   (cursor_handle IN INTEGER,
    SQL_statement IN VARCHAR2,
    language_flag IN INTEGER);

PL/SQL8 offers a second, overloaded version of DBMS_SQL.PARSE, which comes in handy when you have very large SQL statements. If your SQL statement exceeds the largest possible contiguous allocation on your system (and it is machine-dependent) or 32K bytes (the maximum size for VARCHAR2), use this version of the PARSE procedure:

PROCEDURE DBMS_SQL.PARSE
   (cursor_handle IN INTEGER,
    SQL_statement IN DBMS_SQL.VARCHAR2S,
    lb IN INTEGER,
    ub IN INTEGER,
    lfflg IN BOOLEAN,
    language_flag IN INTEGER);

C.14.16 The VARIABLE_VALUE procedure

The VARIABLE_VALUE procedure lets you retrieve the value of a named variable from the specified PL/SQL block. The overloaded specification for this procedure supports three datatypes, as follows:

PROCEDURE DBMS_SQL.VARIABLE_VALUE
   (cursor_handle IN INTEGER,
    variable_name IN VARCHAR2,
    value OUT NUMBER);

PROCEDURE DBMS_SQL.VARIABLE_VALUE
   (cursor_handle IN INTEGER,
    variable_name IN VARCHAR2,
    value OUT DATE);

PROCEDURE DBMS_SQL.VARIABLE_VALUE
   (cursor_handle IN INTEGER, 
    variable_name IN VARCHAR2, 
    value OUT VARCHAR2);


C.13 DBMS_SNAPSHOT		C.15 DBMS_TRANSACTION