Re: Equivalent to UNDEFINE in PL/SQL

Boris Goldshmidt wrote:
> Is there an equivalent to "UNDEFINE" command in PL/SQL? In the
> example below, I am trying to update col1 based on user input. The
> issue is that, variables i1 and i2 will not prompt the user for input
> after the the first pass. How can I undefine these variables and
> prompt the user for new input inside the loop? Thanks for your help.
>
>
> UNDEFINE i1 i2
> DECLARE v1 NUMBER :=0;
> v2 NUMBER :=0;
> cnt NUMBER :=0;
> BEGIN
> LOOP
> cnt := cnt+1;
> v1 := &i1;
> v2 := &i2;
> UPDATE ex1
> SET col1=v1
> WHERE col2=v2;
> EXIT WHEN cnt=12;
> END LOOP;
> COMMIT;
> END;
> /

Unfortunately, the ampersand is a sqlplus, not a pl/sql, construct. Once you get inside the loop in pl/sql, you can't 'undefine' your sqlplus variables. And just as pl/sql is pretty sparse on the display side (other than dbms_output), it's really sparse on the input side.

I tried to think of a way you could do looping in sqlplus, but I guess that's one of the reasons they invented pl/sql. You could accept your 24 variables (2 variables x 12 loops) in sqlplus and then inside your pl/sql loop, do something like:

update ex1 set col1=&c1 where col2=&w1;
update ex1 set col1=&c2 where col2=&w2;
...
update ex1 set col1=&c12 where col2=&w12;

But that's a really tacky way to do it. You could also write it in Java, which has a rich set of input/output capabilities. Received on Thu Oct 10 2002 - 19:15:38 CDT