Re: How to return value of stored proc to command file (using sqlplus)?

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
> 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 := 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 Received on Fri Jun 06 2008 - 13:14:43 CDT