|
As Solution Using New
11g Functionality In DBMS_SQL |
I can't even count the
number of times I have found myself staring at the output of a
pipelined table function, or a weakly typed ref cursor, and wondered
about the column names, data types, and other attributes.
If you have faced this then you too can stop cursing the darkness.
Heres a few demonstrations of the pain. |
SELECT COUNT(*)
FROM dba_arguments
WHERE data_type = 'REF CURSOR';
SELECT DISTINCT package_name
FROM dba_arguments
WHERE data_type = 'REF CURSOR'
AND package_name LIKE 'DBMS%'
ORDER BY 1; |
| And that isn't counting all of the
code written by internal developers and developers from software
vendors. Lets start by building a demo table and loading it with a
few rows. |
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));
INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
SELECT * FROM employees; |
|
Then we will create a small stored
procedure that uses a weakly typed ref cursor to receive input from
an anonymous block. |
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor IN SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/ |
|
Lets test the anonymous block and see if
it all works. |
set serveroutput on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';
pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/ |
The block calls the pass_ref_cursor
procedure which uses dbms_output to show us that it read the
employee table.
Here we can see the source code and can identify the source and its
data type. But what if we couldn't?
The following code demonstrates the full capability of the
DBMS_SQL.DESC_TAB and takes advantage of the fact that with Database
11g we can convert ref cursors into DBMS_SQL API handles. The
private procedure "p" is used to print the results using DBMS_OUTPUT. |
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN
sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30)||to_char(desctab(i).col_type));
p(rpad('COL_MAX_LEN:',30)||to_char(desctab(i).col_max_len));
p(rpad('COL_NAME:',30)||desctab(i).col_name);
p(rpad('COL_NAME_LEN:',30)||to_char(desctab(i).col_name_len));
p(rpad('COL_SCHEMA_NAME:',30)||desctab(i).col_schema_name);
p(rpad('COL_SCHEMA_NAME_LEN:',30)||to_char(desctab(i).col_schema_name_len));
p(rpad('COL_PRECISION:',30)||to_char(desctab(i).col_precision));
p(rpad('COL_SCALE:',30)||to_char(desctab(i).col_scale));
p(rpad('COL_CHARSETID:',30)||to_char(desctab(i).col_charsetid));
p(rpad('COL_CHARSETFORM:',30)||to_char(desctab(i).col_charsetform));
IF desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'TRUE');
ELSIF NOT desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'FALSE');
ELSE
p(rpad('COL_NULL_OK:',30)||'NULL');
END IF;
END LOOP;
END print_desc_tab;
/ |
| Lets run an anonymous block that
calls the print_desc_tab and test it. |
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;
h := dbms_sql.to_cursor_number(refcur);
dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/ |
It works well. the DBMS_SQL's
DESCRIBE_COLUMNS API gives us access to the column data type in
integer form, its max length in bytes, the column's name, the length
of the column's name in bytes, the schema name that owns the
column's data type if the data type is user defined, the length, in
bytes, of the schema name, and the columns precision, scale,
character set identifier, character set form, and whether it is
nullable.
Lets try again but this time with a simplified version of
print_desc_tab
that only exposes two attributes: data type and column name. |
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN
sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
dtype VARCHAR2(30);
PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
SELECT DECODE(to_char(desctab(i).col_type), 1, 'VARCHAR2', 2,
'NUMBER', 12, 'DATE')
INTO dtype
FROM dual;
p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30) || dtype);
p(rpad('COL_NAME:',30) || desctab(i).col_name);
END LOOP;
END print_desc_tab;
/ |
| We will again run the same anonymous
block to call print_desc_tab |
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;
h := dbms_sql.to_cursor_number(refcur);
dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/ |
| Now you know how to do it.The only
thing left is to find a project where you can put it to good use. |
