Re: executing parsed PLSQL block statement at a time

Gregory S. Lipman wrote:
>
> Hi there
> What i am doing is parsing PLSQL block or
> body of stored procedure and executing it one
> statement at a time in PB4 dynamic SQL.
> Of course statements from such a block do not live in
> vacuum, the may reference input params and local vars,
> so basically i have to keep them alive during a life of
> connection. In SQL*Plus i try in the begining of session
> to DECLARE a variable BUT it would not accept it as a
> separate statement. PowerBuilder also returns error if you send
> only DECLARE var .. alone.
> Question : Is there such a thing in ORACLE as variable for a
> session, not just block.
> Question : Does dynamic SQL in PB allow to send PLSQL block in
> one stroke.
> Thank, Gregory

ORACLE PL/SQL supports persistent variables which can hold values for a life of oracle session. These variables can be created by defining them in package specification.

For example:
create package my_variables as
  keep_it_for_me varchar2(500);
end;
/

Above package specification holds definition of variable keep_to_for_me;

Now, in sqlplus you can do the following;

SQL> begin

        my_variables.keep_it_for_me := 'HELLO THERE';
     end;

Any other pl/sql block, procedure, function can now access this value throughout the session.
For example:
SQL> declare

          dummy := varchar2(500);
      begin
         dummy := my_variables.keep_it_for_me;
      end;

PL/SQL procedure successfully completed.

HTH SANJAY D. S.
Oracle Consultant
Prudential Securities, Inc. Received on Mon Jun 24 1996 - 00:00:00 CEST