Re: returning a value from code executed under DBMS_SQL

This is exactly what I needed - thanks to Gerard and to Rudy Zung, who also pointed me in this direction.

Paul

Gerard M. Averill <e-mail.address_at_my.sig> wrote:
>The idea of using a package variable would work, but is not necessary. By
>using a bind parameter you can retrieve the value of an expression in the
>dynamic block:
>
>PROCEDURE example IS
> global_var INTEGER;
> block_string VARCHAR;
> cursor_handle INTEGER;
> cursor_result INTEGER;
>BEGIN
> ...
> block_string := 'DECLARE ' ||
> ' X INTEGER := 3; ' ||
> 'BEGIN ' ||
> ' X := 1; ' ||
> ' :result := X; ' || -- Added this
> 'END;' ;
> -- run it through dbms_sql
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_handle, block_string, DBMS_SQL.NATIVE); -- Added this
> DBMS_SQL.BIND_VARIABLE(cursor_handle 'result', 0);
> cursor_result := DBMS_SQL.EXECUTE(cursor_handle);
> DBMS_SQL.VARIABLE_VALUE(cursor_handle, 'result', global_var); -- Added this
> DBMS_SQL.CLOSE_CURSOR(cursor_handle);
> ...
>END;
>
>Hope this helps.
>Gerard
>
>----
>Gerard M. Averill, Researcher
>CHSRA, University of Wisconsin - Madison
>GAverill<at>chsra<dot>wisc<dot>edu

--

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