We help IT Professionals succeed at work.

How to stop a oracle procedure mid swing...

I have this in a procedure:

if input1 is null and input2 is not null
Do this:
else if input1 is not null and input2 is null
Do this:
else return;
end if;

This errors at the end of my procedure saying "encountered symbol <name of proc> ... "
What do I do if I want to exit out of the procedure if in else condition?
Thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Try elsif instead of else if:

...
elsif input1
...
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
That didn't work...
what else you got?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Please post a complete test procedure.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
On mobile and cannot confirm but try exit instead of return?

What is the exact error?
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
IF (field1 IS NOT NULL AND field2 IS NULL) THEN
 
      Select field3, field4, field2 INTO <variables>  FROM ACTVTY WHERE field1 = field1;
     
  ELSE if pbsa_id is null and pdcn is not null then
 
      Select field3, field4, field1 INTO <variables>  FROM ACTVTY WHERE field2 = field2;
     
  else return;
     
  END IF;
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
it works if I remove the else statement and make the Else if a else statement.
error says: encountered the symbol "name of stored procedure" when expecting one of the following: if
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
sorry, let me adjust:
IF (input1 IS NOT NULL AND input2 IS NULL) THEN
 
      Select field3, field4, field2 INTO <variables>  FROM ACTVTY WHERE field1 = input1;
     
  ELSE if input1 is null and input2 is not null then
 
      Select field3, field4, field1 INTO <variables>  FROM ACTVTY WHERE field2 = input2;
     
  else return;
     
  END IF;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
That still has 'else if' and is not a complete procedure test case.

I"m looking for sample code that starts with 'create or replace procedure' and ends with 'end;' that I can reproduce on my end.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
SQL DEVELOPER just doesn't like ELSEIF, it barks at me.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
Please forgive, I am not allowed to show any further.
it's a privacy thing.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
input1 and input2 are inputs into the stored procedure.

basically, if one of these two conditions are met, more stuff follows....
if not, report null and stop.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Here is what I mean by a stand-alone test case.

I ran this using sqlplus on 10.2.0.3.

Results follow.

Notice the 3rd call did not 'make it to the end'.
create or replace procedure myproc(invar in char)
is
begin
	if invar = 'a' then
		dbms_output.put_line('Got a a');
	elsif invar='b' then
		dbms_output.put_line('Got a b');
	else
		dbms_output.put_line('Got something else');
		return;
	end if;
	dbms_output.put_line('made it to the end!');
end;
/

show errors

set serveroutput on
exec myproc('a');
exec myproc('b');
exec myproc('c');

Open in new window

Procedure created.

No errors.

Got a a
made it to the end!

PL/SQL procedure successfully completed.

Got a b
made it to the end!

PL/SQL procedure successfully completed.

Got something else

PL/SQL procedure successfully completed.

SQL>

Open in new window

Evan CutlerVolunteer Chief Information Officer

Author

Commented:
I'll take it...
Thanks.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>just doesn't like ELSEIF, it barks at me.

Sorry.  Didn't notice the typo.  It was elsif not elseif.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.