DBMS_SQL.DESCRIBE_COLUMNS tips

The DESCRIBE_COLUMNS API describes the columns of the SELECT statement which is opened and parsed through a cursor. This procedure consists of one input parameter accepting the cursor ID which is active in the session and two output parameters producing the number of columns in the statement parsed by the cursor and their description respectively.

The syntax for this procedure is shown below,

PROCEDURE describe_columns(c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT desc_tab);

The third parameter has a nested table DESC_TAB as its data type which is created from the index by table DESC_REC which has its structure as below,

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 above index by table structure has 11 columns which holds all possible information about the columns parsed through the cursor.

In the below listing, the EMPLOYEES table is parsed and then described using the DESCRIBE_COLUMNS procedure. All the necessary information corresponding to a column is retrieved from the nested table output parameter in the DECRIBE_COLUMNS procedure and is inserted into a test table which holds the column ID in its first column and the column description in its second column. The insertion is looped and the loop lives the range between 1 and the column count value returned by the DESCRIBE_COLUMNS procedure.

1.  SET SERVEROUTPUT ON 200000;

2.  DECLARE

3.  l_ntt_desc_tab dbms_sql.desc_tab;

4.  l_i_cursor_id INTEGER;

5.  l_i_col_cnt   INTEGER;

6.  l_vc_query    VARCHAR2(100):='select e.rowid,e.* from Employees e';

7.  BEGIN

8.  l_i_cursor_id:=dbms_sql.open_cursor;

9.  dbms_sql.parse(l_i_cursor_id, l_vc_query, dbms_sql.native);

10. dbms_sql.describe_columns(l_i_cursor_id, l_i_col_cnt, l_ntt_desc_tab);

11. FOR loop_dc IN 1..l_i_col_cnt

12. LOOP

13. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_name:'||decode(l_ntt_desc_tab(loop_dc).col_name, Null, 'Null', l_ntt_desc_tab(loop_dc).col_name));

14. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_type:'||decode(l_ntt_desc_tab(loop_dc).col_type, Null, 'Null', l_ntt_desc_tab(loop_dc).col_type));

15. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_precision:'||decode(l_ntt_desc_tab(loop_dc).col_precision, Null, 'Null', l_ntt_desc_tab(loop_dc).col_precision));

16. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_max_len:'||decode(l_ntt_desc_tab(loop_dc).col_max_len, Null, 'Null', l_ntt_desc_tab(loop_dc).col_max_len));

17. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_name_len:'||decode(l_ntt_desc_tab(loop_dc).col_name_len, Null, 'Null', l_ntt_desc_tab(loop_dc).col_name_len));

18. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_schema_name:'||decode(l_ntt_desc_tab(loop_dc).col_schema_name, Null, 'Null', l_ntt_desc_tab(loop_dc).col_schema_name));

19. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_schema_name_len:'||decode(l_ntt_desc_tab(loop_dc).col_schema_name_len, Null, 'Null', l_ntt_desc_tab(loop_dc).col_schema_name_len));

20. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_scale:'||decode(l_ntt_desc_tab(loop_dc).col_scale, Null, 'Null', l_ntt_desc_tab(loop_dc).col_scale));

21. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_charsetid:'||decode(l_ntt_desc_tab(loop_dc).col_charsetid, Null, 'Null', l_ntt_desc_tab(loop_dc).col_charsetid));

22. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'col_charsetform:'||decode(l_ntt_desc_tab(loop_dc).col_charsetform, Null, 'Null', l_ntt_desc_tab(loop_dc).col_charsetform));

23. IF l_ntt_desc_tab(loop_dc).col_null_ok THEN

24. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'Nullable:Y'); 

25. ELSE

26. INSERT INTO desc_col_test (col_id, col_desc) VALUES (loop_dc, 'Nullable:N');

27. END IF;

28. COMMIT;

29. END LOOP loop_dc;

30. END;

31. /

Script Explanation

Line No.	Description
1	This environment variable opens up an output buffer of size limit of 200000.
2	Start of the declare section of the block.
3	A local variable l_ntt_desc_tab is declared with dbms_sql.desc_tab nested table data type.
4	A local variable l_i_cursor_id is declared with INTEGER data type.
5	A local variable l_i_col_cnt is declared with INTEGER data type.
6	A local variable l_vc_query is declared with VARCHAR2 data type with a precision of 100 characters is defaulted to a dynamic SELECT query which selects the pseudo column and all other columns from the EMPLOYEES table.
7	Start of the execution section of the block.
8	The function DBMS_SQL.OPEN_CURSOR is executed and its return value is assigned to the local variable l_i_cursor_id.
9	The procedure DBMS_SQL.PARSE is executed with its first parameter as the local variable l_i_cursor_id, the second parameter as the local variable l_vc_query and the third parameter as the function DBMS_SQL.NATIVE.
10	The procedure DBMS_SQL.DESCRIBE_COLUMNS is executed with its input parameter as the local variable l_i_cursor_id and its first output parameter as the local variable l_i_col_cnt and the second output parameter as the local variable l_ntt_desc_tab.
11,12	Start of the FOR loop with its range between 1 and l_i_col_cnt.
13-27	An insert into the intermediate table DESC_COL_TEST with its first column value as the loop index value and its second column value as the different associative array parameters in the DESC_TAB nested table for each loop index value.
28	The INSERTs performed in the above step is committed.
29	End of the FOR loop.
30,31	End of the execution section of the loop.

      

After the necessary column information is inserted into the intermediate table, the data can be converted into a much more readable format by using the below SQL statement,

SELECT *

FROM

  (SELECT col_id,

    regexp_substr(col_desc,'[^:]+',1,1) col_desc_name,

    regexp_substr(col_desc,'[^:]+',1,2) col_desc_value

  FROM desc_col_test

  ) pivot (MAX(col_desc_value) FOR col_desc_name IN ('col_name' AS COL_NAME, 'col_type' AS COL_TYPE, 'col_precision' AS COL_PRECISION,

  'col_max_len' AS COL_MAX_LEN, 'col_name_len' AS COL_NAME_LEN,

  'col_schema_name' AS COL_SCHEMA_NAME, 'col_schema_name_len' AS COL_SCHEMA_NAME_LEN,

  'col_scale' AS COL_SCALE, 'col_charsetid' AS COL_CHARSETID,

  'col_charsetform' AS COL_CHARSETFORM, 'Nullable' AS NULLABLE))

ORDER BY col_id;

Script Result:

COL_ID	COL_NAME	COL_TYPE	COL_PRECISION	COL_MAX_LEN	COL_NAME_LEN	COL_SCHEMA_NAME	COL_SCHEMA_NAME_LEN	COL_SCALE	COL_CHARSETID	COL_CHARSETFORM	NULLABLE
1	ROWID	11	0	16	5	Null	0	0	0	0	N
2	EMPLOYEE_ID	2	6	22	11	Null	0	0	0	0	Y
3	FIRST_NAME	1	0	20	10	Null	0	0	178	1	Y
4	LAST_NAME	1	0	25	9	Null	0	0	178	1	N
5	EMAIL	1	0	25	5	Null	0	0	178	1	N
6	PHONE_NUMBER	1	0	20	12	Null	0	0	178	1	Y
7	HIRE_DATE	12	0	7	9	Null	0	0	0	0	N
8	JOB_ID	1	0	10	6	Null	0	0	178	1	N
9	SALARY	2	8	22	6	Null	0	2	0	0	Y
10	COMMISSION_PCT	2	2	22	14	Null	0	2	0	0	Y
11	MANAGER_ID	2	6	22	10	Null	0	0	0	0	Y
12	DEPARTMENT_ID	2	4	22	13	Null	0	0	0	0	Y

The above table data show the detailed information on the columns which are parsed through the cursor in a much readable format.

When a dynamic statement with its column more than 32 in length is used in the above block like the below query,

SELECT to_timestamp_tz('30-Sep-1989 04:32:00 PM +05:30','DD-MON-RR HH:MI:SS AM TZH:TZM') at TIME ZONE 'UTC' FROM dual;

The block throws a run time error as shown below,

Error report:

ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow, col_name_len=90. Use describe_columns2

ORA-06512: at "SYS.DBMS_SQL", line 2070

ORA-06512: at line 9

06502. 00000 -  "PL/SQL: numeric or value error%s"

*Cause:   

*Action:

The error states that the column name has exceeded its allowed length and it suggests us to use the DESCRIBE_COLUMNS2 API instead.

Thank you,

Boobal Ganesan