Oracle - select a specific column from a ref cursor
You can do this with DBMS_SQL
, but it ain't pretty.
Table and sample data (COLUMN1 has the numbers 1 - 10):
create table table1(column1 number, column2 date, column3 varchar2(1000), column4 clob);insert into table1select level, sysdate, level, level from dual connect by level <= 10;commit;
Package with a procedure that opens a 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;/
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;/
Given the original question, jonearles's answer is still correct, so I'll leave it marked as such, but I ended up doing something completely different and much better.
The problem was/is that I have no control over SP1's database, I just have to call it from somewhere else as a 3rd party client. Now I managed to get permission to see not only SP, but also the type of the cursor. I still don't see the table but now there is a much cleaner solution:
In the other database I have been granted access to see this type now:
type cur_Table1 is ref cursor return Table1%rowtype;
So in my database I can do this now:
mycursor OtherDB.cur_Table1;myrecord mycursor%rowtype;...OtherDB.SP1(mycursor);fetch mycursor into myrecord;dbms_output.put_line(myrecord.Column1);
See, I still don't need any access to the table, I see the cursor only. The key is that the magical %rowtype works for cursors as well, not just tables. It doesn't work on a sys_refcursor, but it does on a strongly typed one. Given this code, I don't have to care if anything changes on the other side, I don't have to define all the columns or records at all, I just specify the one column I'm interested in.
I really love this OOP attitude about Oracle.
Don't know if it's an option or not, but wouldn't a better solution be to create a function that returns the specific value you're looking for? That avoids the overhead of sending the extra data. Alternatively, you could define a cursor with a set of known fields in it that both parties know about.