returning a value from code executed under DBMS_SQL

From: Paul Wagner <wagner_at_cs.umn.edu>
Date: 1 Apr 1999 04:04:48 GMT
Message-ID: <7dur90$7i8$1@news1.tc.umn.edu>

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
  ...

  block_string := 'DECLARE ' ||
                  ' X INTEGER := 3; ' ||
                  'BEGIN ' ||
                  ' X := 1; ' ||
                  'END;' ;

• run it through dbms_sql cursor_handle := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cursor_handle, block_string, DBMS_SQL.NATIVE); cursor_result := DBMS_SQL.EXECUTE(cursor_handle); DBMS_SQL.CLOSE_CURSOR(cursor_handle); ... 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.

Any ideas on how I can get a value back out of the dynamic code?

Thanks much for any help and/or ideas,

Paul

--
* * * * * * * * * * * * * * * *

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

Received on Wed Mar 31 1999 - 22:04:48 CST