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

Question

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.

Answer 1

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;