Skip to Main Content
  • Questions
  • How to fetch one column from a sys_refcursor?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rohit.

Asked: October 12, 2018 - 8:55 am UTC

Last updated: October 15, 2018 - 12:39 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

How do I get only few columns from a sys_refcursor and pass it as an input to another proc?

I have a proc which returns the result set in a sys_refcursor. A column from this output has to be passed as an input to another proc.

For eg:

proc_a(name in varchar2, details out sys_refcursor) --> This is the proc who's output is required.

The columns in "details" are -- (Id, department, mail_id, active_yn)

proc_b(id in number, department in varchar2, result out number) --> The inputs id and department are to be got from "details" which is the output of proc_a.

and Chris said...

I'm not aware of a way to fetch a subset of the columns from a cursor variable. If you want more or less columns, you should change the cursor definition.

If you're looking to simplify your code, you can use a record variable which has the same structure as the cursor. And reference the attributes of that you want:

create table t (
  c1 int, c2 int
);

insert into t values ( 1, 2 );
commit;

declare
  cur sys_refcursor;
  rec t%rowtype;
begin
  open cur for 
    select * from t;
    
  fetch cur into rec;
  
  dbms_output.put_line ( 'C1 = ' || rec.c1 );
    
  close cur;
end;
/

C1 = 1

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 15, 2018 - 6:34 am UTC

You can convert sys_refcursor to a dbms_sql cursor using dbms_sql.to_cursor_number and then parse and process it via dbms_sql.define_column, dbms_sql.fetch_rows, etc. - there you will have the full control on which columns to process.
Chris Saxon
October 15, 2018 - 12:39 pm UTC

True, though I'd still opt for changing the source cursor.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library