|Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
Part Number A76936-01
Oracle lets you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
DBMS_SQL enables you to parse any data manipulation language (DML) or data definition language (DDL) statement. Therefore, you can parse DDL statements directly using PL/SQL. For example, you might now choose to enter a
TABLE statement from within a stored procedure by using the
PARSE procedure supplied with the
For more information on native dynamic SQL, see PL/SQL User's Guide and Reference.
For a comparison of
The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).
PL/SQL differs somewhat from other common programming languages, such as C. For example, addresses (also called pointers) are not user-visible in PL/SQL. As a result, there are some differences between the Oracle Call Interface and the
DBMS_SQL package. These differences include the following:
DBMS_SQLpackage uses bind by value.
DBMS_SQLyou must call
VARIABLE_VALUEto retrieve the value of an
OUTparameter for an anonymous block, and you must call
COLUMN_VALUEafter fetching rows to actually retrieve the values of the columns in the rows into your program.
DBMS_SQLpackage does not provide
NULLsare fully supported as values of a PL/SQL variable.
A sample usage of the
DBMS_SQL package is shown below. For users of the Oracle Call Interfaces, this code should seem fairly straightforward.
This example does not actually require the use of dynamic SQL, because the text of the statement is known at compile time. It does, however, illustrate the concepts of this package.
DEMO procedure deletes all of the employees from the
EMP table whose salaries are greater than the salary that you specify when you run
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS cursor_name INTEGER; rows_processed INTEGER; BEGIN cursor_name := dbms_sql.open_cursor; DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x', dbms_sql.native); DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary); rows_processed := dbms_sql.execute(cursor_name); DBMS_SQL.close_cursor(cursor_name); EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR(cursor_name); END;
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 := 0, col_scale BINARY_INTEGER := 0, col_charsetid BINARY_INTEGER := 0, col_charsetform BINARY_INTEGER := 0, col_null_ok BOOLEAN := TRUE); TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;
type Number_Table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; type Varchar2_Table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER; type Date_Table IS TABLE OF DATE INDEX BY BINARY_INTEGER; type Blob_Table IS TABLE OF BLOB INDEX BY BINARY_INTEGER; type Clob_Table IS TABLE OF CLOB INDEX BY BINARY_INTEGER; type Bfile_Table IS TABLE OF BFILE INDEX BY BINARY_INTEGER; type Urowid_Table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
This exception is raised by procedure
VARIABLE_VALUE when the type of the given
OUT parameter (for where to put the requested value) is different from the type of the value.
To process a SQL statement, you must have an open cursor. When you call the
OPEN_CURSOR function, you receive a cursor
ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the
Every SQL statement must be parsed by calling the
PARSE procedure. Parsing the statement checks the statement's syntax and associates it with the cursor in your program.
Oracle8 Concepts provides an explanation of how SQL statements are parsed.
You can parse any DML or DDL statement. DDL statements are run on the parse, which performs the implied commit.
When parsing a DDL statement to drop a package or a procedure, a deadlock can occur if you're still using a procedure in the package. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock timeouts after five minutes.
Many DML statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied.
For each placeholder in the SQL statement, you must call one of the bind procedures,
BIND_ARRAY, to supply the value of a variable in your program (or the values of an array) to the placeholder. When the SQL statement is subsequently run, Oracle uses the data that your program has placed in the output and input, or bind, variables.
DBMS_SQL can run a DML statement multiple times -- each time with a different bind variable. The
BIND_ARRAY procedure lets you bind a collection of scalars, each value of which is used as an input variable once per
EXECUTE. This is similar to the array interface supported by the OCI.
The columns of the row being selected in a
SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call one of the define procedures (
DEFINE_ARRAY) to specify the variables that are to receive the
SELECT values, much the way an
INTO clause does for a static query.
DEFINE_COLUMN_LONG procedure to define
LONG columns, in the same way that
DEFINE_COLUMN is used to define non-
LONG columns. You must call
DEFINE_COLUMN_LONG before using the
COLUMN_VALUE_LONG procedure to fetch from the
DEFINE_ARRAY procedure to define a PL/SQL collection into which you want to fetch rows in a single
DEFINE_ARRAY provides an interface to fetch multiple rows at one fetch. You must call
DEFINE_ARRAY before using the
COLUMN_VALUE procedure to fetch the rows.
EXECUTE function to run your SQL statement.
FETCH_ROWS function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch is unable to retrieve anymore rows. Instead of calling
EXECUTE and then
FETCH_ROWS, you may find it more efficient to call
EXECUTE_AND_FETCH if you are calling
EXECUTE for a single execution.
For queries, call
COLUMN_VALUE to determine the value of a column retrieved by the
FETCH_ROWS call. For anonymous blocks containing calls to
SQL procedures or DML statements with
returning clause, call
VARIABLE_VALUE to retrieve the values assigned to the output variables when statements were run.
To fetch just part of a
LONG database column (which can be up to two gigabytes in size), use the
COLUMN_VALUE_LONG procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.
When you no longer need a cursor for a session, close the cursor by calling
CLOSE_CURSOR. If you are using an Oracle Open Gateway, then you may need to close cursors at other times as well. Consult your Oracle Open Gateway documentation for additional information.
If you neglect to close a cursor, then the memory used by that cursor remains allocated even though it is no longer needed.
Definer rights modules run under the privileges of the owner of the module.
DBMS_SQL subprograms called from definer rights modules run with respect to the schema in which the module is defined.
Invoker rights modules run under the privileges of the invoker of the module. Therefore,
DBMS_SQL subprograms called from invoker rights modules run under the privileges of the invoker of the module.
When a module has
AUTHID set to
current_user, the unqualified names are resolved with respect to the invoker's schema.
income is an invoker rights stored procedure in
USER1's schema, and
USER2 has been granted
EXECUTE privilege on it.
CREATE PROCEDURE income(amount number) AUTHID current_user IS c number; n number; BEGIN c:= dbms_sql.open_cursor; dbms_sql.parse(c, 'insert into accts(''income'', :1)', dbms_sql.native); dbms_sql.bind_variable(c, '1', amount); n := dbms_sql.execute(c); dbms_sql.close_cursor(c); END;
USER1's privileges are used, and name resolution of unqualified names is done with respect to
USER2's privileges are used, and name resolution of unqualified names (such as
accts) is done with respect to
DBMS_SQL subprograms called from an anonymous PL/SQL block are run using the privileges of the current user.