Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5694
  • Last Modified:

Execute Oracle Stored Procedure with Output Parameter

Disclaimer - I am a SQL Server guy trying to modify a couple of Oracle stored procedures. I know enough to be dangerous, but not enough to be productive!

The goal is to add a return value after the stored procedure has run. Seems simple enough.

Here is a mockup of the original code:

      CREATE OR REPLACE PROCEDURE proc_test_01 (
               vAsOfDate IN DATE := Trunc(SYSDATE-1)
           )
      IS
      
      BEGIN
      
          /* Lots of stuff happening in real procedure */
      
          INSERT INTO   MFP_Proc_Log(ProcName, AsOfDate)
      
          SELECT 'proc_test_01' procname,
                  vAsOfDate asofdate
      
          FROM  DUAL;
      
      END;
      
I can execute it from SQL Tools (something like Query Analyzer) using the following command:

EXEC proc_test_01 ('01-May-2009')

I created a variant of this procedure that uses an Output parameter.

      CREATE OR REPLACE PROCEDURE proc_test_02 (
               viStatus OUT NUMBER,
               vAsOfDate IN DATE := Trunc(SYSDATE-1)
           )
      IS
      
      BEGIN
      
          /* Lots of stuff happening in real procedure */
      
          INSERT INTO   MFP_Proc_Log(ProcName, AsOfDate)
      
          SELECT 'proc_test_02' procname,
                  vAsOfDate asofdate
      
          FROM  DUAL;
      
          /* Set the output parameter */
          viStatus := 1;
      
      END;

Now, how do I execute this from SQL Tools to test the procedure and display the output?

I have tried lots of permuations of code I found on the web. None of them worked. No doubt is amazingly simple.

Thanks,

John
0
JohnDesautels
Asked:
JohnDesautels
2 Solutions
 
sdstuberCommented:
different tools have different ways but all should support a generic pl/sql block


declare
v_output number;
begin
proc_test_02 (
               viStatus => v_output,
               vAsOfDate => to_date('01-May-2009','DD-Mon-YYYY')
);
dbms_output.put_line(v_output);
end;
0
 
johnsoneSenior Oracle DBACommented:
In SQL*Plus, I would do it this way:

variable stat number;
begin
  proc_test_02(:stat, sysdate);
end;
/
print stat
0
 
MilleniumaireCommented:
You create an anonymous pl/sql block:

declare
   v_status NUMBER;
begin
  proc_test_01 ('01-May-2009');
  proc_test_02 (v_status, '01-May-2009');

  dbms_output.enable;  
  dbms_output.put_line('v_status = '||v_status);
end;
/
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JohnDesautelsAuthor Commented:
Thanks for the rapid responses!

Solutions from sdstuber and Milleniumaire both worked with SQL Tools.
0
 
sdstuberCommented:
glad we could help.

if you use sql*plus, johnsone's post should work as well
0
 
johnsoneSenior Oracle DBACommented:
SQL*Plus is the lowest common denominator.  When all else fails, that should be the tool that everyone has.

I throw that one out there because most people don't think of it.  That is the way it should be called in code, so it simulates the code better.  It doesn't make much difference on an out parameter though.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now