identify the columns and data types in a Weakly Typed REF CURSOR
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
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.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved