DESCRIBE_COLUMNS2 procedure - Retrieve a description of column names in a SELECT list

The DESCRIBE_COLUMNS2 procedure provides the capability to retrieve a description of the columns in a SELECT list from a cursor.

Syntax

Parameters

c: An input argument of type INTEGER that specifies the cursor ID of the cursor whose columns are to be described.
col_cnt: An output argument of type INTEGER that returns the number of columns in the SELECT list of the cursor.
desc_tab: An output argument of type DESC_TAB2 that describes the column metadata. The DESC_TAB2 array provides information on each column in the specified cursor

Authorization

EXECUTE privilege on the DBMS_SQL module.

Usage notes

This procedure requires a user temporary table space with a page size of 4K; otherwise it returns an SQL0286N error. You can create the user temporary table space with this command:

CREATE USER TEMPORARY TABLESPACE DBMS_SQL_TEMP_TBS

DESC_TAB2 is an array of DESC_REC2 records of column information:

Table 1. DESC_TAB2 definition through DESC_REC2 records
Record name	Description
col_type	SQL data type as defined in Supported SQL data types in C and C++ embedded SQL applications.
col_max_len	Maximum length of the column.
col_name	Column name.
col_name_len	Length of the column name.
col_schema	Always NULL.
col_schema_name_len	Always NULL.
col_precision	Precision of the column as defined in the database. If col_type denotes a graphic or DBCLOB SQL data type, then this variable indicates the maximum number of double-byte characters the column can hold.
col_scale	Scale of the column as defined in the database (only applies to DECIMAL, NUMERIC, TIMESTAMP).
col_charsetid	Always NULL.
col_charsetform	Always NULL.
col_null_ok	Nullable indicator. This has a value of 1 if the column is nullable, otherwise, 0.

ALTER MODULE SYSIBMADM.DBMS_SQL PUBLISH TYPE DESC_REC2 AS ROW
(
col_type INTEGER,
col_max_len INTEGER,
col_name VARCHAR(128),
col_name_len INTEGER,
col_schema_name VARCHAR(128),
col_schema_name_len INTEGER,
col_precision INTEGER,
col_scale INTEGER,
col_charsetid INTEGER,
col_charsetform INTEGER,
col_null_ok INTEGER
);

ALTER MODULE SYSIBMADM.DBMS_SQL PUBLISH TYPE DESC_TAB2 AS DESC_REC2 ARRAY[INTEGER];