INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get OUT parameter from stored proc in windows script

Get OUT parameter from stored proc in windows script

(OP)
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.

RE: Get OUT parameter from stored proc in windows script

dechrist,

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

CODE

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:

CODE

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. 

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

RE: Get OUT parameter from stored proc in windows script

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

RE: Get OUT parameter from stored proc in windows script

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

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

CODE

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:

CODE

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

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

RE: Get OUT parameter from stored proc in windows script

(OP)
Thanks! That did help my effort.

Here's my final batch script:

CODE

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close