Writes information about a prepared statement to the SQL
Descriptor Area (SQLDA).
The DESCRIBE statement is a dynamic SQL statement. Dynamic SQL
lets programs accept or generate SQL statements at run time,
in contrast to SQL statements that are part of the source code
for precompiled programs or SQL module language procedures.
Unlike precompiled SQL or SQL module language statements, such
dynamically executed SQL statements are not part of a program's
source code but are generated while the program runs. Dynamic SQL
is useful when you cannot predict the type of SQL statement your
program needs to process.
The SQLDA is a collection of host language variables used only
in dynamic SQL programs. To use the SQLDA, host languages must
support pointer variables that provide indirect access to storage
by storing the address of data instead of directly storing data
in the host language variable. The languages supported by the
SQL precompiler that also support pointer variables are Ada, C,
and PL/I. Any other language that supports pointer variables can
use the SQLDA, but must call SQL module procedures that contain
SQL statements instead of embedding the SQL statements directly
in source code. The SQLDA provides information about dynamic SQL
statements to the program and information about memory allocated
by the program to SQL.
The DESCRIBE statement is how SQL writes information that the
program uses to the SQLDA. Specifically, the DESCRIBE statement
stores in the SQLDA the number and data types of any select list
items or parameter markers in a prepared statement.
The Oracle Rdb SQL Reference Manual describes in more detail
the specific fields of the SQLDA and how programs use it to
communicate about select list items and parameter markers in
prepared statements.
1 – Environment
You can use the DESCRIBE statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
DESCRIBE -+-> <statement-name> ------------+----+
+-> <statement-id-parameter> ----+ |
+---------------------- <----------------------+
+--+----------------+-----> INTO <descriptor-name> -->
+-> SELECT LIST -+
+-> OUTPUT ------+
+-> MARKERS -----+
+-> INPUT -------+
3 – Arguments
3.1 – INPUT
Specifies that the DESCRIBE statement writes information about
input parameter markers to the SQLDA. The MARKERS or INPUT clause
specifies that the DESCRIBE statement writes information about
the number and data types of any input parameter markers in the
prepared statement to the SQLDA.
Input parameter markers in a prepared statement serve the same
purpose as host language variables in nondynamic, embedded SQL
statements. The program can use that information in the SQLDA
to allocate storage. The program must supply values in that
allocated storage. SQL substitutes these values for the parameter
markers when it dynamically executes the prepared statement.
3.2 – INTO descriptor name
Specifies the name of a structure declared in the host language
program as an SQLDA to which SQL writes information about select
list items, or input or output parameter markers.
Precompiled programs can use the embedded SQL statement INCLUDE
SQLDA to automatically insert a declaration of an SQLDA
structure, called SQLDA, in the program when it precompiles
the program. Programs that use the SQL module language must
explicitly declare an SQLDA. Either precompiled or SQL module
language programs can explicitly declare additional SQLDAs but
must declare them with unique names. For sample declarations of
SQLDA structures, see the Oracle Rdb SQL Reference Manual.
3.3 – MARKERS
Specifies that the DESCRIBE statement writes information about
input parameter markers to the SQLDA. The MARKERS or INPUT clause
specifies that the DESCRIBE statement writes information about
the number and data types of any input parameter markers in the
prepared statement to the SQLDA.
Input parameter markers in a prepared statement serve the same
purpose as host language variables in nondynamic, embedded SQL
statements. The program can use that information in the SQLDA
to allocate storage. The program must supply values in that
allocated storage. SQL substitutes these values for the parameter
markers when it dynamically executes the prepared statement.
3.4 – OUTPUT
Specifies that the DESCRIBE statement writes information about
returned values in a prepared statement to the SQLDA. If you
use this clause, the DESCRIBE statement writes information about
the number and data types of any returned values in the prepared
statement to the SQLDA. The program uses that information to
allocate storage for the returned values. The storage allocated
by the program then receives the returned values.
The following statements or clauses return values to the DESCRIBE
statement:
o Select list items in a SELECT statement
o The following statements within multistatement procedures:
- Singleton SELECT statement
- INSERT . . . RETURNING and UPDATE . . . RETURNING statements
- SET assignment statement
o CALL statement (invoking a stored procedure)
o Dynamic singleton SELECT statement
The default is SELECT LIST (or OUTPUT).
3.5 – SELECT_LIST
Specifies that the DESCRIBE statement writes information about
returned values in a prepared statement to the SQLDA. If you
use this clause, the DESCRIBE statement writes information about
the number and data types of any returned values in the prepared
statement to the SQLDA. The program uses that information to
allocate storage for the returned values. The storage allocated
by the program then receives the returned values.
The following statements or clauses return values to the DESCRIBE
statement:
o Select list items in a SELECT statement
o The following statements within multistatement procedures:
- Singleton SELECT statement
- INSERT . . . RETURNING and UPDATE . . . RETURNING statements
- SET assignment statement
o CALL statement (invoking a stored procedure)
o Dynamic singleton SELECT statement
The default is SELECT LIST (or OUTPUT).
3.6 – statement-name
Specifies the name of a prepared statement. If the PREPARE
statement for the dynamically executed statement specifies a
parameter, use the same parameter in the DESCRIBE statement
instead of an explicit statement name.
You can supply either a parameter or a compile-time statement
name. Specifying a parameter lets SQL supply identifiers to
programs at run time. Use an integer parameter to contain the
statement identifier returned by SQL or a character string
parameter to contain the name of the statement that you pass
to SQL. See the PREPARE statement and the DECLARE Dynamic_CURSOR
statement for more details.
3.7 – statement-id-parameter
The name of a prepared statement. If the PREPARE statement for
the dynamically executed statement specifies a parameter, use the
same parameter in the DESCRIBE statement instead of an explicit
statement name.
You can supply either a parameter or a compile-time statement
name. Specifying a parameter lets SQL supply identifiers to
programs at run time. Use an integer parameter to contain the
statement identifier returned by SQL or a character string
parameter to contain the name of the statement that you pass
to SQL. See the PREPARE statement and the DECLARE Dynamic_CURSOR
statement for more details.
4 – Examples
Example 1: Using the DESCRIBE . . . OUTPUT statement with a
prepared SELECT statement
This PL/I program illustrates using the DESCRIBE . . . OUTPUT
statement to write information to the SQLDA about the select
list items of a prepared SELECT statement. There are no parameter
markers in this particular prepared SELECT statement.
After issuing the DESCRIBE statement, the program stores in the
SQLDA the addresses of host language variables that will receive
values from columns of the result table during FETCH statements.
To shorten the example, this PL/I program is simplified:
o The program includes the SELECT statement to be dynamically
executed as part of the source code directly in the PREPARE
statement. A program with such coded SQL statements does
not need to use dynamic SQL at all, but can simply embed the
SELECT statement in a DECLARE CURSOR statement. (A program
that must process SQL statements generated as it executes is
the only type that requires dynamic SQL.)
o The program declares host language variables for select list
items without checking the SQLDA for a description of those
items. Typically, an application needs to look in the SQLDA
to determine the number and data type of select list items
generated by a prepared SELECT statement before allocating
storage.
o The program does not use the DESCRIBE . . . INPUT statement
to determine if there are any parameter markers in this
dynamically executed SELECT statement. In this example,
because the SELECT statement is coded in the program, it
is clear that there is no need for a DESCRIBE . . . INPUT
statement. However, if the SELECT statement is generated
at run time, the program may have to determine there if are
parameter markers by issuing a DESCRIBE . . . INPUT statement
and looking at the value of the SQLD field in the SQLDA.
CURSOR_EX : PROCEDURE OPTIONS (MAIN);
/*
* Illustrate the DESCRIBE...SELECT LIST statement using a
* dynamic SELECT statement:
*
* Use a dynamic SELECT statement as the basis for
* a cursor that displays a join of the EMPLOYEES
* and SALARY_HISTORY tables on the screen.
*/
declare sys$putmsg external entry
(any, any value, any value, any value);
/* Declare SQL Communications Area: */
EXEC SQL INCLUDE SQLCA;
/* Declare SQL Descriptor Area: */
EXEC SQL INCLUDE SQLDA;
/* Declare the alias: */
EXEC SQL DECLARE ALIAS FILENAME 'SQL$DATABASE';
/*
* Branch to ERR_HANDLER if the SQLCODE field
* of the SQLCA is greater than 0:
*/
EXEC SQL WHENEVER SQLERROR GOTO ERR_HANDLER;
/*
* Declare a cursor named EMP that uses the
* prepared statement DYN_SELECT:
*/
EXEC SQL DECLARE EMP CURSOR FOR DYN_SELECT;
/* Declare a host structure to receive
* the results of FETCH statements:
*/
DCL 1 P_REC,
2 EMPLOYEE_ID CHAR(5),
2 FIRST_NAME CHAR(10),
2 LAST_NAME CHAR(14),
2 SALARY_AMOUNT FIXED BINARY(31);
/* Allocate memory for the SQLDA and
* set the value of its SQLN field:
*/
SQLSIZE = 10;
ALLOCATE SQLDA SET (SQLDAPTR);
SQLN = 10;
/* Prepare the SELECT statement
* for dynamic execution directly
* from a statement string:
*/
EXEC SQL PREPARE DYN_SELECT FROM
'SELECT E.EMPLOYEE_ID,
E.FIRST_NAME,
E.LAST_NAME,
S.SALARY_AMOUNT
FROM EMPLOYEES E, SALARY_HISTORY S
WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID AND
S.SALARY_END IS NULL';
/* Write information about the
* columns of the result table
* of DYN_SELECT into the SQLDA:
*/
EXEC SQL DESCRIBE DYN_SELECT OUTPUT INTO SQLDA;
/*
* Assign the addresses of the host language
* variables that will receive the values of the
* fetched row to the SQLDATA field
* of the SQLDA:
*/
SQLDATA(1) = ADDR( EMPLOYEE_ID );
SQLDATA(2) = ADDR( FIRST_NAME);
SQLDATA(3) = ADDR( LAST_NAME );
SQLDATA(4) = ADDR( SALARY_AMOUNT);
/* Open the cursor: */
EXEC SQL OPEN EMP;
/* Fetch the first row of the result table.
* SQL uses the addresses in the SQLDA
* to store values from the table into
* the host language variables.
*/
EXEC SQL FETCH EMP USING DESCRIPTOR SQLDA;
PUT EDIT ('Current Salaries of Employees: ') (SKIP, A, SKIP(2));
/* While the SQLCODE field of the
* SQLCA is not 100 (NOT_FOUND error):
*/
DO WHILE (SQLCA.SQLCODE = 0);
/* Display the values from the host language variables: */
PUT SKIP EDIT
(EMPLOYEE_ID, ' ', FIRST_NAME, ' ', LAST_NAME, ' ',
SALARY_AMOUNT)
(A, A, A, A, A, A, F(9));
/* Fetch another row of the result table: */
EXEC SQL FETCH EMP USING DESCRIPTOR SQLDA;
END;
/* Close the cursor: */
EXEC SQL CLOSE EMP;
RETURN;
ERR_HANDLER:
PUT EDIT
('Unexpected error, SQLCODE is: ', SQLCA.SQLCODE) (skip, a, f(9));
CALL SYS$PUTMSG(RDB$MESSAGE_VECTOR, 0, 0, 0);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK;
RETURN;
END CURSOR_EX;
See also Example 2 in DECLARE Dynamic_CURSOR.