running a package function in PL/SQL

Question

I have a package which has a function which takes 4 parameters of which 3 are out parameters

Package Definition

 function get_sip_movement (
      pi_report_instance_id   in     number,
      po_plan_name               out plans.plan_name%type,
      po_client_comp_name        out companies.comp_name%type,
      po_comp_code               out companies.comp_code%type)
      return sys_refcursor;

I would like to run this function. I tried to create a function to output the results of the function.

CREATE OR REPLACE FUNCTION EMP_REFCURSOR_F
  RETURN SYS_REFCURSOR
AS
  L_EMP_REF_CURSOR   SYS_REFCURSOR;
   po_plan_name VARCHAR2(10 CHAR);
      po_client_comp_name VARCHAR2(50 CHAR);
      po_comp_code VARCHAR2(10 CHAR);
BEGIN
  OPEN L_EMP_REF_CURSOR FOR
   select rr400_generate_sip_movement.get_sip_movement(137610, po_plan_name, po_client_comp_name, po_comp_code) from dual;
  RETURN L_EMP_REF_CURSOR;
END;

When I run this function I see an error

ORA-06572: Function GET_SIP_MOVEMENT has out arguments

I have modified my function to only include the input parameter

CREATE OR REPLACE FUNCTION EMP_REFCURSOR_F
  RETURN SYS_REFCURSOR
AS
  L_EMP_REF_CURSOR   SYS_REFCURSOR;
   po_plan_name VARCHAR2(10 CHAR);
      po_client_comp_name VARCHAR2(50 CHAR);
      po_comp_code VARCHAR2(10 CHAR);
BEGIN
  OPEN L_EMP_REF_CURSOR FOR
   select rr400_generate_sip_movement.get_sip_movement(137610) from dual;
  RETURN L_EMP_REF_CURSOR;
END;

Now when I try to run this. I get this error

LINE/COL  ERROR
--------- -------------------------------------------------------------
10/4      PL/SQL: SQL Statement ignored
10/11     PL/SQL: ORA-00904: "RR400_GENERATE_SIP_MOVEMENT"."GET_SIP_MOVEMENT": invalid identifier
10/39     PLS-00306: wrong number or types of arguments in call to 'GET_SIP_MOVEMENT'
Errors: check compiler log

What am I missing here. Sorry I am really new to PL/SQL. I am using SQL Developer for this.

Answer 1

You don't call that function from SQL.

If a function has OUT parameters then it can only be called from PL/SQL and not SQL.

Something like:

CREATE FUNCTION EMP_REFCURSOR_F
  RETURN SYS_REFCURSOR
AS
  v_plan_name plans.plan_name%type;
  v_comp_name companies.comp_name%type;
  v_comp_code companies.comp_code%type;
BEGIN
  RETURN rr400_generate_sip_movement.get_sip_movement(
           137610,
           v_plan_name,
           v_comp_name,
           v_comp_code
         );
END;
/

Note: Having OUT parameters on a function is not usual practice. If you want to return multiple parameters then it is usual to use a PROCEDURE.