oracleoracle11gcursorsys-refcursorref-cursor

How to access the structure and get the column list ,datatypes of refcursor?


I have a procedure which gets me the output with refcursor and data/structure in cursor will be dynami. Each time depending on inputs datatypes and no of columns in cursor will vary. So how can I access this structure and get the datatypes ?

PROCEDURE  PROC_B ( name_     IN       VARCHAR2,
                    date_     IN       DATE,
                    code_           IN       VARCHAR2,
                    sp_name_       IN       VARCHAR2,
                    wrapper_         OUT      sys_refcursor,
                    datyapes_     OUT      VARCHAR2,
                     TS2_     OUT      VARCHAR2,
                     header_   OUT      VARCHAR2)
AS
      TS_         DATE;
BEGIN
      PROC_A (name_, date_, code_, sp_name_, wrapper_, TS_, header_);
      TS2_:= TO_CHAR(TS_, 'MM-DD-YYYY.HH24_MI');

      -- Logic should come here for below requirement 
-- Get the datatypes of variables from wrapper_ (ref cursor datatype) and send them back in datyapes_ . 
 -- Eg1 : If ref cursor returns 2 values with dataytpes VARCHAR2 & Num then o/p should be VARCHAR2|NUMBER , 
 --Eg2 : If ref cursor returns 3 values with dataytpes DATE , TIMESTAMP , VARCHAR2 then o/p should be DATE|TS|VARCHAR2

  END;**

Solution

  • You can convert the ref cursor to a DBMS_SQL cursor using the DBMS_SQL.TO_CURSOR_NUMBER function. Then, having the cursor number, you can inspect manipulate it via DBMS_SQL. This includes being able to describe it's columns, as shown in the example below:

    DECLARE
      l_rc              SYS_REFCURSOR;
    
      l_cursor_number   INTEGER;
      l_col_cnt         INTEGER;
      l_desc_tab        DBMS_SQL.desc_tab;
      l_col_num         INTEGER;
    BEGIN
      OPEN l_rc FOR 'SELECT object_name, object_type, last_ddl_time FROM dba_objects where rownum <= 10';
    
      l_cursor_number   := DBMS_SQL.to_cursor_number (l_rc);
    
      DBMS_SQL.describe_columns (l_cursor_number, l_col_cnt, l_desc_tab);
    
      l_col_num         := l_desc_tab.FIRST;
    
      IF (l_col_num IS NOT NULL) THEN
        LOOP
          DBMS_OUTPUT.put_line ('Column #' || l_col_num);
          DBMS_OUTPUT.put_line ('...name: ' || l_desc_tab (l_col_num).col_name);
          DBMS_OUTPUT.put_line ('...type: ' || l_desc_tab (l_col_num).col_type);
          DBMS_OUTPUT.put_line ('...maxlen: ' || l_desc_tab (l_col_num).col_max_len);
          -- ... other fields available in l_desc_tab(l_col_num) too.
          l_col_num   := l_desc_tab.NEXT (l_col_num);
          EXIT WHEN (l_col_num IS NULL);
        END LOOP;
      END IF;
    
      DBMS_SQL.close_cursor (l_cursor_number);
    END;
    

    Output

    Column #1
    ...name: OBJECT_NAME
    ...type: 1
    ...maxlen: 128
    Column #2
    ...name: OBJECT_TYPE
    ...type: 1
    ...maxlen: 23
    Column #3
    ...name: LAST_DDL_TIME
    ...type: 12
    ...maxlen: 7