CODEWITHSUNDEEP
oracle-databaseplsql
user2788235
user2788235

Reputation: 31

Package in Oracle (facing error while calling it)

I am using Oracle 11G database. I have created a package as described below:

create or replace package forward_emp is    
  Function emp_sal_avg return number;   
  Procedure emp_new_sal;    
End forward_emp;    
/

package created.

Then I created the package body:

create or replace package body forward_emp is
  Function emp_sal_avg return number is 
    avg_sal emp.salary%type;
  Begin
    Select avg (salary) into avg_sal 
     from emp;
    Return avg_sal;
  End  emp_sal_avg;
  Procedure emp_new_sal is 
  Begin
    Insert into Emp (salary) values (emp_sal_avg);
  End emp_new_sal;
End forward_emp;
/

Package body created.

Now when I tried calling the package its is showing error.

Call forward_emp.emp_new_sal;
ORA- 06576: not a valid function or procedure.

Please help. Not been able to understand the problem.

Upvotes: 1

Views: 362

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

CALL is a SQL statement, not a PL/SQL statement, not a SQLPLUS command. CALL can be used in SQLPLUS directly, but, when you execute a function, the returning result should be stored somewhere, hence into clause of the call statement is needed. And parenthesis, even if a function or a stored procedure has no arguments, kind of mandatory.

Here is an example:

SQL> create or replace package pkg as
  2    function f1 return number;
  3    procedure p1;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg as
  2    function f1 return number is
  3    begin
  4     return 12345;
  5    end;
  6    procedure p1 is
  7    begin
  8      null; -- does nothing
  9    end;
 10  end;
 11  /

Package body created.

Now let's execute those procedure and function defined withing the PKG package:

-- variable, which is going to store result the function returns
SQL> variable f_res number;

-- executing the F1 function
SQL> call pkg.f1() into :f_res;

Call completed.

-- print the result
SQL> print f_res;

     F_RES                                                                      
----------                                                                      
     12345                                                                      

-- executing the P1 procedure
SQL> call pkg.p1();

Call completed.

This what happens if we simply omit parenthesis or do not specify into clause when executing a stored procedure or a type method using CALL statement:

SQL> call pkg.p1;
call pkg.p1
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name 

SQL> call pkg.f1 into :f_res;
call pkg.f1 into :f_res
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name 

SQL> call pkg.f1();
call pkg.f1()
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

Upvotes: 1

TenG
TenG

Reputation: 4004

I think you're trying to use CALL as a SQL*Plus command. As AHWNN pointed out, in sqlplus you would use EXECUTE to run the procedure. However you could in SQL test with CALL by using an anonymous block:

BEGIN
CALL the_pack.the_proc;
END;
/

Upvotes: 0

Related Questions