Using Dynamic SQL
This chapter describes the dynamic SQL package, DBMS_SQL. The following topics are described in this chapter:
Overview
You can 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 permits you to create procedures that are more general purpose. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.
Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.
Creating the DBMS_SQL Package
To create the DBMS_SQL package, submit the DBMSSQL.SQL and PRVTSQL.PLB scripts when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script. See page 7 - 39 for information on granting the necessary privileges to users who will be executing this package.
Using DBMS_SQL
The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI). You should refer to the Programmer's Guide to the Oracle Call Interface for additional information on the concepts presented in this chapter.
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:
- The OCI uses bind by address, while the DBMS_SQL package uses bind by value.
- The current release of the DBMS_SQL package does not provide DESCRIBE or CANCEL cursor procedures, nor support for the array interface.
- Indicator variables are not required because nulls are 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. Each of the functions and procedures used in this example is described later in this chapter. A more detailed example, which shows how you can use the DBMS_SQL package to build a query statement dynamically, begins ![[*]](jump.gif)
. This example does not actually require the use of dynamic SQL, because the text of the statement is known at compile time. However, it illustrates the concepts of this package.
/* The DEMO procedure deletes all of the employees from the EMP
* table whose salaries are greater than the salary that you
* specify when you run DEMO. */
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.v7);
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;
Execution Flow
The typical flow of procedure calls is shown in Figure 11-1. A general explanation of these procedures follows. Each of these procedures is described in greater detail starting .
Figure 10 - 1. DBMS_SQL Execution Flow
OPEN_CURSOR
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 DBMS_SQL package.
PARSE
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. A complete explanation of how SQL statements are parsed is included in the Oracle7 Server Tuning manual.
You can parse any data manipulation language or data definition language statements. Data definition language statements are executed on the parse, which performs the implied commit.
Attention: When parsing a data definition language statement to drop a package or a procedure, a deadlock can occur if a procedure in the package is still in use by you. 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 will timeout after five minutes.
BIND_VARIABLE
Many data manipulation language 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 the BIND_VARIABLE procedure to supply the value of a variable in your program to the placeholder. When the SQL statement is subsequently executed, Oracle uses the data that your program has placed in the output and input, or bind, variables.
DEFINE_COLUMN
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 DEFINE_COLUMN 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
You use the 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 to fetch from the LONG column.
EXECUTE
Call the EXECUTE function to execute your SQL statement.
FETCH_ROWS
Call FETCH_ROWS to retrieve the rows that satisfy the query. Each successive fetch retrieves another row, 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 iteration.
VARIABLE_VALUE
For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures, call VARIABLE_VALUE to retrieve the values assigned to the output variables of the PL/SQL procedures when they were executed.
COLUMN_VALUE_LONG
To fetch just part of a LONG database column (which can be up to two gigabytes in size), you use the COLUMN_VALUE_LONG procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.
CLOSE_CURSOR
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, 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, the memory used by that cursor remains allocated even though it is no longer needed.
Security
This section describes the security domain for DBMS_SQL procedures when you are using the Oracle Server or Trusted Oracle Server.
For Oracle Server Users
Any DBMS_SQL procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any DBMS_SQL procedures called from a stored procedure are executed using the privileges of the owner of the stored procedure.
For Trusted Oracle Server Users
Any DBMS_SQL procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any DBMS_SQL procedures called from a stored procedure are executed using the discretionary access control (DAC) and system privileges of the owner of the stored procedure and the mandatory access control (MAC) privileges of the current user.
Procedures and Functions
Table 10 - 1 provides a brief description of each of the procedures and functions associated with the DBMS_SQL package, which are described in detail later in this chapter. An example of how these functions can be used begins .
| Function/Procedure
| Description
| Refer to Page
|
| OPEN_CURSOR
| Return cursor ID number of new
cursor.
| 10 - 9
|
| PARSE
| Parse given statement.
| 10 - 9
|
| BIND_VARIABLE
| Bind a given value to a given
variable.
| 10 - 10
|
| DEFINE_COLUMN
| Define a column to be selected from the given cursor, used only with
SELECT statements.
| 10 - 12
|
| DEFINE_COLUMN_LONG
| Define a LONG column to be selected from the given cursor, used only with SELECT statements.
| 10 - 14
|
| EXECUTE
| Execute a given cursor.
| 10 - 14
|
| EXECUTE_AND_FETCH
| Execute a given cursor and fetch rows.
| 10 - 15
|
| FETCH_ROWS
| Fetch a row from a given cursor.
| 10 - 15
|
| COLUMN_VALUE
| Returns value of the cursor element for a given position in a cursor.
| 10 - 16
|
| COLUMN_VALUE_LONG
| Returns a selected part of a LONG column, that has been defined
using DEFINE_COLUMN_LONG.
| 10 - 17
|
| VARIABLE_VALUE
| Returns value of named variable for given cursor.
| 10 - 18
|
| IS_OPEN
| Returns TRUE if given cursor is open.
| 10 - 20
|
| CLOSE_CURSOR
| Closes given cursor and frees memory.
| 10 - 20
|
| LAST_ERROR_POSITION
| Returns byte offset in the SQL
statement text where the error
occurred.
| 10 - 21
|
| LAST_ROW_COUNT
| Returns cumulative count of the number of rows fetched.
| 10 - 21
|
| LAST_ROW_ID
| Returns ROWID of last row
processed.
| 10 - 21
|
| LAST_SQL_
FUNCTION_CODE
| Returns SQL function code for
statement.
| 10 - 21
|
Table 10 - 1. DBMS_SQL Package Functions and Procedures
OPEN_CURSOR Function
Call OPEN_CURSOR to open a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR.
You can use cursors to execute the same SQL statement repeatedly or to execute 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 reusing it.
Syntax
The OPEN_CURSOR function returns the cursor ID number of the new cursor. The syntax for this function is
DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
PARSE Procedure
Call PARSE to parse the given statement in the given cursor. Currently, unlike the OCI OPARSE call, which supports deferred parsing, all statements are parsed immediately. This may change in future versions; you should not rely on this behavior.
Syntax
The parameters for the PARSE procedure are described in Table 10 - 2. The syntax for this procedure is
DBMS_SQL.PARSE(
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER);
| Parameter
| Description
|
| c
| Specify the ID number of the cursor in which to parse the statement.
|
| statement
| Provide the SQL statement to be parsed. Your SQL statement should not include a final semicolon.
|
| language_
flag
| This parameter determines how Oracle handles the SQL statement. The following options are recognized for this parameter:
V6 - specified Version 6 behavior
V7 - specifies Oracle7 behavior
NATIVE - specifies normal behavior for the database to which the program is connected.
|
Table 10 - 2. DBMS_SQL.PARSE Procedure Parameters
BIND_VARIABLE Procedures
Call BIND_VARIABLE to bind a given value 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, the given bind value must be valid for the variable type. Bind values for OUT variables are ignored.
The bind variables of a SQL statement are identified by their names. When binding a value to a bind variable, the string identifying the bind variable in the statement must contain a leading colon, as shown in the following example:
SELECT emp_name FROM emp WHERE SAL > :X;
For this example, the corresponding bind call would look similar to
BIND_VARIABLE(cursor_name, ':X', 3500);
Syntax
The parameters for the BIND_VARIABLE procedures are described in Table 10 - 3. The syntax for these procedures is shown below. Notice that the BIND_VARIABLE procedure is overloaded to accept different datatypes.
DBMS_SQL.BIND_VARIABLE(c IN INTEGER,
name IN VARCHAR2,
value IN <datatype>);
where <datatype> can be any one of the following types:
NUMBER
DATE
MLSLABEL
VARCHAR2
The following syntax is also supported for the BIND_VARIABLE procedure. The square brackets [] indicate optional parameters.
DBMS_SQL.BIND_VARIABLE(
c IN INTEGER,
name IN VARCHAR2,
value IN VARCHAR2
[,out_value_size IN INTEGER]);
DBMS_SQL.BIND_VARIABLE_CHAR(
c IN INTEGER,
name IN VARCHAR2,
value IN CHAR
[,out_value_size IN INTEGER]);
DBMS_SQL.BIND_VARIABLE_RAW(
c IN INTEGER,
name IN VARCHAR2,
value IN RAW
[,out_value_size IN INTEGER]);
DBMS_SQL.BIND_VARIABLE_ROWID(
c IN INTEGER,
name IN VARCHAR2,
value IN ROWID);
| Parameter
| Description
|
| c
| Specify the ID number of the cursor to which you want to bind a value.
|
| name
| Provide the name of the variable in the statement.
|
| value
| Provide the 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
| The maximum expected OUT value size, in bytes, for the VARCHAR2, RAW, CHAR OUT or IN/OUT variable. If no size is given, the length of the current value is used.
|
Table 10 - 3. DBMS_SQL.BIND_VARIABLE Procedure Parameters
Processing Queries
If you are using dynamic SQL to process a query, you must perform the following steps:
1. Specify the variables that are to receive the values returned by the SELECT statement by calling DEFINE_COLUMN.
2. Execute your SELECT statement by calling EXECUTE.
3. Call FETCH_ROWS (or EXECUTE_AND_FETCH) to retrieve the rows that satisfied your query.
4. Call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call for your query. If you used anonymous blocks containing calls to PL/SQL procedures, you must call VARIABLE_VALUE to retrieve the values assigned to the output variables of these procedures.
DEFINE_COLUMN Procedure
This procedure is only used with SELECT cursors. Call DEFINE_COLUMN to define a column to be selected from the given cursor. The column being defined is identified by its relative position in the SELECT list of the statement in the given cursor. The type of the COLUMN value determines the type of the column being defined.
Syntax
The parameters for the DEFINE_COLUMN procedure are described in Table 10 - 4. The syntax for this procedure is shown below. Notice that this procedure is overloaded to accept different datatypes.
DBMS_SQL.DEFINE_COLUMN(
c IN INTEGER,
position IN INTEGER
column IN <datatype>);
where <datatype> can be any one of the following types:
NUMBER
DATE
MLSLABEL
The following syntax is also supported for the DEFINE_COLUMN procedure:
DBMS_SQL.DEFINE_COLUMN(
c IN INTEGER,
position IN INTEGER,
column IN VARCHAR2,
column_size IN INTEGER);
DBMS_SQL.DEFINE_COLUMN_CHAR(
c IN INTEGER,
position IN INTEGER,
column IN CHAR,
column_size IN INTEGER);
DBMS_SQL.DEFINE_COLUMN_RAW(
c IN INTEGER,
position IN INTEGER,
column IN RAW,
column_size IN INTEGER);
DBMS_SQL.DEFINE_COLUMN_ROWID(
c IN INTEGER,
position IN INTEGER,
column IN ROWID);
| Parameter
| Description
|
| c
| The ID number of the cursor for the row being defined to be selected.
|
| position
| The relative position of the column in the row being defined. The first column in a statement has position 1.
|
| column
| The value of the column being defined. The type of this value determines the type for the column being defined.
|
| column_size
| The maximum expected size of the column value, in bytes, for columns of type VARCHAR2, CHAR, and RAW.
|
Table 10 - 4. DBMS_SQL.DEFINE_COLUMN Procedure Parameters
DEFINE_COLUMN_
LONG Procedure
Call this procedure to define a LONG column for a SELECT cursor. The column being defined is identified by its relative position in the SELECT list of the statement for the given cursor. The type of the COLUMN value determines the type of the column being defined.
Syntax
The parameters of DEFINE_COLUMN_LONG are described in Table 10 - 5. The syntax is
DBMS_SQL.DEFINE_COLUMN_LONG(
c IN INTEGER,
position IN INTEGER);
| Parameter
| Description
|
| c
| The ID number of the cursor for the row being defined to be selected.
|
| position
| The relative position of the column in the row being defined. The first column in a statement has position 1.
|
Table 10 - 5. DBMS_SQL.DEFINE_COLUMN_LONG Procedure Parameters
EXECUTE Function
Call EXECUTE to execute a given cursor. This function accepts the ID number of the cursor and returns the number of rows processed. The return value is only valid for INSERT, UPDATE, and DELETE statements; for other types of statements, including DDL, the return value is undefined and should be ignored.
Syntax
The syntax for the EXECUTE function is
DBMS_SQL.EXECUTE (
c IN INTEGER)
RETURN INTEGER;
EXECUTE_AND_FETCH Function
Call EXECUTE_AND_FETCH to execute the given cursor and fetch rows. This function provides the same functionality as calling EXECUTE and then calling FETCH_ROWS. Calling EXECUTE_AND_FETCH instead, however, may cut down on the number of network round-trips when used against a remote database.
Syntax
The EXECUTE_AND_FETCH function returns the number of rows actually fetched. The parameters for this procedure are described in Table 10 - 6, and the syntax is shown below.
DBMS_SQL.EXECUTE_AND_FETCH(
c IN INTEGER,
exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
| Parameter
| Description
|
| c
| Specify the ID number of the cursor to execute and fetch.
|
| exact
| Set to TRUE to raise an exception if the number of rows actually matching the query differs from one. Even if an exception is raised, the rows are still fetched and available.
|
Table 10 - 6. DBMS_SQL.EXECUTE_AND_FETCH Function Parameters
FETCH_ROWS Function
Call FETCH_ROWS to fetch a row from a given cursor. You can call FETCH_ROWS repeatedly as long as there are rows remaining to be fetched. These rows are retrieved into a buffer, and must be read by calling COLUMN_VALUE, for each column, after each call to FETCH_ROWS.
Syntax
The FETCH_ROWS function accepts the ID number of the cursor to fetch, and returns the number of rows actually fetched. The syntax for this function is shown below.
DBMS_SQL.FETCH_ROWS(
c IN INTEGER)
RETURN INTEGER;
COLUMN_VALUE Procedure
This procedure returns the value of the cursor element for a given position in a given cursor. This procedure is used to access the data fetched by calling FETCH_ROWS.
Syntax
The parameters for the COLUMN_VALUE procedure are described in Table 10 - 7. The syntax for this procedure is shown below. The square brackets [] indicate optional parameters.
DBMS_SQL.COLUMN_VALUE(
c IN INTEGER,
position IN INTEGER,
value OUT <datatype>,
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
where <datatype> can be any one of the following types:
NUMBER
DATE
MLSLABEL
VARCHAR2
The following syntax is also supported for the COLUMN_VALUE procedure:
DBMS_SQL.COLUMN_VALUE_CHAR(
c IN INTEGER,
position IN INTEGER,
value OUT CHAR
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
DBMS_SQL.COLUMN_VALUE_RAW(
c IN INTEGER,
position IN INTEGER,
value OUT RAW
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
dbms_sql.column_value_rowid(
c IN INTEGER,
position IN INTEGER,
value OUT ROWID
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
| Parameter
| Mode
| Description
|
| c
| IN
| Specify the ID number of the cursor from which you are fetching the values.
|
| position
| IN
| Specify the relative position of the column in the
cursor. The first column in a statement has position 1.
|
| value
| OUT
| Returns the value at the specified column and row.
If the row number specified is greater than the total number of rows fetched, you receive an error message.
Oracle raises exception ORA-06562, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to DEFINE_COLUMN.
|
| column_
error
| OUT
| Returns any error code for the specified column
value.
|
| actual_
length
| OUT
| Returns the actual length, before any truncation,
of the value in the specified column.
|
Table 10 - 7. DBMS_SQL.COLUMN_VALUE Procedure Parameters
COLUMN_VALUE_LONG Procedure
This procedure returns the value of the cursor element for a given position, offset, and size in a given cursor. This procedure is used to access the data fetched by calling FETCH_ROWS.
Syntax
The parameters of the COLUMN_VALUE_LONG procedure are described in Table 10 - 8. The syntax of the procedure is
DBMS_SQL.COLUMN_VALUE_LONG(
c IN INTEGER,
position IN INTEGER,
length IN INTEGER,
offset IN INTEGER,
value OUT VARCHAR2,
value_length OUT INTEGER);
| Parameter
| Description
|
| c
| The ID number of the cursor for the row being defined to be selected.
|
| position
| The relative position of the column in the row being defined. The first column in a statement has position 1.
|
| length
| The length in bytes of the segment of the column value that is to be selected.
|
| offset
| The byte position in the LONG column at which the SELECT is to start.
|
| value
| The value of the column segment to be SELECTed.
|
| value_length
| The (returned) length of the value that was SELECTed.
|
Table 10 - 8. DBMS_SQL.COLUMN_VALUE_LONG Procedure Parameters
VARIABLE_VALUE Procedure
This procedure returns the value of the named variable for a given cursor. It is also used to return the values of bind variables inside PL/SQL blocks.
Syntax
The parameters for the VARIABLE_VALUE procedure are described in Table 10 - 9. The syntax for this procedure is shown below.
DBMS_SQL.VARIABLE_VALUE(
c IN INTEGER,
name IN VARCHAR2,
value OUT <datatype>);
where <datatype> can be any one of the following types:
NUMBER
DATE
MLSLABEL
VARCHAR2
The following syntax is also supported for the VARIABLE_VALUE procedure:
DBMS_SQL.VARIABLE_VALUE_CHAR(
c IN INTEGER,
name IN VARCHAR2,
value OUT CHAR);
DBMS_SQL.VARIABLE_VALUE_RAW(
c IN INTEGER,
name IN VARCHAR2,
value OUT RAW);
DBMS_SQL.VARIABLE_VALUE_ROWID(
c IN INTEGER,
name IN VARCHAR2,
value OUT ROWID);
| Parameter
| Mode
| Description
|
| c
| IN
| Specify the ID number of the cursor from which to get the values.
|
| name
| IN
| Specify the name of the variable for which you are retrieving the value.
|
| value
| OUT
| Returns the value of the variable for the specified position.
Oracle raises exception ORA-06562, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to BIND_VARIABLE.
|
Table 10 - 9. DBMS_SQL.VARIABLE_VALUE Procedure Parameters
Processing Updates, Inserts and Deletes
If you are using dynamic SQL to process an INSERT, UPDATE, or DELETE, you must perform the following steps:
1. You must first execute your INSERT, UPDATE, or DELETE statement by calling EXECUTE. The EXECUTE procedure is described .
2. If you used anonymous blocks containing calls to PL/SQL procedures, you must call VARIABLE_VALUE to retrieve the values assigned to the output variables of these procedures. The VARIABLE_VALUE procedure is described .
IS_OPEN Function
The IS_OPEN function returns TRUE if the given cursor is currently open.
Syntax
The IS_OPEN function accepts the ID number of a cursor, and returns TRUE if the cursor is currently open, or FALSE if it is not. The syntax for this function is
DBMS_SQL.IS_OPEN(
c IN INTEGER)
RETURN BOOLEAN;
CLOSE_CURSOR Procedure
Call CLOSE_CURSOR to close a given cursor.
Syntax
The parameter for the CLOSE_CURSOR procedure is described in Table 10 - 10. The syntax for this procedure is
DBMS_SQL.CLOSE_CURSOR(
c IN OUT INTEGER);
| Parameter
| Mode
| Description
|
| c
| IN
| Specify the ID number of the cursor that you want to close.
|
|
| OUT
| The cursor is set to null. After you call
CLOSE_CURSOR, the memory allocated to the
cursor is released and you can no longer fetch from that cursor.
|
Table 10 - 10. DBMS_SQL.CLOSE_CURSOR Procedure Parameters
Locating Errors
There are additional functions in the DBMS_SQL package for obtaining information about the last referenced cursor in the session. The values returned by these functions are only meaningful immediately after a SQL statement is executed. In addition, some error-locating functions are only meaningful after certain DBMS_SQL calls. For example, you call LAST_ERROR_POSITION immediately after a PARSE.
LAST_ERROR_POSITION Function
Returns the byte offset in the SQL statement text where the error occurred. The first character in the SQL statement is at position 0.
DBMS_SQL.LAST_ERROR_POSITION RETURN INTEGER;
Call this function after a PARSE call, before any other DBMS_SQL procedures or functions are called.
LAST_ROW_COUNT Function
Returns the cumulative count of the number of rows fetched.
DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;
Call this function after a FETCH_ROWS or an EXECUTE_AND_FETCH call. If called after an EXECUTE call, the value returned will be zero.
LAST_ROW_ID Function
Returns the ROWID of the last row processed.
DBMS_SQL.LAST_ROW_ID RETURN ROWID;
Call this function after a FETCH_ROWS or an EXECUTE_AND_FETCH call.
LAST_SQL_FUNCTION_CODE Function
Returns the SQL function code for the statement. These codes are listed in the Programmer's Guide to the Oracle Call Interface.
DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;
You should call this function immediately after the SQL statement is executed; otherwise, the return value is undefined.
Examples
This section provides example procedures that make use of the DBMS_SQL package.
Example 1
The following sample procedure is passed a SQL statement, which it then parses and executes:
CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
--DDL statements are executed by the parse call, which
--performs the implied commit
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.V7);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
Creating such a procedure allows you to perform the following operations:
- The SQL statement can be dynamically generated at runtime by the calling program.
- The SQL statement can be a DDL statement.
For example, after creating this procedure, you could make the following call:
exec('create table acct(c1 integer)');
You could even call this procedure remotely, as shown in the following example. This allows you to perform remote DDL.
exec@hq.com('CREATE TABLE acct(c1 INTEGER)');
Example 2
The following sample procedure is passed the names of a source and a destination table, and copies the rows from the source table to the destination table. This sample procedure assumes that both the source and destination tables have the following columns:
ID of type NUMBER
NAME of type VARCHAR2(30)
BIRTHDATE of type DATE
This procedure does not specifically require the use of dynamic SQL; however, it illustrates the concepts of this package.
CREATE OR REPLACE PROCEDURE copy(source IN VARCHAR2,
destination IN VARCHAR2) is
-- This procedure copies rows from a given source table to a
-- given destination table assuming that both source and
-- destination tables have the following columns:
-- - ID of type NUMBER,
-- - NAME of type VARCHAR2(30),
-- - BIRTHDATE of type DATE.
id NUMBER;
name VARCHAR2(30);
birthdate DATE;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
-- prepare a cursor to select from the source table
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,
'SELECT id, name, birthdate FROM ' || source,
DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate);
ignore := DBMS_SQL.EXECUTE(source_cursor);
-- prepare a cursor to insert into the destination table
destination_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(destination_cursor,
'INSERT INTO ' || destination ||
' VALUES (:id, :name, :birthdate)',
DBMS_SQL.V7);
-- fetch a row from the source table and
-- insert it into the destination table
LOOP
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate);
-- bind the row into the cursor that inserts into the
-- destination table
-- You could alter this example to require the use of
-- dynamic SQL by inserting an if condition before the
-- bind.
DBMS_SQL.BIND_VARIABLE(destination_cursor, 'id', id);
DBMS_SQL.BIND_VARIABLE(destination_cursor, 'name', name);
DBMS_SQL.BIND_VARIABLE(destination_cursor, 'birhtdate',
birthdate);
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- no more row to copy
EXIT;
END IF;
END LOOP;
-- commit and close all cursors
COMMIT;
DBMS_SQL.CLOSE_CURSOR(source_cursor);
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
END IF;
RAISE;
END;
