Oracle7 Server Application Developer's Guide

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
OPEN_CURSOR Return cursor ID number of new cursor.
PARSE Parse given statement.
BIND_VARIABLE Bind a given value to a given variable.
DEFINE_COLUMN Define a column to be selected from the given cursor, used only with SELECT statements.
DEFINE_COLUMN_LONG Define a LONG column to be selected from the given cursor, used only with SELECT statements.
EXECUTE Execute a given cursor.
EXECUTE_AND_FETCH Execute a given cursor and fetch rows.
FETCH_ROWS Fetch a row from a given cursor.
COLUMN_VALUE Returns value of the cursor element for a given position in a cursor.
COLUMN_VALUE_LONG Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG.
VARIABLE_VALUE Returns value of named variable for given cursor.
IS_OPEN Returns TRUE if given cursor is open.
CLOSE_CURSOR Closes given cursor and frees memory.
LAST_ERROR_POSITION Returns byte offset in the SQL statement text where the error occurred.
LAST_ROW_COUNT Returns cumulative count of the number of rows fetched.
LAST_ROW_ID Returns ROWID of last row processed.
LAST_SQL_ FUNCTION_CODE Returns SQL function code for statement.

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);

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:

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

Function/Procedure	Description	Refer to
`OPEN_CURSOR`	Return cursor ID number of new cursor.
`PARSE`	Parse given statement.
`BIND_VARIABLE`	Bind a given value to a given variable.
`DEFINE_COLUMN`	Define a column to be selected from the given cursor, used only with SELECT statements.
`DEFINE_COLUMN_LONG`	Define a LONG column to be selected from the given cursor, used only with SELECT statements.
`EXECUTE`	Execute a given cursor.
`EXECUTE_AND_FETCH`	Execute a given cursor and fetch rows.
`FETCH_ROWS`	Fetch a row from a given cursor.
`COLUMN_VALUE`	Returns value of the cursor element for a given position in a cursor.
`COLUMN_VALUE_LONG`	Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG.
`VARIABLE_VALUE`	Returns value of named variable for given cursor.
`IS_OPEN`	Returns TRUE if given cursor is open.
`CLOSE_CURSOR`	Closes given cursor and frees memory.
`LAST_ERROR_POSITION`	Returns byte offset in the SQL statement text where the error occurred.
`LAST_ROW_COUNT`	Returns cumulative count of the number of rows fetched.
`LAST_ROW_ID`	Returns ROWID of last row processed.
`LAST_SQL_ FUNCTION_CODE`	Returns SQL function code for statement.

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.

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.

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.

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.

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.