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


You can create it like this:

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

And then use it like this:

returnvalue NUMBER;
procedurename(0, 'xxx', returnvalue);

You can look at CREATE PROCEDURE documentation at Oracle website.


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.

   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);
   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);

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

  • 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
    Commented 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. Commented Oct 4, 2016 at 20:48

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
    RETURN 'input: ' || i_input;

The above call will return "input: foo".


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

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

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 and acknowledge you have read our privacy policy.

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