Execute Oracle Package with Function in PL/SQL

Question

Is there a way to execute a package function in Oracle PL/SQL?

Here is a sample package with a function that returns a Ref Cursor:

create or replace PACKAGE "PKG_PACKAGE1"
AS
    TYPE CURS_OUT IS REF CURSOR;
    FUNCTION fnc_PACKAGE1(PARAM1 VARCHAR2, PARAM2 INT) RETURN CURS_OUT;
 END "PKG_PACKAGE1";


create or replace PACKAGE BODY "PKG_PACKAGE1"
AS
FUNCTION fnc_PACKAGE1(PARAM1 VARCHAR2, PARAM2 INT) RETURN CURS_OUT
AS
    RUNCURS CURS_OUT;
        BEGIN
            OPEN  RUNCURS FOR
                SELECT 'Data Returned' FROM DUAL;
            RETURN RUNCURS;
            END;
END "PKG_PACKAGE1";

Here is how I want to execute it in PL/SQL using SQL Developer Tools:

 BEGIN
   :returnRes := PKG_PACKAGE1.FNC_PACKAGE1(:PARAM1,:PARAM2);
 END;

OR

Declare 
returnRes Varchar2(200);
BEGIN
   returnRes := PKG_PACKAGE1.FNC_PACKAGE1(:PARAM1,:PARAM2);
END;

When I run either I get error: PLS-00382: expression is of wrong type. When I run the package through the wizard it to returns the value.

Is there a specific way to execute packages this way or is it not possible?

Thanks.

Alex Poole · Accepted Answer · 2018-06-21 10:44:36Z

The variable you use to receive the result returned by the function has to be the same type:

DECLARE 
   returnRes PKG_PACKAGE1.CURS_OUT;
BEGIN
   returnRes := PKG_PACKAGE1.FNC_PACKAGE1(:PARAM1,:PARAM2);
END;

You would then need to iterate over the returned cursor to see the actual string value from the function's query, e.g.:

set serveroutput on
DECLARE 
   returnRes PKG_PACKAGE1.CURS_OUT;
   res varchar2(200);
BEGIN
   returnRes := PKG_PACKAGE1.FNC_PACKAGE1(:PARAM1,:PARAM2);
   loop
      fetch returnRes into res;
      exit when returnRes%NOTFOUND;
      dbms_output.put_line(res);
    end loop;
END;
/

You might want to consider using SYS_REFCURSOR instead of defining your own ref cursor type, incidentally; i.e.:

create or replace package pkg_package1
as
    function fnc_package1(param1 varchar2, param2 int)
    return sys_refcursor;
end pkg_package1;
/

create or replace package body pkg_package1
as
    function fnc_package1 (param1 varchar2, param2 int)
    return sys_refcursor as
        runcurs sys_refcursor;
    begin
        open  runcurs for
            select 'Data Returned' from dual;
        return runcurs;
    end fnc_package1;
end pkg_package1;
/

the anonymous block above would still work, as long as you change the variable to match:

DECLARE 
   returnRes sys_refcursor;
   res varchar2(200);
BEGIN
...

but you coudld then use your first form instead, as the clock you have or with the exec wrapper if you're using SQL*Plus or SQL Developer:

var param1 number;
var param2 number;
var returnres refcursor;

exec :returnres := pkg_package1.fnc_package1(:param1,:param2);

print :returnRes

'DATARETURNED
-------------
Data Returned

The function arguments aren't being used yet, but as they are there you still need to declare variables for them, even if they're left null.

Collectives™ on Stack Overflow

Execute Oracle Package with Function in PL/SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related