CODEWITHSUNDEEP
oracle-databaseplsql
user595234
user595234

Reputation: 6249

PL/SQL call a function outside a package

I define a function outside a package, tried to call this function, failed.

how to fix it ? thanks

create or replace
package body test_erp AS    
    procedure init_data is
    begin             
        logMessage('procedure init_data');
    end init_data;        
end test_erp;
/

show error

error is 

PLS-00221: 'LOGMESSAGE' is not a procedure or is undefined

Upvotes: 0

Views: 6950

Answers (2)

Ben
Ben

Reputation: 52843

As the error suggests logmessage is not a procedure. It's a function. As functions return something you need to assign this to a variable. You know that logmessage returns a number so you need to declare a variable to put this return value into.

create or replace package body test_erp AS    
    procedure init_data is

    l_success number;

    begin        

        l_message := logMessage('procedure init_data');
        dbms_output.put_line(to_char(l_success));

    end init_data;        

end test_erp;
/

However, it looks like logmessage should in fact be a procedure. I assume you're executing DML statements (update/insert) in this. A function call be used in a select statement unless this is the case, which means that there's always the possibility of an error occurring. If logmessage were a procedure you can declare an out parameter to tell the calling procedure whether everything worked or not; something like the following:

create or replace procedure logmessage( msg in varchar2, success out number) is

begin

   insert into logs values(msg);
   success := 1;
exception when others then
   success := 0;
end logmessage;

You can then call it as follows:

create or replace package body test_erp AS    
   procedure init_data is

       l_success number;

   begin        

      logMessage('procedure init_data', l_success);
      dbms_output.put_line(to_char(l_success));

   end init_data;        
end test_erp;
/

If logmessage isn't going to be used outside the package test_erp I would put it inside the package; it keeps the namespace cleaner and avoids it getting used mistakenly be another package / call etc.

Upvotes: 3

A.B.Cade
A.B.Cade

Reputation: 16905

Assuming that logMessage is the same function from this post:

Since logMessage is a function (and returns a number) you need to call it like this:

procedure init_data is
    i number;
begin             
    i := logMessage('procedure init_data');
end init_data;

Upvotes: 2

Related Questions