Re: returning a value from code executed under DBMS_SQL

Thanks for the response, but this isn't what I meant. My problem is not returning a value from the outer-level subprogram (which could be a procedure or a function), but rather getting a value from the dynamically executed code block inside the program out to the subprogram itself. I think this is an issue either of scope/visibility, or of the dynamic code being executed in it's own context.

One idea I just had (sorry, I know it's bad form to start answering my own question) is to see if I can use a variable global to the package the subprogram is in - perhaps the dynamic code can see that variable. This lead to another possibility, that of using the dbms_pipe functions to communicate between the dynamic code and the subprogram. I'll try these and let everyone know if and how it works.

Paul

---

Rudy Zung <zungr_at_prograph-inc.com> wrote:
>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
>
>

--

*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
* Paul J. Wagner                School   - wagner_at_cs.umn.edu               *
* Computer Science Department   Work     - wagnerp_at_uwstout.edu             *
* University of Minnesota                                                  *
*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *