3

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 ?

4 Answers 4

3

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.

1

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.

2
  • 1
    Noticed your caveat, but this would only work if vTotalBalance is the designated OUT parameter. From the OP, it seems vAssgn_scen_site_cv is the parameter he's trying to pass in and get a return value for. So he'd have to declare this as IN OUT in x.procedurename and that, not vAssgn_scen_site_cv_i or vTotalBalance, would be the input/output. Otherwise, looks good.
    – vapcguy
    Oct 3, 2016 at 17:33
  • @vapcguy You are correct. The procedure call would be the same as shown no matter which value was being returned, but it does appear that the intention was to return vAssgn_scen_site_cv_i rather than vTotalBalance. Oct 4, 2016 at 20:48
1

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".

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.