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
LVL 6
JohnDesautelsAsked:
Who is Participating?
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.