3

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).

My package displayed after installation

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.

0

2 Answers 2

4

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).

0
0

It is a function so you have to use

SELECT C_FIX_STUCK_COS.FIX_STUCK_COS() FROM DUAL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.