Re: Ampersand in PL/SQL

Thomas Dunbar <tdunbar_at_gserver.grads.vt.edu> writes:
|> 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;
|>
|> yes, that works ok..the first time..when called again, tho,
|> it does not prompt for value but reuses what was given before!
|>

A couple of other approaches:

1. Change the PL/SQL block into a stored procedure (just needs to be run once). Then the script simply becomes execute myproc(&aaa);
2. Use PL/SQL variable instead of a SQL*Plus specific variable in the PL/SQL block. Basically it goes something like this:

   variable aaa number;
   begin :aaa := &aaa; end;
   /
   [Run PL/SQL block with :aaa replacing all the instances of &aaa in    the original example]

The second one will probably have the same problem that you will have to rerun the whole script rather than just reexecute the PL/SQL block. It's important to distinguish PL/SQL variables from SQL*Plus text substitution variables. Text substitution variables will likely lead to a shared pool full of unique (unsharable) cursors.

Hope this helps.
Roderick Manalac
Oracle Corporation Received on Sat Apr 15 1995 - 00:00:00 CEST