How to get the contents of unknown SYS REFCURSOR

You can use the following procedure in order to get the unknown number of columns and column types.

DECLARE

    V_CURSOR SYS_REFCURSOR;

    PROCEDURE REFCURSOR_DESC(P_CURSOR SYS_REFCURSOR)
    IS
        V_COLCNT NUMBER;
        ARR_DESC DBMS_SQL.DESC_TAB;
        V_TYPE VARCHAR2(50);
        V_CURSOR SYS_REFCURSOR;
    BEGIN
           
        V_CURSOR := P_CURSOR;
        DBMS_SQL.DESCRIBE_COLUMNS(C => DBMS_SQL.TO_CURSOR_NUMBER(V_CURSOR),
                                  COL_CNT => V_COLCNT,
                                  DESC_T  => ARR_DESC 
                                 );

        FOR I IN 1 .. V_COLCNT
        LOOP
            V_TYPE :=  CASE ARR_DESC(I).COL_TYPE
                            WHEN 1 THEN 'VARCHAR2'
                            WHEN 2 THEN 'NUMBER'
                            WHEN 12 THEN 'DATE'
                            WHEN 96 THEN 'CHAR'
                            WHEN 180 THEN 'TS'
                            ELSE 'UNKNOWN'
                        END;
            --Display column names and types of dynamic sql  
            DBMS_OUTPUT.PUT_LINE(ARR_DESC(I).COL_NAME || ' ' || V_TYPE || ' (' || ARR_DESC(I).COL_MAX_LEN ||')');
            
        END LOOP;  
                                         
    END;
    
BEGIN

    --Your sys_refcursor
    OPEN V_CURSOR FOR SELECT * FROM HR.EMPLOYEES;
    
    REFCURSOR_DESC(V_CURSOR);


END;
Leave a Reply

Your email address will not be published.