How to access the structure and get the column lis

问题:

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;**

回答1:

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

回答2:

Since you're on 11g, you can use the dbms_sql package to interrogate your ref cursor, and then loop over the column types. They are reported as numbers so you'll need to translate the type numbers to strings (listed here).

This is a demo to give you the idea:

set serveroutput on
DECLARE
  -- mimicking your procedure arguments
  wrapper_ SYS_REFCURSOR;
  datyapes_ VARCHAR(100);

  L_COLS NUMBER;
  L_DESC DBMS_SQL.DESC_TAB;
  L_CURS INTEGER;
  L_VARCHAR VARCHAR2(4000);
BEGIN
  -- fake cursor, instead of procedure call
  open wrapper_ for q'[select 42, 'Test', date '2017-03-02' from dual]';

  L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(wrapper_);
  DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
    DESC_T => L_DESC);

  FOR i IN 1..L_COLS LOOP
    datyapes_ := datyapes_ || CASE WHEN i > 1 THEN '|' END
      || CASE L_DESC(i).col_type
        WHEN 1 THEN 'VARCHAR2'
        WHEN 2 THEN 'NUMBER'
        WHEN 12 THEN 'DATE'
        WHEN 96 THEN 'CHAR'
        WHEN 180 THEN 'TS'
        -- more types as needed
        ELSE 'unknown'
      END;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(L_CURS);

  -- just for debug
  dbms_output.put_line('datyapes_: ' || datyapes_);
END;
/

which gets output:

datyapes_: NUMBER|CHAR|DATE

PL/SQL procedure successfully completed.

I've kept your variable name as datyapes_ as shown in the question, but perhaps you really have datatypes_.