Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Oleg.

Asked: December 08, 2016 - 6:42 pm UTC

Last updated: December 09, 2016 - 3:40 pm UTC

Version: Oracle 12

Viewed 1000+ times

You Asked

Hi Tom,
I have a stored procedure where I have to check values of several parameters and if they are empty I have to return -1. Oracle cannot use return statement. I cannot use function because it return control to stored procedure. If I use pragma, can it exit with status -1? And how exactly I can do it. I couldn't find any good example of such pragma

Thanks,
Oleg

and Chris said...

If you want to return a value, you have to use a function!

You can put a return in a procedure to stop processing. But there won't be a value associated with it:

create or replace procedure p(p1 int, p2 int, p3 int) is
begin
  dbms_output.put_line('started');
  if coalesce(p1, p2, p3) is null then
    return;
  end if;
  dbms_output.put_line('stopped');
end p;
/

exec p(null, null, null);

PL/SQL procedure successfully completed.
started


I don't understand what the issue with a function is. Both will "return control to stored procedure".

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Out

A reader, December 09, 2016 - 10:51 am UTC

(1) Use a function.
(2) You can of course use an out parameter in conjunction with the above.

create or replace procedure p(p1 int, p2 int, p3 int, res out nocopy int) is
begin
  dbms_output.put_line('started');
  if coalesce(p1, p2, p3) is null then
    res := -1;
    return;
  end if;
  dbms_output.put_line('stopped');
end p;
/

declare
  res int;
begin
  p(null,null,null, res);
  dbms_output.put_line('Valid of res is ' || res);
end;

started
Valid of res is -1


Chris Saxon
December 09, 2016 - 2:40 pm UTC

Excellent point. Out parameters do allow you to return values from procedures.

Exit from stored procedure

Oleg, December 09, 2016 - 12:03 pm UTC

I have to not just return. I have to return certain number, for example return -1 from stored procedure. Is it possible?
Chris Saxon
December 09, 2016 - 2:42 pm UTC

I'm not sure what the issue with functions is. But if you must use a procedure, out parameters can "return" a value from a procedure. See the above comment for an example.

Additonal Comments to what has been mentioned already.

David Pulliam, December 09, 2016 - 3:26 pm UTC

What about using exception handling instead? What I will do many times is use user defined exceptions if there is a limited number of of outcomes when I want the program to come to a halt and I need to send the results back up several levels. I believe, correct me if I am wrong, that the default action on an exception is to raise it if not handled. I agree with Chris here that in your case, a function would be the way to go. You actually have to tell the function what to return and it always has to return a value. If you hit your bad case, return -1 and your done. If you cannot use a function, then you might need to reconsider how your process is designed. Out Parameters are a good option as already mentioned especially for cases where you need to return multiple values as a function can only return either a single variable value or a static hard coded value.
Chris Saxon
December 09, 2016 - 3:40 pm UTC

Yes, exception handling is another way to deal with this.

In this case I think it's better than returning -1 as it represents an invalid scenario. You can only have customer errors in the range -20000 to -20999 though.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library