Search code examples
sqloracle-databaseplsql

Anon PL/SQL block to test FUNCTION with return of REF CURSOR and OUTPUT variable

I have a FUNCTION inside a PACKAGE which ALSO has an OUTPUT variable

create package sum_pkg

TYPE rc is REF CURSOR;

function sum_detail
(
    p_name in varchar2,
    p_out  out varchar2
)
return rc
as
   begin
   ...
end;

/

I am testing as follows but getting error:

    var x REFCURSOR;

    exec :x := sum_pkg.sum_detail ( p_name = 'abc', p_out => :x ) ;

    print :x

But I am getting : Identifier X must be declared.

Solution

  • You are returning a cursor and a string, you need two variables:

    VARIABLE x REFCURSOR;
VARIABLE y VARCHAR2(10);

EXECUTE :x := sum_pkg.sum_detail( p_name = 'abc', p_out => :y ) ;

PRINT :x
PRINT :y

    Then, if you are using SQL Developer, run the SQL file as a script using F5 or the icon with a green triangle with a page behind it (don't use Ctrl + Enter, or the green triangle icon, as it only runs a single statement and not the entire script).