how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR?

Question

W.r.t code below I can not declare the type of fetch-into-variable as the underlying table's %ROWTYPE because the SYS_REFCURSOR is on a select that joins two tables and also selects a few functions called on the attributes of the underlying two tables; i.e I can't declare as L_RECORD T%ROWTYPE

---
DECLARE
  P_RS SYS_REFCURSOR;
  L_RECORD P_RS%ROWTYPE;
BEGIN
  CAPITALEXTRACT(
    P_RS => P_RS
  );
    OPEN P_RS;
    LOOP
      BEGIN
        FETCH P_RS INTO L_RECORD;
        EXIT WHEN P_RS%NOTFOUND;
        ...
      EXCEPTION
        WHEN OTHERS THEN
        ...
      END;
    END LOOP;
    CLOSE P_RS;
END;
--------
CREATE or REPLACE PROCEDURE CAPITALEXTRACT
(
    p_rs OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN p_rs for 
     select t.*,tminusone.*, f(t.cash), g(t.cash) FROM T t, TMINUSONE tminusone
    where t.ticket=tminusone.ticket;
END CAPITALEXTRACT;

Of course I don't want to define a static table R with columns as returned in the SYS_REFCURSOR and then declare as L_RECORD R%ROWTYPE.

And hence the question: how to declare %ROWTYPE of a variable that is a weakly typed SYS_REFCURSOR ?

Answer 1

The short answer is, you can't. You'd need to define a variable for each column that wil be returned.

DECLARE
    P_RS SYS_REFCURSOR;
    L_T_COL1 T.COL1%TYPE;
    L_T_COL1 T.COL2%TYPE;
    ...

And then fetch into the list of columns:

FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ;

This is painful but manageable as long as you know what you're expecting in the ref cursor. Using T.* in your procedure makes this fragile though, as adding a column to the table would break the code that thinks it knows what columns there are and what order they're in. (You can also break it between environments if the tables aren't built consistently - I've seen places where column ordering is different in different environments). You'll probably want to make sure you're only selecting the columns you really care about anyway, to avoid having to define variables for things you'll never read.

From 11g you can use the DBMS_SQL package to convert your sys_refcursor into a DBMS_SQL cursor, and you can interrogate that to determine the columns. Just as an example of what you can do, this will print out the value of every column in every row, with the column name:

DECLARE
    P_RS SYS_REFCURSOR;
    L_COLS NUMBER;
    L_DESC DBMS_SQL.DESC_TAB;
    L_CURS INTEGER;
    L_VARCHAR VARCHAR2(4000);
BEGIN
    CAPITALEXTRACT(P_RS => P_RS);
    L_CURS := DBMS_SQL.TO_CURSOR_NUMBER(P_RS);
    DBMS_SQL.DESCRIBE_COLUMNS(C => L_CURS, COL_CNT => L_COLS,
        DESC_T => L_DESC);

    FOR i IN 1..L_COLS LOOP
        DBMS_SQL.DEFINE_COLUMN(L_CURS, i, L_VARCHAR, 4000);
    END LOOP;

    WHILE DBMS_SQL.FETCH_ROWS(L_CURS) > 0 LOOP
        FOR i IN 1..L_COLS LOOP
            DBMS_SQL.COLUMN_VALUE(L_CURS, i, L_VARCHAR);
            DBMS_OUTPUT.PUT_LINE('Row ' || DBMS_SQL.LAST_ROW_COUNT
                || ': ' || l_desc(i).col_name
                || ' = ' || L_VARCHAR);
        END LOOP;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(L_CURS);
END;
/

That's not of much practical use, and for brevity I'm treating every value as a string since I just want to print it anyway. Look at the docs and search for examples for more practical applications.

If you only want a few columns from your ref cursor you could, I suppose, loop around l_desc and record the position where column_name is whatever you're interested in, as a numeric variable; you could then refer to the column by that variable later where you would normally use the name in a cursor loop. Depends what you're doing with the data.

But unless you're expecting to not know the column order you're getting back, which is unlikely since you seem to control the procedure - and assuming you get rid of the .*s - you're probably much better off reducing the returned columns to the minimum you need and just declaring them all individually.