HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How do I execute a PL/SQL package from my database SQL> prompt?

Submitted by: @import:stackexchange-dba··
0
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.

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.

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.

exec PKG_NAME.SPROC_NAME


or in your case

exec C_FIX_STUCK_COS.FIX_STUCK_COS


as both package and SP have very similar names (which is not necessarily a good idea in itself).

Code Snippets

exec PKG_NAME.SPROC_NAME
exec C_FIX_STUCK_COS.FIX_STUCK_COS

Context

StackExchange Database Administrators Q#207279, answer score: 4

Revisions (0)

No revisions yet.