Sorry to ask such a basic question, but I am yet to do my Oracle course at work and I am already required to do some run PL/SQL processes.
I have installed a 'package' using SQL Developer, see it below (C_FIX_STUCK_COS).
But now I need to execute it from the SQL> prompt using SQLPlus (and finally from a Bash script).
So having added a 'stored procedure' previously and got that to run with the execute command I tried that again with my package.
execute C_FIX_STUCK_COS
But it fails with the following error:
BEGIN C_FIX_STUCK_COS; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'C_FIX_STUCK_COS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I guess that I am not using the correct method of execution, but haven't managed to find anything online yet that will help me.
The called procedure 'inside' doesn't have any parameters:
PROCEDURE FIX_STUCK_COS
-- Main procedure, called from external source
IS
TYPE GEN_CURSOR IS REF CURSOR;
v_CO_CUR GEN_CURSOR;
Please can someone just explain quickly (if that is possible) how a package differs from a 'stored procedure'?
But more importantly, can someone please tell me how I can execute the 'package'?
Thanks very much.
Best Answer
You cannot execute a PL/SQL package, as it is simply a container for one or more routines (stored procedures and functions). Typically you use packages to organize various related routines.
You execute (call) individual routines in a package by referencing them by their names, e.g.
or in your case
as both package and SP have very similar names (which is not necessarily a good idea in itself).