Oracle-L: How to retrieve DBMS_DESCRIBE info in OUT ref

From: <Rick_Cale_at_teamhealth.com>
Date: Fri, 22 Mar 2002 07:38:22 -0800
Message-ID: <F001.00430DF0.20020322073822@fatcity.com>

Hi All,

Not exactly a DBA question but more of a developer question but any help is appreciated including how to subscribe to Oracle developer forum.

What I need is for the DBMS_DESCRIBE output in a single record set in the form of a REF CURSOR that can be
returned from a stored procedure as an output parameter. I have a package with a variable of type REF CURSOR
that can be pulled and pushed in and out of a stored procedure. The procedure SP_FETCH_CLINICIAN below describes opening a REF CURSOR (which is the output parameter). A similar REF CURSOR output parameter is what I need returned from a proc that would call DBMS_DESCRIBE. The DBMS_DESCRIBE procedure returns a bunch of NUMBER_TABLE
and VARCHAR2_TABLE datatypes that are described in the package header as "TABLE OF VARCHAR2" or "TABLE OF NUMBER" types. I need to somehow iterate through those retuned values from the DBMS_DESCRIBE procedure and build something that I can return as a REF CURSOR as an output parameter in a custom stored procedure. The SP_WSDESCRIBE_PROC below what
I am currently returning (just a VARCHAR2), but It would be great if I could return the same type of data in a REF CURSOR output parameter that would represent a cursor built from a select statement.

For your viewing pleasure are 2 procdures mentioned above.

Thanks
Rick

PROCEDURE SP_FETCH_CLINICIAN

    ( p_cursor OUT CLINCUR,
      i_CLINICIAN_ID IN NUMBER,
      o_RETVAL OUT NUMBER

    )
    IS
   BEGIN
       O_RETVAL:= 0;

       OPEN p_cursor FOR
       SELECT CLINICIAN_ID, SITE_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME,
              SUFFIX, DEGREE, NT_LOGON_ID, LICENSE_NO, DEA_NO, HIS_ID
         FROM EMR.CLINICIAN_LOOKUP
        WHERE CLINICIAN_ID = i_CLINICIAN_ID;

   EXCEPTION
      WHEN OTHERS THEN
          o_RETVAL := SQLCODE;

END; PROCEDURE SP_WSDESCRIBE_PROC
(

 i_PROCNAME IN VARCHAR,
 i_OUTTYPE IN VARCHAR,
 o_OUTBUFF OUT VARCHAR

)
AS
overload SYS.DBMS_DESCRIBE.NUMBER_TABLE; position SYS.DBMS_DESCRIBE.NUMBER_TABLE; t_level SYS.DBMS_DESCRIBE.NUMBER_TABLE;
argument_name SYS.DBMS_DESCRIBE.VARCHAR2_TABLE; datatype SYS.DBMS_DESCRIBE.NUMBER_TABLE; default_value SYS.DBMS_DESCRIBE.NUMBER_TABLE; in_out SYS.DBMS_DESCRIBE.NUMBER_TABLE;
t_length SYS.DBMS_DESCRIBE.NUMBER_TABLE; t_precision SYS.DBMS_DESCRIBE.NUMBER_TABLE;

scale SYS.DBMS_DESCRIBE.NUMBER_TABLE;
radix SYS.DBMS_DESCRIBE.NUMBER_TABLE;
spare SYS.DBMS_DESCRIBE.NUMBER_TABLE;

s_PARMBUFF VARCHAR2(200);
BEGIN
sys.dbms_describe.describe_procedure(i_PROCNAME,'','',

overload,position,t_level,argument_name,

datatype,default_value,in_out,t_length,

                                       t_precision,scale,radix,spare);

IF overload IS NOT NULL THEN
IF i_OUTTYPE = 'XML' THEN

o_OUTBUFF := '<?xml version="1.0" encoding="utf-8"
?><storedprocedures><storedprocedure name="' || i_PROCNAME ||
'"><parameters>';

       FOR i IN overload.FIRST..overload.LAST LOOP
            select '<parameter name="' || argument_name(i) || '" size="' ||
t_length(i) || '" precision="' || t_precision(i) || '" ' ||
                   'scale="' || scale(i) || '" datatype="' ||

DECODE(datatype(i), 0, 'NULL', 1, 'VARCHAR2', 2, 'NUMBER', 3, 'BINARY_INTEGER',

                          8, 'LONG',     10, 'ROWID', 12, 'DATE', 23,
'RAW',    24, 'LONG_RAW',
                               96, 'CHAR', 102, 'REF_CURSOR', 106,
'MLSLABEL', 250, 'RECORD', 251, 'TABLE',
                                          252, 'BOOLEAN') || '" direction
="' ||
                 DECODE(in_out(i), 0, 'IN', 1, 'OUT', 2, 'IN_OUT') || '"
/>' into s_PARMBUFF from dual;
             select o_OUTBUFF || s_PARMBUFF into o_OUTBUFF from dual;
       END LOOP;

END IF;
IF i_OUTTYPE = 'CSV' THEN

       FOR i IN overload.FIRST..overload.LAST LOOP
       select argument_name(i) || ', ' || t_length(i) || ', ' ||
t_precision(i) || ', ' ||
              scale(i) || ', ' || DECODE(datatype(i), 0, 'NULL', 1,
'VARCHAR2', 2, 'NUMBER', 3, 'BINARY_INTEGER',
           8, 'LONG',    10, 'ROWID', 12, 'DATE', 23, 'RAW',      24,
'LONG_RAW',
           96, 'CHAR', 102, 'REF_CURSOR', 106, 'MLSLABEL', 250, 'RECORD',
251, 'TABLE',
                     252, 'BOOLEAN') || ', ' ||
                               DECODE(in_out(i), 0, 'IN', 1, 'OUT', 2,
'IN_OUT') into s_PARMBUFF from dual;
        IF i < overload.count THEN
             select o_OUTBUFF || RTRIM(s_PARMBUFF) || '~' into o_OUTBUFF
from dual;
        ELSE
             select o_OUTBUFF || RTRIM(s_PARMBUFF) into o_OUTBUFF from
dual;
        END IF;

END LOOP;
END IF;
END IF;
END;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Mar 22 2002 - 09:38:22 CST

How to retrieve DBMS_DESCRIBE info in OUT ref_cursor.