1 2 Previous Next 18 Replies Latest reply: Jan 27, 2010 3:37 AM by 650063 RSS

    How to find-out Columns & Their Data Types in Ref-Cursor

    585157
      Hi,

      Is there any way to find out, that..how many coulmns will be returned by the Ref Cursor and what will be the DataType / Column Names of those columns which will be returned as resultset?

      orapdev
        • 1. Re: How to find-out Columns & Their Data Types in Ref-Cursor
          JustinCave
          If you don't know that information at compile time, that implies that you're writing dynamic SQL. If you are writing dynamic SQL, you can use the DBMS_SQL information to get this sort of information, but it does tend to complicate your code.

          Justin
          • 2. Re: How to find-out Columns & Their Data Types in Ref-Cursor
            585157
            Actually, What I mean to say and What I want to do is that:

            I will just give an argument of "procedure_name" to the procedure which will do some dynamic work on given argument and will return a result like that.
            Procedure Name     : Employee_Proc
            Ref-Coursor Found  : Yes
            Ref-Cursor Name    : Resultset
            Ref-Cursor Columns : 3
            Ref-Cursor OutPut  :

              COULMN    TYPE
              --------  -------------
              Emp_Code  NUMBER
              Emp_Name  VARCHAR2(100)
              HireDate  Date
            In brief, I want to Describe a Ref-Cursor.

            Please reply with Code Example.

            I shall be very thankful to you.

            Thanks in Advance.
            orapdev

            Message was edited by:
            orapdev
            • 3. Re: How to find-out Columns & Their Data Types in Ref-Cursor
              121256
              I want to Describe a Ref-Cursor.
              Currently it's not supported on the server side. Only on the client side (via OCI).
              Using DBNS_XMLGEN you can get only column names.
              • 4. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                585157
                :(

                very bad for me.

                basically, I am doing some documentation work on already created procedures.
                and its too difficult for me to open the proc and see whats going on and what type of data will be the resultset.

                any way...

                Thanks. :(
                • 5. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                  MichaelS
                  I would dig into Java Stored Procedures: It looks rather straightforward to pass a refcursor and use the OracleResultSetMetaData Interface to return cursor metadata to PLSQL again.
                  But no time to dig deeper into this myself yet ;)
                  • 6. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                    JustinCave
                    Are the procedures returning a strongly typed ref cursor? Or a weakly typed ref cursor?

                    If you're returning a strongly typed REF CURSOR, you can get the columns in the result set
                    SCOTT @ jcave102 Local> CREATE OR REPLACE PACKAGE refcursor_demo
                      2  AS
                      3    TYPE emp_refcursor_strong IS REF CURSOR RETURN emp%rowtype;
                      4    TYPE emp_refcursor_weak   IS REF CURSOR;
                      5   
                      6    --
                      7    -- You can define either procedures that have the strongly typed
                      8    -- REF CURSOR as an OUT parameter or you can define a function that
                      9    -- returns the REF CURSOR.
                    10    --
                    11    PROCEDURE refCursorProc( employees OUT emp_refcursor_strong );
                    12    FUNCTION  refCursorFunc RETURN emp_refcursor_weak;
                    13  END;
                    14  /

                    Package created.

                    Elapsed: 00:00:00.03
                    SCOTT @ jcave102 Local> desc refcursor_demo
                    FUNCTION REFCURSORFUNC RETURNS REF CURSOR
                    PROCEDURE REFCURSORPROC
                    Argument Name                  Type                    In/Out Default?
                    ------------------------------ ----------------------- ------ --------
                    EMPLOYEES                      REF CURSOR              OUT
                                                    RECORD                  OUT
                         EMPNO                      NUMBER(4)               OUT
                         ENAME                      VARCHAR2(10)            OUT
                         JOB                        VARCHAR2(9)             OUT
                         MGR                        NUMBER(4)               OUT
                         HIREDATE                   DATE                    OUT
                         SAL                        NUMBER(7,2)             OUT
                         COMM                       NUMBER(7,2)             OUT
                         DEPTNO                     NUMBER(2)               OUT
                    If you've got a weakly typed ref cursor, you'd have to write a small app to call the procedure and describe the result.

                    Justin
                    • 7. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                      585157
                      We are using SYS_REFCURSOR (Weak).
                      If you've got a weakly typed ref cursor, you'd have to write a small app to call the procedure and describe the result.
                      Please give some example, I am not getting / not understanding, how can i write a small app to do the same ?
                      • 8. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                        94799
                        I would dig into Java Stored Procedures
                        I'm looking into this with one of our Java developers. If we end up with a suitable example I'll post it.

                        It seems that in 11g there will be an option to cast the ref cursor to a DBMS_SQL cursor and use the DBMS_SQL describe interface to do this in PL/SQL only.
                        • 9. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                          MichaelS
                          Here's what I came up with:
                          michaels>  create or replace and resolve java source named "DescRefCursor" 
                          as
                           import java.sql.Connection;
                           import java.sql.DriverManager;
                           import java.sql.Statement;
                           import java.sql.SQLException; 
                           import java.sql.ResultSet;
                           import java.sql.ResultSetMetaData;
                           import oracle.jdbc.driver.OracleDriver;
                           import oracle.jdbc.driver.OracleConnection; 
                          
                           public class DescRefCursor
                           {
                             public static ResultSet DescCursor(ResultSet rset)
                             {   
                               ResultSet outrset = null;     
                               try
                               {
                                  DriverManager.registerDriver(new OracleDriver());
                                  Connection conn = new OracleDriver().defaultConnection();
                                  ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
                               
                                  Statement stmt = conn.createStatement();
                               
                                  ResultSetMetaData rsetMetaData = rset.getMetaData();
                                           
                                  StringBuffer columnInfo = new StringBuffer();
                                  
                                  for( int i=0; i < rsetMetaData.getColumnCount(); i++ )
                                  {
                                     columnInfo.append("select '");
                                     columnInfo.append(rsetMetaData.getColumnName( i + 1 ));
                                     columnInfo.append("' columns, '");
                                     columnInfo.append(rsetMetaData.getColumnTypeName ( i + 1));
                                     columnInfo.append("' type, '");
                                     columnInfo.append(rsetMetaData.getPrecision ( i + 1));
                                     columnInfo.append("' precision, '");  
                                     columnInfo.append(rsetMetaData.isNullable ( i + 1));
                                     columnInfo.append("' nullable ");
                                                                    
                                     if (i == rsetMetaData.getColumnCount() - 1) {         
                                       columnInfo.append(" from dual");
                                     } else {          
                                       columnInfo.append(" from dual union all ");
                                     }
                                  }
                                  // System.out.println( columnInfo.toString() );        
                                  outrset = stmt.executeQuery(columnInfo.toString());        
                                }
                                catch (SQLException e)
                                {
                                  e.printStackTrace();
                                }      
                                catch (Exception e)
                                {
                                  e.printStackTrace(); 
                                }
                                finally
                                {
                                  return outrset;
                                }
                             }
                           }
                          /
                          Java created.
                          
                          michaels>  create or replace function descrefcursor (cur sys_refcursor)
                             return sys_refcursor
                          as
                             language java
                             name 'DescRefCursor.DescCursor(java.sql.ResultSet) return java.sql.ResultSet';
                          /
                          Function created.
                          
                          michaels>  column columns format a20
                          michaels>  column type format a15
                          michaels>  column precision format a10
                          michaels>  column nullable format a10
                          
                          michaels>  select extractvalue(t.column_value,'ROW/COLUMNS/text()') columns,
                                 extractvalue(t.column_value,'ROW/TYPE/text()') type,
                                 extractvalue(t.column_value,'ROW/PRECISION/text()') precision,
                                 extractvalue(t.column_value,'ROW/NULLABLE/text()') nullable 
                          from table(xmlsequence(descrefcursor(cursor(select * from emp) /* cursor to be described */))) t
                          
                          COLUMNS              TYPE            PRECISION  NULLABLE  
                          -------------------- --------------- ---------- ----------
                          EMPNO                NUMBER          4          0         
                          ENAME                VARCHAR2        10         1         
                          JOB                  VARCHAR2        9          1         
                          MGR                  NUMBER          4          1         
                          HIREDATE             DATE            0          0         
                          SAL                  NUMBER          7          1         
                          COMM                 NUMBER          7          1         
                          DEPTNO               NUMBER          2          1         
                          
                          
                          8 rows selected.
                          • 10. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                            585157
                            Great Work michaels.
                            I am Realy Thankful to you.
                            • 11. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                              585157
                              Please help.

                              I have more then 250 procedures like that:
                              create procedure was_qry_proc(deptno in number, resultset out sys_refcursor)
                              as
                              begin
                              open resultset for 'select * from emp where deptno = '||deptno;
                              end;
                              and I want to write a document for every procedure.

                              If I use your given example then... for every procedure I have to open the proc and then copy the query from procedure and then finally I will use your given code with that query.

                              I will have to open every procedure and then will have to copy the query and then some manupulation can be required in a query because it is in a variable and then can run your SQL code.

                              is there any possiblity to automize the whole process using your great work.

                              may be like that....
                              exec desc_proc_refcur(
                                                                 'scott.was_qry_proc' , --IN
                                                                :refcur_var -- out          --OUT  RefCur
                                                                ); 
                              just giving procedure name as input and get the result as output.

                              Please help.

                              Thanks Again.

                              Message was edited by:
                              orapdev
                              • 12. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                                94799
                                Here's what I came up with
                                Very nice michaels, thanks for that ;-)
                                • 13. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                                  537882
                                  If they really are sys_refcursors you'll have to open the procedures/functions up anyway sinc e they might...
                                  CREATE OR REPLACE FUNCTION be_like_this
                                     RETURN sys_refcursor
                                  IS
                                     rc   sys_refcursor;
                                  BEGIN
                                     CASE TO_CHAR (SYSDATE, 'Day')
                                        WHEN 'Monday'
                                        THEN
                                           OPEN rc FOR 'SELECT 1 number from dual';
                                        WHEN 'Tuesday'
                                        THEN
                                           OPEN rc FOR 'select * from user_tables';
                                        ELSE
                                           OPEN rc FOR 'select sysdate from dual';
                                     END CASE;

                                     RETURN rc;
                                  END;
                                  This is the point of weak-typed result set cursors I would've thought.

                                  Also why this...
                                  michaels>  select extractvalue(t.column_value,'ROW/COLUMNS/text()') columns,
                                         extractvalue(t.column_value,'ROW/TYPE/text()') type,
                                         extractvalue(t.column_value,'ROW/PRECISION/text()') precision,
                                         extractvalue(t.column_value,'ROW/NULLABLE/text()') nullable
                                  from table(xmlsequence(descrefcursor(cursor(select * from emp) /* cursor to be described */))) t
                                  When you could just...
                                  select descrefcursor(cursor(select * from emp)) from dual
                                  ?
                                  • 14. Re: How to find-out Columns & Their Data Types in Ref-Cursor
                                    585157
                                    Nice & Excellent thought "Flying Spontinalli"...

                                    But there is no such type of procedures as you posted.

                                    for every table there are two procedures, one is created with the name of table + suffix "_QRY" (i.e table_name_qry) to Query the table using sys_refcursors and second is created with the name of table + suffix "_SET" (i.e table_name_set) to perform DML on the table.

                                    What I have to do is to describe the parameters of all the procedure, but in all QRY procedures there is a Ref-Cursor which I also have to describe.

                                    is there any way to create a procedure based on :
                                    SELECT descrefcursor(cursor(select * from emp))
                                      FROM dual;
                                    to pass a procedure name to it and get the resultset.

                                    OR

                                    is there any other way.

                                    Please help.
                                    1 2 Previous Next