snippetMinor
How do I execute a PL/SQL package from my database SQL> prompt?
Viewed 0 times
packagesqldatabasehowfrompromptexecute
Problem
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.
But it fails with the following error:
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:
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.
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_COSBut 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 ignoredI 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.
Solution
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).
You execute (call) individual routines in a package by referencing them by their names, e.g.
exec PKG_NAME.SPROC_NAMEor in your case
exec C_FIX_STUCK_COS.FIX_STUCK_COSas both package and SP have very similar names (which is not necessarily a good idea in itself).
Code Snippets
exec PKG_NAME.SPROC_NAMEexec C_FIX_STUCK_COS.FIX_STUCK_COSContext
StackExchange Database Administrators Q#207279, answer score: 4
Revisions (0)
No revisions yet.