How to get two return value from Oracle Stored Procedure

Question

I know how to get one return value from Oracle SP in Oracle as follow

MyReturn := MY_ORACLE_SP ();

If MY_ORACLE_SP2's return value is more than one. How can I do?

Answer 1

-- IN arguments : you get them. You can modify them locally but caller won't see it
-- IN OUT arguments: initialized by caller, already have a value, you can modify them and the caller will see it
-- OUT arguments: they're reinitialized by the procedure, the caller will see the final value.
CREATE PROCEDURE f (p IN NUMBER, x IN OUT NUMBER, y OUT NUMBER)
IS
BEGIN
   x:=x * p;
   y:=4 * p;
END;
/

SET SERVEROUTPUT ON

declare
   foo number := 30;
   bar number := 0;
begin
   f(5,foo,bar);
   dbms_output.put_line(foo || ' ' || bar);
end;
/

outputs: 150 20

Answer 2

What you have there is technically not a procedure, but a function -- the difference being that a procedure does not have a return value and cannot be used as the right-hand-side of an assignment statement.

You basically have two options:

(1) Use OUT parameters. In this case I would make it a procedure with two OUT parameters. Generally people don't like functions that also have OUT parameters, as it violates the usual expectations. @Benoit's answer shows this method.

(2) Define a type that contains multiple values and use this as the return type of the function. Example:

CREATE TYPE two_values AS object (
  A NUMBER,
  b number
  );
  /

CREATE FUNCTION get_two_values RETURN two_values AS
BEGIN
  RETURN two_values(2,4);
END;
/