Re: Ampersand in PL/SQL

In <D70Hpp.904_at_mail.auburn.edu> moorthy_at_eng.auburn.edu (Moorthy) writes:

>
>Hello There,
>
> I typed the following PL/SQL block in a file.
>
>declare
> zzz number(5);
> aaa number(5);
>begin
> select sal into zzz from emp where empno = &aaa;
> if (zzz > 2000) then
> update emp set sal = zzz + 10 where empno = aaa;
> else
> update emp set sal = zzz + 500 where empno = aaa;
> end if;
> commit;
>end;
>/
>
> At the SQL prompt, I started the file.
>
>SQL> start vm_12.sql;
>Enter value for aaa: 7369
>old 5: select sal into zzz from emp where empno = &aaa;
>new 5: select sal into zzz from emp where empno = 7369;
>
>PL/SQL procedure successfully completed.
>
>SQL> select sal, empno from emp;
>
> SAL EMPNO
>---------- ----------
> 800 7369
> 1600 7499
> 1250 7521
> 2975 7566
> 1250 7654
> 2850 7698
> 2450 7782
> 3000 7788
> 5000 7839
> 1500 7844
> 1100 7876
>
> SAL EMPNO
>---------- ----------
> 950 7900
> 3000 7902
> 1300 7934
>
>14 rows selected.
>
> Salary(800) for empno 7369 is not changed. Is ampersand allowed
in PL/SQL ? If so, why the salary is not being updated. When I invoke sqlplus, the following information is displayed on my screen.
>
>_____________________________
>
>Command Prompt: sqlplus /
>
>SQL*Plus: Version 3.0.11.1.2 - Production on Fri Apr 14 00:07:56 1995
>
>Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.
>
>
>Connected to:
>ORACLE RDBMS V6.0.36.0.1, transaction processing option - Production
>PL/SQL V1.0.34.2.1 - Production
>_____________________________
>
> Thanks very much for your help.
>
>Moorthy
>(moorthy_at_eng.auburn.edu)
>
>:=) Do unto others as you would wish to be done by others. :=)
>
>
>
The problem lies in the concept of a programme variable and the PL/SQL substitution variables.
It should have worked correctly but for one mistake that you made. If you want to use substitution variables, they need not be declared. Just any label would suffice with an & before it. The substitution variables are just a text substitution facility and no values is stored on any of the progamme variable.
You have declared the variable aaa and used the & infront of it on the first select statement. PL/SQL correctly asked you for a substitution value at runtime and you supplied it. The select must have gone through as you had expected. But PL/SQL won't store what you entered inot the variable aaa as you had expected. The update statement just uses aaa and without an & infront of it. So PL/SQL did not prompt you for a value but just used the unintialized programme variable aaa.

Try this code, it should work.

declare

   zzz number(5);
begin

   select sal into zzz from emp where empno = &&aaa;    if (zzz > 2000) then

      update emp set sal = zzz + 10 where empno = &aaa;    else

      update emp set sal = zzz + 500 where empno = &aaa;    end if;
   commit;
end;

Hope this solves your problem.

Chakra. Received on Fri Apr 14 1995 - 00:00:00 CEST