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

Question

The error code is below. I cannot declare the type fetch-in-variable as the base table% ROWTYPE, because SYS_REFCURSOR is on an element that joins two tables, and also selects several functions called attributes of the underlying two tables; I can not 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 do not want to define a static table R with columns returned by SYS_REFCURSOR, and then declare it as L_RECORD R% ROWTYPE.

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

Answer 1

Short answer: you cannot. You will need to define a variable for each column to be returned.

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

And then enter into the list of columns:

 FETCH P_RS INTO L_T_COL1, L_T_COL2, ... ; 

This is painful but controllable if you know what you expect in the ref cursor. Using T.* in your procedure makes this fragile, although adding a column to the table will result in code breaking that thinks it knows which columns exist and what order they are in. (You can also split it between environments if the tables are not built sequentially - I saw places where the ordering of the columns differs in different environments). You will probably want to make sure that you only select the columns that you really like to avoid having to define variables for things that you will never read.

From 11g, you can use the DBMS_SQL package to convert your sys_refcursor to a DBMS_SQL cursor, and you can poll it to determine the columns. As an example of what you can do, this will print the value of each column in each 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; / 

This is not a very practical use, and for brevity, I treat each value as a string, since I just want to print it. Look at the docs and find examples for more practical applications.

If you only need a few columns from your ref cursor, you can, I suppose, cross l_desc and write the position where column_name is what you are interested in as a numeric variable; you could then refer to the column of this variable later, where you would normally use the name in the cursor loop. Depends on what you do with the data.

But if you do not expect to know the order of the columns that you are returning about, this is unlikely because you seem to be in control of the procedure - and if you get rid of .* - you are probably much better off reducing the returned columns to the minimum you need and just announcing them individually.