Struggling to collect and return DBMS_SQL.COLUMN_VALUE using User Defined Type

Question

This is my first question on StackOverflow and I'm self taught so please be gentle.

My goal here is to be able to bulk collect headers/values from a dynamic query/cursor in a generated package via

    SELECT * 
    BULK COLLECT INTO l_cur_val 
    FROM TABLE (CUSTOM.GET_REF_VAL(l_cursor));

I have this working successfully for column headers in a similar block of code to GET_REF_VAL (simply RETURN l_col_head before entering the /* COLUMN VALUES */ section).

The errors are coming when I'm trying to assign the return value of DBMS_SQL.COLUMN_VALUE into my t_col_val UDT. (Type definitions are in comments)

TYPES

CREATE OR REPLACE TYPE CUSTOM.r_col_val IS OBJECT (l_col_val VARCHAR2(250 byte));
CREATE OR REPLACE TYPE CUSTOM.t_col_val IS TABLE OF CUSTOM.r_col_val;

ERRORS

--l_val(n) := r_col_val(l_dum_val);    --returns: ORA-06533: Subscript beyond count
--l_val(n) := l_dum_val;               --returns: PLS-00382: expression is of wrong type

Table return function GET_REF_VAL

CREATE OR REPLACE FUNCTION 
CUSTOM.GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

l_val t_col_val := t_col_val();
l_col t_col_head := t_col_head();

n INTEGER := 0;

l_cursor        SYS_REFCURSOR := p_cursor;
l_cursor_id     INTEGER;
l_dummy         INTEGER;
l_col_cnt       INTEGER;
l_tab_rec       DBMS_SQL.DESC_TAB2;
l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

/* COLUMN HEADERS */
FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        n := n + 1;
        l_col(n) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

/* COLUMN VALUES */
LOOP 
    IF DBMS_SQL.FETCH_ROWS(l_cursor_id)> 0 THEN 

    FOR i IN 1 .. l_col_cnt
    LOOP

        l_val.extend;
        n := n + 1;
        DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
        DBMS_OUTPUT.PUT_LINE(l_dum_val); -- This return l_dum_val with no issues

        --l_val(n) := r_col_val(l_dum_val); -- ORA-06533: Subscript beyond count
        --l_val(n) := l_dum_val; --PLS-00382: expression is of wrong type

    END LOOP;

    ELSE 
    EXIT; 
    END IF;
END LOOP;  


DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
RETURN l_val;

END;
/

Execution block

DECLARE

l_sql_stmt VARCHAR(10000) :=
q'!
    SELECT
        SYS_CONTEXT('USERENV','OS_USER') AS OS_USER ,
        SYS_CONTEXT('USERENV','SESSION_USER') AS SESSION_USER,
        SYS_CONTEXT('USERENV','ISDBA') AS ISDBA,
        SYS_CONTEXT('USERENV','SID') AS SID,
        SYS_CONTEXT('USERENV','CURRENT_SQL') AS CURRENT_SQL,
        SYS_CONTEXT('USERENV','DB_NAME') AS DB_NAME,
        SYS_CONTEXT('USERENV','HOST') AS HOST,
        SYS_CONTEXT('USERENV','IP_ADDRESS') AS IP_ADDRESS,
        SYS_CONTEXT('USERENV','SERVICE_NAME') AS SERVICE_NAME
    FROM
        DUAL
!';

l_cursor        SYS_REFCURSOR;
l_cursor_id     INTEGER;
l_dummy         VARCHAR2(50);

TYPE t_cur_head IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_head t_cur_head;

TYPE t_cur_val IS TABLE OF VARCHAR2(250) INDEX BY BINARY_INTEGER;
l_cur_val t_cur_val;    

BEGIN

l_cursor := CUSTOM.GET_REF_CUR(l_sql_stmt);

IF l_cursor%ISOPEN
THEN

    /* Header fetch works fine */

    /*
    SELECT * 
    BULK COLLECT INTO l_cur_head 
    FROM TABLE (CUSTOM.GET_REF_HEAD(l_cursor));

    FOR i IN 1 .. l_cur_head.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_head(i));
    END LOOP;
    */


    /* Values fetch fails */

    SELECT * 
    BULK COLLECT INTO l_cur_val 
    FROM TABLE (CUSTOM.GET_REF_VAL(l_cursor));

    FOR i IN 1 .. l_cur_val.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE(l_cur_val(i));
    END LOOP;

END IF;

END;

So I guess in summary what I want to know is

a) How to handle the return value of dbms_sql.column_value using a user defined type

b) How insert a VARCHAR2 value (l_dum_val) into a UDT object with VARCHAR2 records (l_col_val)

c) Any other obvious errors/bad practices in the code?

Thank you for your time an patience.

Answer 1

The first of your commented-out lines:

    --l_val(n) := r_col_val(l_dum_val); -- ORA-06533: Subscript beyond count

gets that error because you are not resetting n to zero before the second loop. You don't really need that counter variable at all though, you can do use l_val.count instead (in both loops).

The second of your commented-out lines:

    --l_val(n) := l_dum_val; --PLS-00382: expression is of wrong type

gets that error because the l_val(n) is pointing to an object, which has a string attribute; it isn't pointing directly to a string. So you can assign a new object via its constructor; which is what the first version was trying to do, but it should be:

    l_val(l_val.count) := r_col_val(l_dum_val);

Once that object exists you can assign the attribute directly with:

    l_val(some_index).l_col_val := r_col_val(l_dum_val);

but you have to create an object before you can access its attributes, and as you only have a default constructor, that probably isn't going to be much use to you in this case.

So with those changes (and some indentation, and refactoring slightly to get rid of the else) this now works:

CREATE OR REPLACE FUNCTION 
GET_REF_VAL
    (
    p_cursor IN SYS_REFCURSOR
    )
RETURN t_col_val
IS

    l_val t_col_val := t_col_val();
    l_col t_col_head := t_col_head();

    l_cursor        SYS_REFCURSOR := p_cursor;
    l_cursor_id     INTEGER;
    l_dummy         INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB2;
    l_dum_val       VARCHAR2(250);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(l_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    FOR r IN 1..l_col_cnt
    LOOP

        l_col.extend;
        l_col(l_col.count) := r_col_head(l_tab_rec(r).col_name);
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_dum_val, 4000); 

    END LOOP;

    /* COLUMN VALUES */
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            l_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_dum_val);        
            DBMS_OUTPUT.PUT_LINE(l_dum_val);
            l_val(l_val.count) := r_col_val(l_dum_val);

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    RETURN l_val;

END;
/

db<>fiddle

---

Your code suggests you have a separate function to get the headers, so you're duplicating code. You could simplify into one procedure with two out variables instead:

CREATE OR REPLACE PROCEDURE
GET_REF_HEAD_AND_VAL
    (
    p_cursor IN OUT SYS_REFCURSOR,
    p_col OUT SYS.odcivarchar2list,
    p_val OUT SYS.odcivarchar2list
    )
IS

    l_cursor_id     INTEGER;
    l_col_cnt       INTEGER;
    l_tab_rec       DBMS_SQL.DESC_TAB3;
    l_value         VARCHAR2(250 byte);

BEGIN

    l_cursor_id := DBMS_SQL.TO_CURSOR_NUMBER(p_cursor); 
    DBMS_SQL.DESCRIBE_COLUMNS3(l_cursor_id, l_col_cnt, l_tab_rec);

    /* COLUMN HEADERS */
    p_col := SYS.odcivarchar2list();
    FOR r IN 1..l_col_cnt
    LOOP

        p_col.extend;
        p_col(p_col.count) := l_tab_rec(r).col_name;
        DBMS_SQL.DEFINE_COLUMN(l_cursor_id, r, l_value, 250);

    END LOOP;

    /* COLUMN VALUES */
    p_val := SYS.odcivarchar2list();
    LOOP 
        IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN 
            EXIT; 
        END IF;

        FOR i IN 1 .. l_col_cnt
        LOOP

            p_val.extend;
            DBMS_SQL.COLUMN_VALUE(l_cursor_id, i, l_value);        
            --DBMS_OUTPUT.PUT_LINE(l_dum_val);
            p_val(p_val.count) := l_value;

        END LOOP;
    END LOOP;  

    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);

END;
/

This is using a built-in collection type rather than creating your own object/table types (though you could still create your own collection type; it doesn't need to used objects though).

db<>fiddle