Re: Equivalent to UNDEFINE in PL/SQL

Karsten Farell wrote:

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

Or write it in Forms, C, C++, JavaScript, VBScript, PowerBuilder, Delphi, ColdFusion, and likely 100 other products. Take your choice.

Daniel Morgan Received on Fri Oct 11 2002 - 10:40:03 CDT