Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: returning a value from code executed under DBMS_SQL

Re: returning a value from code executed under DBMS_SQL

From: Rudy Zung <zungr_at_prograph-inc.com>
Date: Thu, 1 Apr 1999 09:42:04 -0500
Message-ID: <7e07nk$4fu$1@mailhost.prograph-inc.com>

Paul Wagner wrote in message
>I would like to "return" a value from some PL/SQL code executed
dynamically
>using DBMS_SQL to a variable in the procedure containing the DBMS_SQL
>calls. The following works fine (cleanly runs to the end of the
>procedure):
>
>PROCEDURE example IS
> global_var INTEGER;
> block_string VARCHAR;
> cursor_handle INTEGER;
> cursor_result INTEGER;
>BEGIN

  [...deleted code snippet...]

>END;
>
>But, what I really need is to somehow pass or connect the value of X
>(in the dynamic code) back to global_var in the procedure. When I
try
>replacing the X := 1 line with something like global_var := X, the
>procedure executes cleanly to that point but completes right there
>without an apparent error message and without continuing to the end
>of the procedure.

What you want (if I interpret your question correctly) is to either create a function rather than a procedure; look under documentation for CREATE [or replace] FUNCTION ...

The other way of doing what you want is to create a procedure that takes either an IN OUT parameter or an OUT parameter. Oracle treats OUT parameters as purely write only parameters, and the procedure body is disallowed from reading from the parameter, so IN OUT might be appropriate in some situations where you would want r/w access on the parameter. An IN OUT basically works as a pass by reference (a VAR parameter in Pascal terms, or an & parameter in C++.)

...Ru Received on Thu Apr 01 1999 - 08:42:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US