Oracle - select a specific column from the ref cursor

Question

I have a table called Table1. It has many columns, one of them is column1. I do not know other columns, sometimes they can change. There is a strongly typed type of ref cursor that returns Table1% rowtype named cur_Table1. I have a stored procedure called SP1 that has an out parameter of type cur_Table1. I call this stored procedure SP1 from another database that sees only this stored procedure, but not the table or the type itself. How to select only Column1 from the returned cursor? I know that I can take a record or as many variables as the cursor has columns, but I only know about the existence of one column, so I can not declare a full record or the correct number of variables.

Answer 1

You can do this with DBMS_SQL , but it is not.

Table and sample data (COLUMN1 is numbered 1-10):

 create table table1(column1 number, column2 date, column3 varchar2(1000), column4 clob); insert into table1 select level, sysdate, level, level from dual connect by level <= 10; commit; 

A package with a procedure that opens the ref cursor and selects everything:

 create or replace package test_pkg is type cur_Table1 is ref cursor return table1%rowtype; procedure sp1(p_cursor in out cur_table1); end; / create or replace package body test_pkg is procedure sp1(p_cursor in out cur_table1) is begin open p_cursor for select column1, column2, column3, column4 from table1; end; end; / 

A PL / SQL block that reads COLUMN1 data from the ref cursor:

 --Basic steps are: call procedure, convert cursor, describe and find columns, --then fetch rows and retrieve column values. -- --Each possible data type for COLUMN1 needs to be added here. --Currently only NUMBER is supported. declare v_cursor sys_refcursor; v_cursor_number number; v_columns number; v_desc_tab dbms_sql.desc_tab; v_position number; v_typecode number; v_number_value number; begin --Call procedure to open cursor test_pkg.sp1(v_cursor); --Convert cursor to DBMS_SQL cursor v_cursor_number := dbms_sql.to_cursor_number(rc => v_cursor); --Get information on the columns dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab); --Loop through all the columns, find COLUMN1 position and type for i in 1 .. v_desc_tab.count loop if v_desc_tab(i).col_name = 'COLUMN1' then v_position := i; v_typecode := v_desc_tab(i).col_type; --Pick COLUMN1 to be selected. if v_typecode = dbms_types.typecode_number then dbms_sql.define_column(v_cursor_number, i, v_number_value); --...repeat for every possible type. end if; end if; end loop; --Fetch all the rows, then get the relevant column value and print it while dbms_sql.fetch_rows(v_cursor_number) > 0 loop if v_typecode = dbms_types.typecode_number then dbms_sql.column_value(v_cursor_number, v_position, v_number_value); dbms_output.put_line('Value: '||v_number_value); --...repeat for every possible type end if; end loop; end; / 

Answer 2

Given the original question, jonearles answer is still correct, so I will leave it marked as such, but in the end I did something completely different and much better.

The problem was that I did not control the SP1 database, I just needed to call it from another location as a third-party client. Now I managed to get permission to see not only the SP, but also the type of cursor. I still don't see the table, but now there is a much cleaner solution:

In another database, I was granted access to see this type now:

 type cur_Table1 is ref cursor return Table1%rowtype; 

So, in my database, I can do it now:

 mycursor OtherDB.cur_Table1; myrecord mycursor%rowtype; ... OtherDB.SP1(mycursor); fetch mycursor into myrecord; dbms_output.put_line(myrecord.Column1); 

Look, I still do not need access to the table, I see only the cursor. The key is that the magic% rowtype also works for cursors, not just tables. It does not work on sys_refcursor, but it works on a strongly typed one. Given this code, I don’t need to care if something changes on the other hand, I don’t need to define all columns or records at all, I just indicate one column that interests me.

I really like this attitude of OOP towards Oracle.

Answer 3

I don’t know if this is an option or not, but not the best solution would be to create a function that returns the specific value you are looking for? This avoids the overhead of sending additional data. In addition, you can define a cursor with a set of fields known in it that both sides are aware of.