Re: returning a value from code executed under DBMS

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Thu, 01 Apr 99 20:38:51 GMT
Message-ID: <7e0l7q$m7q$2@news.doit.wisc.edu>

In article <7e08as$cfk$1_at_news1.tc.umn.edu>, wagner_at_cs.umn.edu (Paul Wagner) wrote:
>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.

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 Received on Thu Apr 01 1999 - 14:38:51 CST

Re: returning a value from code executed under DBMS_SQL