Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01 |
|
DBMS_SQL , 2 of 2
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 reusing it.
DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
pragma restrict_references(open_cursor,RNDS,WNDS);
This function returns the cursor ID number of the new cursor.
This procedure parses the given statement in the given cursor. All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.
There are two versions of the PARSE
procedure: one uses a VARCHAR2
statement as an argument, and the other uses a VARCHAR2S
(table of VARCHAR2
) as an argument.
The size limit for parsing SQL statements with the preceding syntax is 32KB.
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER);
The PARSE
procedure also supports the following syntax for large SQL statements:
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2S, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER);
To parse SQL statements larger than 32 KB, DBMS_SQL
makes use of PL/SQL tables to pass a table of strings to the PARSE
procedure. These strings are concatenated and then passed on to the Oracle server.
You can declare a local variable as the VARCHAR2S
table-item type, and then use the PARSE
procedure to parse a large SQL statement as VARCHAR2S
.
The definition of the VARCHAR2S
datatype is:
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
If you create a type/procedure/function/package usingDBMS_SQL
that has compilation warnings, anORA-24344
exception is raised, and the procedure is still created.
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 names. When binding a value to a bind variable or bind array, the string identifying it 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); or BIND_VARIABLE (cursor_name, 'X', 3500);
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 VARCHAR2 CHARACTER SET ANY_CS BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID
Notice that BIND_VARIABLE
is overloaded to accept different datatypes.
pragma restrict_references(bind_variable,WNDS);
The following syntax is also supported for BIND_VARIABLE
. The square brackets [] indicate an optional parameter for the BIND_VARIABLE
function.
DBMS_SQL.BIND_VARIABLE ( c IN INTEGER, name IN VARCHAR2, value IN VARCHAR2 CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);
To bind CHAR
, RAW
, and ROWID
data, you can use the following variations on the syntax:
DBMS_SQL.BIND_VARIABLE_CHAR ( c IN INTEGER, name IN VARCHAR2, value IN CHAR CHARACTER SET ANY_CS [,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);
Bulk selects, inserts, updates, and deletes can enhance the performance of applications by bundling many calls into one. The DBMS_SQL
package lets you work on collections of data using the PL/SQL table type.
Table items are unbounded homogeneous collections. In persistent storage, they are like other relational tables and have no intrinsic ordering. But when a table item is brought into the workspace (either by querying or by navigational access of persistent data), or when it is created as the value of a PL/SQL variable or parameter, its elements are given subscripts that can be used with array-style syntax to get and set the values of elements.
The subscripts of these elements need not be dense, and can be any number including negative numbers. For example, a table item can contain elements at locations -10, 2, and 7 only.
When a table item is moved from transient workspace to persistent storage, the subscripts are not stored; the table item is unordered in persistent storage.
At bind time the table is copied out from the PL/SQL buffers into local DBMS_SQL
buffers (the same as for all scalar types) and then the table is manipulated from the local DBMS_SQL
buffers. Therefore, if you change the table after the bind call, then that change does not affect the way the execute acts.
You can declare a local variable as one of the following table-item types, which are defined as public types in DBMS_SQL
.
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;
DBMS_SQL.BIND_ARRAY ( c IN INTEGER, name IN VARCHAR2, <table_variable> IN <datatype> [,index1 IN INTEGER, index2 IN INTEGER)] );
Where the <table_variable
> and its corresponding <datatype> can be any one of the following matching pairs:
<num_tab> Number_Table <vchr2_tab> Varchar2_Table <date_tab> Date_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <bfile_tab> Bfile_Table <urowid_tab> Urowid_Table
Notice that the BIND_ARRAY
procedure is overloaded to accept different datatypes.
For binding a range, the table must contain the elements that specify the range -- tab(index1) and tab(index2) -- but the range does not have to be dense. Index1 must be less than or equal to index2. All elements between tab(index1) and tab(index2) are used in the bind.
If you do not specify indexes in the bind call, and two different binds in a statement specify tables that contain a different number of elements, then the number of elements actually used is the minimum number between all tables. This is also the case if you specify indexes -- the minimum range is selected between the two indexes for all tables.
Not all bind variables in a query have to be array binds. Some can be regular binds and the same value are used for each element of the collections in expression evaluations (and so forth).
See Also:
"Examples 3, 4, and 5: Bulk DML" for examples of how to bind collections. |
This procedure defines a column to be selected from the given cursor. This procedure is only used with SELECT
cursors.
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.
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 BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID
Notice that DEFINE_COLUMN
is overloaded to accept different datatypes.
pragma restrict_references(define_column,RNDS,WNDS);
The following syntax is also supported for the DEFINE_COLUMN
procedure:
DBMS_SQL.DEFINE_COLUMN ( c IN INTEGER, position IN INTEGER, column IN VARCHAR2 CHARACTER SET ANY_CS, column_size IN INTEGER), urowid IN INTEGER;
To define columns with CHAR
, RAW
, and ROWID
data, you can use the following variations on the procedure syntax:
DBMS_SQL.DEFINE_COLUMN_CHAR ( c IN INTEGER, position IN INTEGER, column IN CHAR CHARACTER SET ANY_CS, 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);
This procedure defines the collection for column into which you want to fetch rows (with a FETCH_ROWS
call). This procedure lets you do batch fetching of rows from a single SELECT
statement. A single fetch call brings over a number of rows into the PL/SQL aggregate object.
When you fetch the rows, they are copied into DBMS_SQL
buffers until you run a COLUMN_VALUE
call, at which time the rows are copied into the table that was passed as an argument to the COLUMN_VALUE
call.
You can declare a local variable as one of the following table-item types, and then fetch any number of rows into it using DBMS_SQL
. (These are the same types as you can specify for the BIND_ARRAY
procedure.)
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;
DBMS_SQL.DEFINE_ARRAY ( c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype> cnt IN INTEGER, lower_bnd IN INTEGER);
Where <table_variable
> and its corresponding <datatype> can be any one of the following matching pairs:
<num_tab> Number_Table <vchr2_tab> Varchar2_Table <date_tab> Date_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <bfile_tab> Bfile_Table <urowid_tab> Urowid_Table
Notice that DEFINE_ARRAY
is overloaded to accept different datatypes.
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.
The count (cnt)
must be an integer greater than zero; otherwise an exception is raised. The indx
can be positive, negative, or zero. A query on which a DEFINE_ARRAY
call was issued cannot contain array binds.
See Also:
"Examples 6 and 7: Defining an Array" for examples of how to define collections. |
This procedure defines 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.
DBMS_SQL.DEFINE_COLUMN_LONG ( c IN INTEGER, position IN INTEGER);
This function executes 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.
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;
Parameter | Description |
---|---|
c |
Cursor ID number of the cursor to execute. |
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.
DBMS_SQL.EXECUTE_AND_FETCH ( c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;
pragma restrict_references(execute_and_fetch,WNDS);
This function fetches 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
.
The FETCH_ROWS
function accepts the ID number of the cursor to fetch, and returns the number of rows actually fetched.
DBMS_SQL.FETCH_ROWS ( c IN INTEGER) RETURN INTEGER;
Parameter | Description |
---|---|
c |
ID number. |
pragma restrict_references(fetch_rows,WNDS);
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
.
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 VARCHAR2 CHARACTER SET ANY_CS BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID
pragma restrict_references(column_value,RNDS,WNDS);
The following syntax is also supported for the COLUMN_VALUE
procedure:
DBMS_SQL.COLUMN_VALUE( c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype>);
Where the <table_variable
> and its corresponding <datatype> can be any one of these matching pairs:
<num_tab> Number_Table <vchr2_tab> Varchar2_Table <date_tab> Date_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <bfile_tab> Bfile_Table <urowid_tab> Urowid_Table
For columns containing CHAR
, RAW
, and ROWID
data, you can use the following variations on the syntax:
DBMS_SQL.COLUMN_VALUE_CHAR ( c IN INTEGER, position IN INTEGER, value OUT CHAR CHARACTER SET ANY_CS [,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]);
inconsistent_type
(ORA
-06562
) is raised if the type of the given OUT
parameter value
is different from the actual type of the value. This type was the given type when the column was defined by calling procedure DEFINE_COLUMN
.
This procedure gets part of the value of a long column.
DBMS_SQL.COLUMN_VALUE_LONG ( c IN INTEGER, position IN INTEGER, length IN INTEGER, offset IN INTEGER, value OUT VARCHAR2, value_length OUT INTEGER);
pragma restrict_references(column_value_long,RNDS,WNDS);
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.
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 VARCHAR2 CHARACTER SET ANY_CS BLOB CLOB CHARACTER SET ANY_CS BFILE UROWID
pragma restrict_references(variable_value,RNDS,WNDS);
The following syntax is also supported for the VARIABLE_VALUE
procedure:
DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, <table_variable> IN <datatype>);
Where the <table_variable
> and its corresponding <datatype> can be any one of these matching pairs:
<num_tab> Number_Table <vchr2_tab> Varchar2_Table <date_tab> Date_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <bfile_tab> Bfile_Table <urowid_tab> Urowid_Table
For variables containing CHAR
, RAW
, and ROWID
data, you can use the following variations on the syntax:
DBMS_SQL.VARIABLE_VALUE_CHAR ( c IN INTEGER, name IN VARCHAR2, value OUT CHAR CHARACTER SET ANY_CS); 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);
This function checks to see if the given cursor is currently open.
DBMS_SQL.IS_OPEN ( c IN INTEGER) RETURN BOOLEAN;
pragma restrict_references(is_open,RNDS,WNDS);
Parameter | Description |
---|---|
c |
Cursor ID number of the cursor to check. |
Return Value | Description |
---|---|
TRUE |
Given cursor is currently open. |
FALSE |
Given cursor is currently not open. |
This procedure describes the columns for a cursor opened and parsed through DBMS_SQL
.
The DBMS_SQL
package declares the DESC_REC
record type as follows:
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);
The DESC_TAB
type is a PL/SQL table of DESC_REC
records:
type desc_tab is table of desc_rec index by BINARY_INTEGER;
You can declare a local variable as the PL/SQL table type DESC_TAB
, and then call the DESCRIBE_COLUMNS
procedure to fill in the table with the description of each column. All columns are described; you cannot describe a single column.
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB);
See Also:
"Example 8: Describe Columns" illustrates how to use |
This procedure closes a given cursor.
DBMS_SQL.CLOSE_CURSOR ( c IN OUT INTEGER);
pragma restrict_references(close_cursor,RNDS,WNDS);
This 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;
pragma restrict_references(last_error_position,RNDS,WNDS);
Call this function after a PARSE
call, before any other DBMS_SQL
procedures or functions are called.
This function returns the cumulative count of the number of rows fetched.
DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;
pragma restrict_references(last_row_count,RNDS,WNDS);
Call this function after a FETCH_ROWS
or an EXECUTE_AND_FETCH
call. If called after an EXECUTE
call, then the value returned is zero.
This function returns the ROWID
of the last row processed.
DBMS_SQL.LAST_ROW_ID RETURN ROWID;
pragma restrict_references(last_row_id,RNDS,WNDS);
Call this function after a FETCH_ROWS
or an EXECUTE_AND_FETCH
call.
This function returns the SQL function code for the statement. These codes are listed in the Oracle Call Interface Programmer's Guide.
DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;
pragma restrict_references(last_sql_function_code,RNDS,WNDS);
You should call this function immediately after the SQL statement is run; otherwise, the return value is undefined.
|
Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|