Get OUT parameter from stored proc in windows script

[dechrist]

In windows script, how can you get the value of the output parameter from calling a stored procedure?

Here's a test procedure I've been working with:

PROCEDURE f (p IN NUMBER, y OUT NUMBER)
IS
BEGIN
y:=4 * p;
END;

I want to get the value y into a variable in the windows script.

 

[SantaMufasa]

dechrist,

It depends (slightly) on whether you define your procedure as a database procedure or a locally defined (in-block) procedure. For example:

create or replace PROCEDURE f (p IN NUMBER, y OUT NUMBER)
 IS
 BEGIN
 y:=4 * p;
 END;
/

Procedure created.

declare
    quadrupled number;
    some_num   number;
begin
    some_num := 7;
    f (some_num,quadrupled);
    dbms_output.put_line('Some_num = '||some_num||', and that number quadrupled is '||quadrupled);
end;
/
Some_num = 7, and that number quadrupled is 28

PL/SQL procedure successfully completed.

If, instead, you define the procedure within the DECLARE section of a PL/SQL block, then it looks like this:

set serveroutput on format wrap
declare
    quadrupled number;
    some_num   number;
    PROCEDURE f (p IN NUMBER, y OUT NUMBER) IS
        BEGIN
            y:=4 * p;
        END;
begin
    some_num := 7;
    f (some_num,quadrupled);
    dbms_output.put_line('Some_num = '||some_num||', and that number quadrupled is '||quadrupled);
end;
/
Some_num = 7, and that number quadrupled is 28

PL/SQL procedure successfully completed.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[dechrist]

The procedure is already defined in the database. I don't understand how the value (named quadrupled in your example) can be accessed from a windows batch script.

I have the following a windows batch script:
sqlplus -l -s <usr>/<pswd>@SID @RunProc.sql

And RunProc.sql contains:
declare
quadrupled number;
some_num number;
begin
some_num := 7;
f (some_num,quadrupled);
dbms_output.put_line('Some_num = '||some_num||', and that number quadrupled is '||quadrupled);
end;

 

[SantaMufasa]

You can supply a command-line replacement parameter in your execution statement.

First, here's how your SQL script, "RunProc.sql" could look:

set serveroutput on format wrap
set verify off
declare
    quadrupled number;
    some_num   number;
begin
    some_num := &1;
    f (some_num,quadrupled);
    dbms_output.put_line('Some_num = '||some_num||', and that number quadrupled is '||quadrupled);
end;
/
quit

Notice in the above code that I've introduced a "&1" to the code. This means that the 1st value following the "@RunProc" will replace the "&1". (If your code had a &2, &3, et cetera, then the 2nd, 3rd, et cetera values following "@RunProc" would replace those ordinal place holders in your code during execution.)

Then here is your execution invocation and the output results of the execution:

C:\SQLDBA>sqlplus <user>/<password>@<alias> @RunProc 8

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 29 13:46:17 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Some_num = 8, and that number quadrupled is 32

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\SQLDBA>

Notice that "8" (the command-line supplied value) replaced the &1 that the code specifies.

Let us know if you have additional questions. (I may be a bit slow for the next 24 hours since my mom's funeral visitation and services are this evening and tomorrow.)

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[dechrist]

Thanks! That did help my effort.

Here's my final batch script:

sqlplus -s <usr>/<pswd>@SID @RunProc.sql> output.txt
set /p result=<output.txt
del output.txt
echo  RetVal = %result%     > log.log 2>&1

RunProc.sql

set serveroutput on format wrap
set verify off
declare
    quadrupled number;
    some_num   number;
begin
    some_num := 9;
    ogir.GIM_TEST.pRetVal(some_num,quadrupled);
    dbms_output.put_line(quadrupled);
end;
/
quit

Value in log.log is 36.


My condolences on your loss.