A script-enabled browser is required for this page to function properly.

EXEC_SQL Package

Description

The EXEC_SQL package allows you to access multiple Oracle database servers on several different connections at the same time.

The EXEC_SQL package contains procedures and functions you can use to execute dynamic SQL within PL/SQL procedures. Like the DBMS_SQL package, the SQL statements are stored in character strings that are only passed to or built by your source program at runtime. You can issue any data manipulation language (DML) or data definition language (DDL) statement using the EXEC_SQL package.

The EXEC_SQL package differs from the DMBS_SQL package in the following ways:

For more information about the DBMS_SQL package, see your Oracle Application Developer's Guide.

EXEC_SQL predefined exceptions

Package Name Description
EXEC_SQL.Invalid_Connection An invalid connection handle is passed.
EXEC_SQL.Package_Error Any general error. Use EXEC_SQL. Last_Error_Code and EXEC_SQL.Last_Error_Mesg to retrieve the error.
EXEC_SQL.Invalid_Column_Number The EXEC_SQL.Describe_Column procedure encountered a column number that does not exist in the result set.
EXEC_SQL.Value_Error The EXEC_SQL.Column_Value encountered a value that is different from the original value retrieved by EXEC_SQL.Define_Column.

Retrieving result sets from queries or non-Oracle stored procedures

The EXEC_SQL package is particularly useful when you need to retrieve result sets from different Oracle or ODBC data sources into one form or report. To process a statement that returns a result set:

  1. For each column, use EXEC_SQL.Define_Column to specify the variable for receiving the value.
  2. Execute the statement by calling EXEC_SQL.Execute.
  3. Use EXEC_SQL.Fetch_Rows to retrieve a row in the result set.
  4. Use EXEC_SQL.Column_Value to obtain the value of each column retrieved by EXEC_SQL.Fetch_Rows.
  5. Repeat 3 and 4 until EXEC_SQL.Fetch_Rows returns 0.

Procedures and Functions

The functions and procedures are listed in the order they are usually called in a session.

EXEC_SQL.Open_Connection

EXEC_SQL.Curr_Connection

EXEC_SQL.Default_Connection

EXEC_SQL.Open_Cursor

EXEC_SQL.Describe_Column

EXEC_SQL.Bind_Variable

EXEC_SQL.Define_Column

EXEC_SQL.Execute

EXEC_SQL.Execute_And_Fetch

EXEC_SQL.Fetch_Rows

EXEC_SQL.More_Result_Sets

EXEC_SQL.Column_Value

EXEC_SQL.Variable_Value

EXEC_SQL.Is_Open

EXEC_SQL.Close_Cursor

EXEC_SQL.Is_Connected

EXEC_SQL.Is_OCA_Connection

EXEC_SQL.Close_Connection

The following functions retrieve information about the last referenced cursor in a connection after a SQL statement execution.

EXEC_SQL.Last_Error_Position

EXEC_SQL.Last_Row_Count

EXEC_SQL.Last_SQL_Function_Code

EXEC_SQL.Last_Error_Code

EXEC_SQL.Last_Error_Mesg