How to get return value from pl sql procedure and assign it to variable?

Question

Code:

   execute x.procedurename(row.accc_no, row.bill_no, 0, null, row.total_balance, 'A',assgn_scen_site_cv, :assgn_scen_site_cv);

Its my procedure.

How to get return value from this proc and assign it to variable ?

Answer 1

You can create it like this:

CREATE OR REPLACE PROCEDURE procedurename(param1 NUMBER, param2 varchar(20), returnvalue OUT NUMBER);
IS
BEGIN
    ... your code
END;

And then use it like this:

returnvalue NUMBER;
procedurename(0, 'xxx', returnvalue);
dbms_output.putline(returnvalue);

You can look at CREATE PROCEDURE documentation at Oracle website.

Answer 2

If I understand correctly, you are trying to consume an existing procedure that has a return value. This can be done in another procedure, package, or function, but the simplest method is using a block. In the declare section you define the variables that will receive the values and then use those in the call to the procedure.

DECLARE
   vAccc_no              Number(10);
   vBill_no              Number(10);
   vTotal_Balance        Number(10,2);
   vAssgn_scen_site_cv   Varchar(80);   
   vAssgn_scen_site_cv_i Varchar(80);
BEGIN   
   vAssgn_scen_site_cv_i := 'Pass To Procedure';
   x.procedurename(vAccc_no, vBill_no, 0, null, vTotal_Balance, 'A', vAssgn_scen_site_cv
      , vAssgn_scen_site_cv_i);
   DBMS_Output.Put_Line('New value of Total_Balance: ' || vTotalBalance);
END;
/

I'm guessing at the data types and what is input/output.

Answer 3

If you are the one who writes the procedure, you might want to make it a function instead. While "procedure" and "function" are often used as synonyms, in PL/SQL they are quite different and should be used accordingly.

If you need a program that changes something (delete content, create new structures and so on), it should be a procedure. However, if the main purpose is getting a return value (selecting data, getting results for calculations etc), it should be a function.

Functions are very easy to use in SQL (similar to calling integrated functions like to_char(), length(), instr() ...), you just write:

select my_function('foo') from dual;

The use of dual is just examplary, you can use it in any SQL statement.

Example code:

CREATE OR REPLACE FUNCTION my_function (i_input IN varchar2) RETURN varchar2
IS
BEGIN
    RETURN 'input: ' || i_input;
END;

The above call will return "input: foo".

Answer 4

Wrap your SQL into anonymous block, e.g.:

declare
    result VARCHAR2(4000);
    A_STRING VARCHAR2(4000) := '';
    A_START_POS NUMBER := 0;
    A_END_POS NUMBER := 0;
begin
    result := BETWNSTR(
        A_STRING => A_STRING,
        A_START_POS => A_START_POS,
        A_END_POS => A_END_POS
    );
    -- optional value select
    open ? for select result as result from dual;
end;

Note, that result := ... avoids context switch between PL & SQL engines if to compare with select BETWNSTR(...) into result from dual;