6 Replies Latest reply on Sep 1, 2008 7:53 PM by MichaelS

    Getting column names from SYS_REFCURSOR

    XYZ123
      Hi everybody, i have a SYS_REFCURSOR in a procedure. And i open it and fetch cursor to a record as follows:

      OPEN curgroup FOR vexpr2;
      LOOP
      FETCH curgroup INTO recType;
      ...
      ...
      END LOOP;

      recType is a Record, has two variables. Anyway, the question is can i get the column names from refcursor?

      like recType.COLUMN1 (Column1 is not a record variable ) ????

      or is there anything else to perform this operation?
        • 1. Re: Getting column names from SYS_REFCURSOR
          Billy~Verreynne
          Anyway, the question is can i get the column names from refcursor?
          No, not in 10g. You need to use a DBMS_SQL cursor instead.

          Yes, in 11g you can convert that ref cursor into a DBMS_SQL cursor.

          Details are in the PL/SQL Packages and Types Reference guide for 10g and 11g - document portal for all Oracle version manuals are at http://tahiti.oracle.com
          • 2. Re: Getting column names from SYS_REFCURSOR
            Karthick2003
            DBMS_SQL Can help. Below is a small example to get the column name and values from a select statement.
            SQL> declare
              2      l_theCursor     integer default dbms_sql.open_cursor;
              3      l_columnValue   varchar2(2000);
              4      l_columnOutput  varchar2(4000);
              5      l_status        integer;
              6      l_colCnt        number default 0;
              7      l_separator     varchar2(10) default '~';
              8      l_cnt           number default 0;
              9      l_colDesc          dbms_sql.DESC_TAB;
             10  begin
             11      dbms_sql.parse(  l_theCursor,  'SELECT * FROM EMP', dbms_sql.native );
             12
             13      for i in 1 .. 255 loop
             14          begin
             15              dbms_sql.define_column( l_theCursor, i,
             16                                      l_columnValue, 2000 );
             17              l_colCnt := i;
             18          exception
             19              when others then
             20                  if ( sqlcode = -1007 ) then exit;
             21                  else
             22                      raise;
             23                  end if;
             24          end;
             25      end loop;
             26
             27      dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );
             28
             29      l_status := dbms_sql.execute(l_theCursor);
             30
             31     dbms_sql.describe_columns(l_theCursor,l_colCnt, l_colDesc);
             32
             33     l_separator := '';
             34
             35     for lColCnt in 1..l_colCnt
             36     loop
             37              l_columnOutput := l_columnOutput || l_separator || Upper(l_colDesc(lColCnt).col_name);
             38         l_separator := '/';
             39     end loop;
             40
             41     DBMS_OUTPUT.PUT_LINE(l_columnOutput);
             42     DBMS_OUTPUT.PUT_LINE(CHR(13));
             43
             44      l_columnOutput := '';
             45
             46      loop
             47          exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
             48          l_separator := '';
             49          for i in 1 .. l_colCnt loop
             50              dbms_sql.column_value( l_theCursor, i, l_columnValue );
             51              l_columnOutput := l_columnOutput || l_separator || l_columnValue;
             52              l_separator := '/';
             53          end loop;
             54
             55          DBMS_OUTPUT.PUT_LINE(l_columnOutput);
             56
             57          l_columnOutput := '';
             58
             59          l_cnt := l_cnt+1;
             60      end loop;
             61
             62      dbms_sql.close_cursor(l_theCursor);
             63  end;
             64  /
            EMPNO/ENAME/JOB/MGR/HIREDATE/SAL/COMM/DEPTNO
            
            7369/SMITH/CLERK/7902/17-DEC-80/800//20
            7499/ALLEN/SALESMAN/7698/20-FEB-81/1600/300/30
            7521/WARD/SALESMAN/7698/22-FEB-81/1250/500/30
            7566/JONES/MANAGER/7839/02-APR-81/2975//20
            7654/MARTIN/SALESMAN/7698/28-SEP-81/1250/1400/30
            7698/BLAKE/MANAGER/7839/01-MAY-81/2850//30
            7782/CLARK/MANAGER/7839/09-JUN-81/2450//10
            7788/SCOTT/ANALYST/7566/19-APR-87/3000//20
            7839/KING/PRESIDENT//17-NOV-81/5000//10
            7844/TURNER/SALESMAN/7698/08-SEP-81/1500/0/30
            7876/ADAMS/CLERK/7788/23-MAY-87/1100//20
            7900/JAMES/CLERK/7698/03-DEC-81/950//30
            7902/FORD/ANALYST/7566/03-DEC-81/3000//20
            7934/MILLER/CLERK/7782/23-JAN-82/1300//10
            
            PL/SQL procedure successfully completed.
            Thanks,
            Karthick.
            • 3. Re: Getting column names from SYS_REFCURSOR
              436423
              Hi,

              Despite what Billy states it is very possible to get the column names from a weak ref cursor, or describe a ref cursor with PL/SQL. It has been possible since 8i, it just relies on the little known fact that a ref cursor in PL/SQL translates directly to a ResultSet in Java so we can use a tiny JSP in the DB.

              Here is the link to the completely free, Open Source code which allows you to do this: [XUTL_REFCURSOR|http://www.chrispoole.co.uk/apps/xutlrefcursor.htm]

              And a little example:
              SQL> variable scott_cursor refcursor
              SYS@ORA10GR2
              SQL> begin
                2  open :scott_cursor for select * from scott.emp;
                3  end;
                4  /

              PL/SQL procedure successfully completed.

              SYS@ORA10GR2
              SQL> begin
                2  xutl_refcursor.describe_columns(:scott_cursor);
                3  end;
                4  /

              PL/SQL procedure successfully completed.

              SYS@ORA10GR2
              SQL> select col_name from xutl_described_columns;

              COL_NAME
              --------------
              EMPNO
              ENAME
              JOB
              MGR
              HIREDATE
              SAL
              COMM
              DEPTNO

              SYS@ORA10GR2
              SQL> print :scott_cursor

                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
                    7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
                    7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
                    7521 WARD       SALESMAN        7698 22/02/1981 00:00:00       1250        500         30
                    7566 JONES      MANAGER         7839 02/04/1981 00:00:00       2975                    20
                    7654 MARTIN     SALESMAN        7698 28/09/1981 00:00:00       1250       1400         30
                    7698 BLAKE      MANAGER         7839 01/05/1981 00:00:00       2850                    30
                    7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
                    7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
                    7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10
                    7844 TURNER     SALESMAN        7698 08/09/1981 00:00:00       1500          0         30
                    7876 ADAMS      CLERK           7788 23/05/1987 00:00:00       1100                    20
                    7900 JAMES      CLERK           7698 03/12/1981 00:00:00        950                    30
                    7902 FORD       ANALYST         7566 03/12/1981 00:00:00       3000                    20
                    7934 MILLER     CLERK           7782 23/01/1982 00:00:00       1300                    10

              14 rows selected.

              SYS@ORA10GR2
              SQL>
              As the demo shows describing the ref cursor does not affect the cursor in any way, it is not selected from. It is not converted into a DBMS_SQL cursor and can be passed to a front end.

              [XUTL_REFCURSOR|http://www.chrispoole.co.uk/apps/xutlrefcursor.htm] is designed to have exactly the same output and uses the same return type as DBMS_SQL DESCRIBE COLUMNS, to enable pre-existing code that uses that API to be quickly re-used.

              HTH

              Chris
              • 4. Re: Getting column names from SYS_REFCURSOR
                Billy~Verreynne
                Evil! +<holding up a bag of garlic and waving a wooden stake around>+ ;-)

                Chris, something is just inherently wrong IMO making use of Java inside Oracle to process a cursor. You want Java ref cursor functionality? That is called a DBMS_CURSOR in PL/SQL. That is the structure that give you the same interface and same flexibility in PL/SQL, than what a ref cursor gives Java.

                A ref cursor has little use in PL/SQL as its primary reason for existence is to service as a cursor interface for external programs (Java. C#, Delphi. etc). As PL/SQL runs in a totally different environment, it makes very little sense to think that a ref cursor is the same to PL/SQL as what it is to Java. I think it is more of a case of confusion than anything else. Simply use the right tool in PL/SQL. And that is called DBMS_SQL when you need full blown dynamic binding and a describe interface.
                • 5. Re: Getting column names from SYS_REFCURSOR
                  XYZ123
                  thanks for all responses and examples. I am appreciating all of you.
                  • 6. Re: Getting column names from SYS_REFCURSOR
                    MichaelS
                    the question is can i get the column names from refcursor?
                    Here's another way: [Column names in cursor  | http://forums.oracle.com/forums/thread.jspa?messageID=1797454&#1797454]