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

From: Ed Prochak <>
Date: Fri, 6 Jun 2008 13:45:20 -0700 (PDT)
Message-ID: <>

On Jun 6, 12:37 pm, inkylou <> 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!

write a function to call the procedure.

so if you have

PROCEDURE A_PROC( X IN type, ... Z IN type, XX OUT type, .... YY OUT othertype )

and Y is the value you care about, then create a function basically like this:

FUNCTION A_FUNC(X IN type,... Z IN type ) RETURN YY othertype
-- locals to catch other outputs from procedure A XX type; ....
  A_PROC( X, ... Z, XX, .... YY );
END A_FUNC ; Received on Fri Jun 06 2008 - 15:45:20 CDT

Original text of this message