Using DBMS_SQL → Interface and the DBMS_SQL package. These differences include the following: The OCI uses bind by address … , while the DBMS_SQL package uses bind by value. With DBMS_SQL you must call VARIABLE_VALUE to … release of the DBMS_SQL package does not provide CANCEL cursor procedures. Indicator variables are … DBMS_SQL
69 DBMS_SQL → whose name is not known until runtime. Additionally, DBMS_SQL enables you to parse any data … within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package. Note: Oracle8 … i introduces native dynamic SQL, an alternative to DBMS_SQL. Using native dynamic SQL, you can … replace DBMS_SQL.
Summary of DBMS_SQL Subprograms → Table 69-1 DBMS_SQL Subprograms Subprogram Description OPEN_CURSOR Function Returns cursor ID … Procedure Describes the columns for a cursor opened and parsed through DBMS_SQL. CLOSE_CURSOR Procedure
Advantages of the DBMS_SQL Package → The DBMS_SQL package provides the following advantages over native dynamic SQL: DBMS_SQL is … Supported in Client-Side Programs The DBMS_SQL package is supported in client-side programs, but native … dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL … variable,
Constants, Types, and Exceptions for DBMS_SQL → Constants v6 constant INTEGER:= 0; native constant INTEGER:= 1; v7 constant INTEGER:= 2; Types TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER; TYPE desc_rec IS RECORD ( col_type BINARY_INTEGER:= 0, col_max_len BINARY_INTEGER:= 0, col_name VARCHAR2(32):= '', col_name_len BINARY_INTEGER:= 0, col_schema_name VARCHAR2(32):= '', col_schema_name_len BINARY_INTEGER:= 0, col_precision BINARY_INTEGER:=
Choosing Between Native Dynamic SQL and the DBMS_SQL Package → DBMS_SQL package. Native dynamic SQL lets you place dynamic SQL statements directly into PL/SQL code. These … information about the COMPATIBLE parameter. The DBMS_SQL package is a PL/SQL library that offers an … API to execute SQL statements dynamically. The DBMS_SQL package has procedures to open a cursor … , parse a cursor,
Examples of DBMS_SQL Package Code and Native Dynamic SQL Code → the DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are … table. Table 8-2 shows sample code that accomplishes this query using the DBMS_SQL package and … native dynamic SQL. Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL DBMS_SQL Query … this DML operation
OPEN_CURSOR Function → This procedure opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR. You can use cursors to run the same SQL statement repeatedly or to run a new SQL statement. When a cursor is reused, the contents of the corresponding cursor data area are reset when the new SQL statement is parsed. It is never necessary to close and reopen a cursor before
Syntax → DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
Parameters → Table 69-3 BIND_VARIABLE Procedure Parameters Parameter Description c ID number of the cursor to which you want to bind a value. name Name of the variable in the statement. value Value that you want to bind to the variable in the cursor. For IN and IN / OUT variables, the value has the same type as the type of the value being passed in for this parameter. out_value_size Maximum expected OUT value
Scalar and LOB Types for Collections → number of rows into it using DBMS_SQL. (These are the same types as you can specify for the BIND_ARRAY
Pragmas → pragma restrict_references(define_array,RNDS,WNDS); The subsequent FETCH_ROWS call fetch \"count\" rows. When the COLUMN_VALUE call is made, these rows are placed in positions indx, indx+1, indx+2, and so on. While there are still rows coming, the user keeps issuing FETCH_ROWS / COLUMN_VALUE calls. The rows keep accumulating in the table specified as an argument in the COLUMN_VALUE call.
EXECUTE_AND_FETCH Function → This function executes the given cursor and fetches rows. This function provides the same functionality as calling EXECUTE and then calling FETCH_ROWS. Calling EXECUTE_AND_FETCH instead, however, may reduce the number of network round-trips when used against a remote database. The EXECUTE_AND_FETCH function returns the number of rows actually fetched.
Pragmas → pragma restrict_references(fetch_rows,WNDS);
Parameters → Table 69-11 COLUMN_VALUE Procedure Parameters Parameter Description c ID number of the cursor from which you are fetching the values. position Relative position of the column in the cursor. The first column in a statement has position 1. value Returns the value at the specified column and row. If the row number specified is greater than the total number of rows fetched, then you receive an error message.
VARIABLE_VALUE Procedure → This procedure returns the value of the named variable for a given cursor. It is used to return the values of bind variables inside PL/SQL blocks or DML statements with returning clause.
BIND_VARIABLE and BIND_ARRAY Procedures → These two procedures bind a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement. If the variable is an IN or IN / OUT variable or an IN collection, then the given bind value must be valid for the variable or array type. Bind values for OUT variables are ignored. The bind variables or collections of a SQL statement are identified by their
Syntax → DBMS_SQL.BIND_VARIABLE ( c IN INTEGER, name IN VARCHAR2, value IN ) Where can be any one of the following types: NUMBER DATE VARCHAR2 CHARACTER SET ANY_CS BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID Notice that BIND_VARIABLE is overloaded to accept different datatypes. See Also: Oracle9i Application Developer's Guide - Large Objects (LOBs)
Pragmas → pragma restrict_references(bind_variable,WNDS);
Syntax → DBMS_SQL.BIND_ARRAY ( c IN INTEGER, name IN VARCHAR2, IN [,index1 IN INTEGER, index2 IN INTEGER)] ); Where the and its corresponding can be any one of the following matching pairs: Number_Table Varchar2_Table Date_Table Blob_Table Clob_Table Bfile_Table Urowid_Table Notice that the BIND_ARRAY procedure is overloaded to accept different datatypes.