Calling a Function That Returns a Refcursor

Calling a function that returns a refcursor

Yes, use:

CREATE OR REPLACE FUNCTION function_1(refcursor) RETURNS refcursor AS $$
BEGIN
        OPEN $1 FOR SELECT * FROM some_table;
        RETURN $1;    
END;
$$ LANGUAGE plpgsql;

Result:

SELECT function_1('myowncursorname');
   function_1
-----------------
 myowncursorname
(1 row)

It looks like auto-generated name is <unnamed portal n>, where n is natural number (from 1).

EDIT:

As another way you could use pg_cursors view with such query to obtain generated cursor name:

SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';

For example:

BEGIN;
SELECT function_1();
SELECT name FROM pg_cursors WHERE statement LIKE 'SELECT * FROM some_table';
COMMIT;

Result:

     function_1
--------------------
 <unnamed portal 3>
(1 row)

        name
--------------------
 <unnamed portal 3>
(1 row)

How to call an Oracle function of refcursor return type using sqlalchemy and python

I used cx_Oracle instead of SQLAlchemy and it worked. Result output is same what I was expecting from sqlalchemy. I converted the function to procedure.

Procedure

CREATE or replace procedure get_data(data_key in integer, result out 
sys_refcursor) as
BEGIN

open result for 'Select DISTINCT COL1
        FROM ref_table
        where DATA_KEY='||data_key;

END;

Code for calling the above procedure using python and cx_Oracle

import cx_Oracle
import pandas as pd

conn = cx_Oracle.connect('system/<password>@127.0.0.1/XE')
cur = conn.cursor()

myvar = cur.var(cx_Oracle.CURSOR)
cur.callproc('get_data', (30038, myvar))
data = myvar.getvalue().fetchall()
if len(data) == 0:
    data = {}
df = pd.DataFrame(data, columns=[i[0] for i in myvar.getvalue().description])
print df

Output of the above code

                      COL1
--------------------------
0                   219586
1                   246751
2                   228245
3                   244517
4                   220765
5                   243467
6                   246622
7                   222784

How to call an Oracle function with a Ref Cursor and return the values on Visual Basic

You need to get the reader from the parameter, not from the command.

So instead of

Dim reader As OracleDataReader = rvCmd.ExecuteReader()

You need:

rvCmd.CommandType = CommandType.StoredProcedure
rvCmd.CommandText = "getallprodpckg.get_allcust"

Dim qrySerial3 As New Oracle.DataAccess.Client.OracleParameter(
    None, OracleDbType.RefCursor, ParameterDirection.ReturnValue)

rvCmd.Parameters.Add(qrySerial3)

rvCmd.ExecuteNonQuery()

Dim cursor as OracleRefCursor = DirectCast(qrySerial3.Value, OracleRefCursor)

Dim reader As OracleDataReader = cursor.GetDataReader()

Function returning REF CURSOR

declare 
  type rec is record(produc_id number, name varchar2(x), price number);
  type l_rec is table of rec; 
  v_l_rec l_rec; 
  v_cursor product_package.t_ref_cursor;
begin 
  v_cursor  := product_package.get_products_ref_cursor;
  fetch v_cursor  bulk collect into v_l_rec; 
  -- in v_l_rec is list collection of products.
  close v_cursor;
end;

Code wasn't tested.

fetch from function returning a ref cursor to record

I suspect that you think that your cursor should be fetching rows from the REFCURSOR. It's not. The REFCURSOR is itself a cursor, you don't use another cursor to select from it.

What your current cursor is doing is fetching a single row, with a single column, containing the result of the function call. Which is a record_cursor not a record_name, so you get a type mismatch.

I suspect what you really want to do is something like this:

declare
  symbol_cursor  package_name.record_cursor;
  symbol_record  package_name.record_name;
begin
  symbol_cursor := package_name.function_name('argument');
  loop
    fetch symbol_cursor into symbol_record;
    exit when symbol_cursor%notfound;

    -- Do something with each record here, e.g.:
    dbms_output.put_line( symbol_record.field_a );

  end loop;

  CLOSE symbol_cursor;

end;

How to call oracle function which return sys_refcursor using java + hibernate

This is how to get the result in a Java class (hopefully you can port it to Hibernate using this question as an exemplar):

Oracle Setup:

CREATE FUNCTION get_ref_cursor RETURN SYS_REFCURSOR
IS
  out_cursor SYS_REFCURSOR;
BEGIN
  OPEN out_cursor FOR
    SELECT 123 AS col1 FROM DUAL UNION ALL
    SELECT 456 FROM DUAL UNION ALL
    SELECT 789 FROM DUAL;

  RETURN out_cursor;
END;
/

Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

public class GetRefCursorFromFunction
{
  public static void main( final String[] args ){
    try{
      Class.forName( "oracle.jdbc.OracleDriver" );

      Connection con = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:orcl",
          "USERNAME",
          "PASSWORD"
      );

      OracleCallableStatement st =
        (OracleCallableStatement) con.prepareCall( "BEGIN :1 := get_Ref_Cursor(); END;" );
      st.registerOutParameter( 1, OracleTypes.CURSOR );
      System.out.println( st.execute() );
      ResultSet rs = st.getCursor( 1 );
      while ( rs.next() )
      {
        System.out.println( rs.getInt( 1 ) );
      }

      st.close();
      con.close();
    } catch (ClassNotFoundException | SQLException ex) {
      System.out.println( ex.getMessage() );
      ex.printStackTrace();
    }
  }
}

(Note: this assumes you are using Oracle's driver to connect to the database.)

Output:

123
456
789

Returning a ref cursor from a Oracle Function

A couple of mistakes, look into my working example, just changed the source table:

CREATE OR REPLACE FUNCTION  TEST_CUR RETURN SYS_REFCURSOR
AS
   VAR_REF SYS_REFCURSOR;
BEGIN
    OPEN VAR_REF FOR
        SELECT *
        FROM DUAL;

    RETURN VAR_REF;
END;

Here you don't need to open the cursor, it is already opened.

DECLARE
    L_VAR SYS_REFCURSOR;
    L_STATUS VARCHAR2(10);
BEGIN
    L_VAR:=TEST_CUR;
    LOOP
        FETCH L_VAR INTO L_STATUS;
        EXIT WHEN L_VAR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE( L_STATUS );
    END LOOP;
    CLOSE L_VAR;
END;

A really interesting post showing how to test oracle cursors:

5 different ways to test Oracle Ref Cursor results

---

Related Topics