Function Return Sys_Refcursor Call from SQL with Specific Columns

Function return sys_refcursor call from sql with specific columns

No, not with a ref cursor at all, and otherwise not without creating SQL types to cast the return into, like this example: http://dbaspot.com/oracle-server/9308-select-ref-cursor.html:

create or replace type myType as object (
a int,
b varchar2(10)
)
/

create or replace type myTable as table of myType;
/

create or replace function f1 return myTable as
l_data myTable := myTable();
begin
for i in 1 .. 5 loop
l_data.extend;
l_data(i) := myType(i, 'Row #'||i );
end loop;
return l_data;
end;
/

select * from TABLE ( cast( f1() as myTable ) );

---------- ----------
1 Row #1
2 Row #2
3 Row #3
4 Row #4
5 Row #5

From the last post on that thread:

the way you already knew about is the only one to use
the REF CURSOR in a select statement.

how to get value of column from sys_refcursor returned by a function being called

declare 
  src_cur sys_refcursor;
  curid    NUMBER;
  v_jobtitle varchar2(35);
  v_min_salary number;
begin 
  open src_cur for 'select job_id,job_title,min_salary,max_salary from jobs';
  curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
  dbms_sql.define_column(curid,2,v_jobtitle,35);
  dbms_sql.define_column(curid,3,v_min_salary);
   WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
       DBMS_SQL.COLUMN_VALUE(curid, 2, v_jobtitle);
       DBMS_SQL.COLUMN_VALUE(curid, 3, v_min_salary);
       DBMS_OUTPUT.put_line('job_titile='||v_jobtitle||' ;min_salary='||v_min_salary);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;

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 table1
select 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;
/

REF CURSOR get a column from a procedure

It is possible to just show column X from the result?

Not without additional coding, no.

As @OldProgrammer said in the comment to your question you can use dbms_sql package to describe columns and pick one you like.

But, if, as you said, you know column names, the probably easiest way to display contents of that column would be using XML functions, xmlsequence() and extract() in particular.

Unfortunately we cannot pass SQL*PLUS bind variable as a parameter to the xmlsequence() function, so you might consider to wrap your procedure in a function, which returns refcursor:

Test table:

create table t1(col, col2) as
  select level
       , level
    from dual
   connect by level <= 5;

SQL> select * from t1;

       COL       COL2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

Here is a simple procedure, which opens a refcursor for us:

create or replace procedure p1(
   p_cursor out sys_refcursor
) is
begin
  open p_cursor for
     select * from t1;
end;
/
Procedure created

Here is the function-wrapper for the p1 procedure, which simply executes the procedure and returns refcursor:

create or replace function p1_wrapper
return sys_refcursor is
  l_res sys_refcursor;
begin
  p1(l_res);
  return l_res;
end;
/
Function created

The query. Extract path is ROW/COL2/text(), where COL2 is the name of a column we want to print.

select t.extract('ROW/COL2/text()').getstringval() as res
  from table(xmlsequence(p1_wrapper)) t ;

Result:

RES                                                                             
--------
1                                                                               
2                                                                               
3                                                                               
4                                                                               
5                                                                               

5 rows selected.

Returning cursor from function in Oracle

Your function is returning a sys_refcursor and in your code you are returning a simple cursor. This makes the code wrong. If you want to return a ref_cursor from a function you can use as below:

create or replace function stuff (p_var number)
   return sys_refcursor
is
   rf_cur   sys_refcursor;
begin
   open rf_cur for
      select *
        from employee
       where employee_id = p_var;

   return rf_cur;
end stuff;

Execution:

select stuff(1) from dual;

RETURN REF CURSOR FROM PROCEDURE BY SELECT

The idea of a REF CURSOR is that you open the cursor, but whoever called you does the fetching and the closing. Using SQL*Plus or Oracle SQL Developer, here is a simple example using anonymous PL/SQL; the "print" command does the fetching, displaying and cursor closing.

SQL> var rc refcursor;
SQL> begin
  2    open :rc for select * from dual;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print :rc

D
-
X

Calling a function would look like this:

SQL> create or replace function f return sys_refcursor is
  2    l_rc sys_refcursor;
  3  begin
  4    open l_rc for select * from dual;
  5    return l_rc;
  6  end f;
  7  /

Function F compiled

SQL> exec :rc := f;

PL/SQL procedure successfully completed.

SQL> print :rc;

D
-
X

Best regards, Stew Ashton

---

Related Topics