stored-proceduresplsqloracle-sqldeveloperstored-functionspls-00103

How to call a stored function from a stored procedure and store the return value


When I do this in my stored procedure:

   create procedure Proc1(
       startdate IN TIMESTAMP,
       ENDDATE IN TIMESTAMP
   )
   declare test_result number --line 55
    test_result:=Stored_function1(startdate,enddate,11,13); --line 56
END;

SQL Developer throws 2 errors:

PLS-00103: Encountered the symbol "TEST_RESULT" when expecting one of the following: := . ( @ % ; not null range default character The symbol "." was substituted for "TEST_RESULT" to continue.

PLS-00103: Encountered the symbol "END" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior

Stored_function1 is user defined, takes 4 parameters, and does not belong to any package. Where did I do wrong and how do I correct it? Thanks.


Solution

  • Without seeing more of it it's hard to tell, but it seems you have some syntax error(s) in your procedure. No DECLARE is needed, there should be a semi-colon at the end of line 55 and a BEGIN before line 56 at the least.

    Here's a basic skeleton:

    Create or replace procedure my_procedure as
      test_result number;
    BEGIN
      test_result := Stored_function1(startdate, enddate, 11, 13); 
    END;