Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!l42g2000hsc.googlegroups.com!not-for-mail
From: "fitzjarrell@cox.net" <oratune@msn.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: How to return value of stored proc to command file (using 
 sqlplus)?
Date: Fri, 6 Jun 2008 11:14:43 -0700 (PDT)
Organization: http://groups.google.com
Lines: 34
Message-ID: <408d503d-1bde-4e61-b61e-386a9fd4b690@l42g2000hsc.googlegroups.com>
References: <88d015c6-6cf0-4956-86b9-18c6f73010b4@m73g2000hsh.googlegroups.com>
NNTP-Posting-Host: 138.32.32.166
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1212776084 23064 127.0.0.1 (6 Jun 2008 18:14:44 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 6 Jun 2008 18:14:44 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: l42g2000hsc.googlegroups.com; posting-host=138.32.32.166; 
 posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; 
 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1) ;  Embedded Web 
 Browser from: http://bsalsa.com/; .NET CLR 1.1.4322; .NET CLR 
 2.0.50727),gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.misc:252953
X-Received-Date: Fri, 06 Jun 2008 14:14:44 EDT (text.usenetserver.com)

On Jun 6, 11:37=A0am, inkylou <amylo...@gmail.com> wrote:
> Hi all! I hope this is the right forum for this question.
> I am trying to determine how to call a stored procedure from a CMD
> file, using sqlplus. My proc has two parameters, one of them an out
> parameter. I need to capture the value of the out parameter, and
> assign it to a variable in my CMD file, to use as input to another
> program.
> Currently, the only way we've found to do this is by using DBMS_OUTPUT
> to write the value to a file, then reading the file from our command
> file.
> Is there a better way to do this?
> Thanks!

Have you considered using UTL_FILE to create a dynamic CMD or BAT
file, writing the other program name and the parameter to it?
Something like:

filehandle :=3D utl_file.open('MYDIR','prog_call.bat', 'R');

utl_file.write(filehandle, '<program name> '||
returned_value_from_proc);

Then, after your PL/SQL block terminates you'd call the new .BAT file:

call c:\mydir\prog_call.bat

And you'd have your program executed with the value of your OUT
parameter and not have the need to read in the value from another
file.

It's a thought.


David Fitzjarrell