Re: Substitution variables in Pl/sql developer

"Younes" <Essouabni_younes_at_hotmail.com> wrote in message news:aec2728c.0209230202.dfe337d_at_posting.google.com...
> Hi everybody,
> I'm using the tool named PL/sql developer that you can find at
> http://www.allroundautomations.com/plsqldev.html
> I'm trying to use Substitution variable with that tool, but without
> succes.
> Can you tell me what is wrong in the following code?
>
> [code]
> create or replace procedure P_Exception is
> exception_name exception;
> v_num number:=&1;
> cursor curseur is select empno from emp where empno=v_num;
> v_empno errorlog.empno%type;
>
> begin
>
> open curseur;
> fetch curseur into v_empno;
> if (curseur%found)
> then
> dbms_output.put_line('found');
> else
> dbms_output.put_line('Exception raised');
> raise exception_name;
> end if;
> close curseur;
> exception
> When Exception_name
> then
> dbms_output.put_line('Handling exception');
> insert into errorlog(empno,Code_Erreur) values (v_num,'not found');
> dbms_output.put_line('inserting into table ErrorLog');
> end P_Exception;
>
>
> [/code]
>
>
> Thx in advance for your help.

The v_num would be treated as a constant. When the procedure is compiled the value for &1 becomes a status constant value. It will be stored inside the database with the procedure. The next time you run the procedure only the same v_num is used over and over. See Daniel's reply for options.

Kevin Received on Mon Sep 23 2002 - 21:14:02 CEST