How to call an Oracle procedure within a package?

2
votes

I'm trying to call an Oracle stored procedure within a package and I'm getting this error: SQL Error: ORA-06576: not a valid function or procedure name

I'm using SQL Developer and this is the command I'm using

call WEATHERDATAUPDATES.GetLastRunDate("WeatherData")

Here is the package/procedure

PACKAGE BODY WEATHERDATAUPDATES AS

  PROCEDURE GetLastRunDate(PROCESS IN VARCHAR2, RUNDATE OUT DATE) AS
  BEGIN
    SELECT rundate FROM Marcie.last_rundate
    where process = PROCESS;
  END GetLastRunDate;

END WEATHERDATAUPDATES;

I'm pretty new to Oracle Packages and not sure what I'm missing. I tried searching, but can't find an answer that works. Can someone tell me what I'm missing?

Thanks, Marcie

3
oracleplsqlpackages

3 Answers

3
votes

In your procedure you are not putting the retrieved value anywhere, you should use INTO:

...    
   PROCEDURE GetLastRunDate(PROCESS IN VARCHAR2, RUNDATE OUT DATE) AS
   BEGIN
     SELECT rundate 
     INTO RUNDATE 
     FROM Marcie.last_rundate  
     where process = PROCESS;
   END GetLastRunDate;
...

In the call pass the variable for the out parameter RUNDATE OUT DATE.

Put the call in a PL/SQL block:

DECLARE
    lastRunDate DATE;
BEGIN
    WEATHERDATAUPDATES.GetLastRunDate("WeatherData",lastRunDate);   
    -- do something with lastRunDate
END;
3
votes

GetLastRunDate has 2 parameters, (process and rundate), but you're only passing 1 in your call.

Since you want to pass the second parameter out, one option would be to make it function and return the rundate.

PACKAGE BODY WEATHERDATAUPDATES AS

  FUNCTION GetLastRunDate(PROCESS IN VARCHAR2)
  RETURN DATE
  AS
     lDate DATE;
  BEGIN
    SELECT rundate
    INTO   lDate
    FROM   Marcie.last_rundate
    where  process = PROCESS;

    RETURN lDate;
  END GetLastRunDate;

END WEATHERDATAUPDATES;
2
votes

You have shown the BODY of your package, but not the specification - check that GetLastRunDate is declared in the package spec.