ORA-00900: invalid SQL statement- when run a package in oracle 12c

Question

I am using Oracle 12c database and trying to run a package using SQL commands.

CREATE OR REPLACE PACKAGE "PK_CP_OTM" as
FUNCTION F_CP_OPTIMIZATION (
     v_current_day IN VARCHAR2,
     v_branch_code IN VARCHAR2)
     RETURN VARCHAR2;
END PK_CP_OTM;

When I try to execute it using:

DECLARE
BEGIN
EXECUTE IMMEDIATE PK_CP_OTM.F_CP_OPTIMIZATION('20190409','BRNCD001');
END;

It shows:

ORA-00900: invalid SQL statement
ORA-06512: at line 3
00900. 00000 -  "invalid SQL statement"

Thanks for your help.

Answer 1

As @Littlefoot said, you don't need dynamic SQL here, you can make a static call; but as you are calling a function you do need somewhere to put the result of the call:

declare
  l_result varchar2(30); -- make it a suitable size 
begin
  l_result := pk_cp_otm.f_cp_optimization('20190409','BRNCD001');
end;
/

In SQL*Plus, SQL Developer and SQLcl you can use the execute client command (which might have caused some confusion) and a bind variable for the result:

var result varchar2(30);

exec :result := pk_cp_otm.f_cp_optimization('20190409','BRNCD001');

print result