Re: How to return value of stored proc to command file (using sqlplus)?
Date: Fri, 6 Jun 2008 11:14:43 -0700 (PDT)
On Jun 6, 11:37 am, inkylou <amylo..._at_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
> 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
> Is there a better way to do this?
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 := 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:
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 Received on Fri Jun 06 2008 - 13:14:43 CDT