Re: Substitution variables in Pl/sql developer

Younes wrote:

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

If I understand what you are trying to do ... you can't.

Input parameters to a stored procedure can only be entered as follows:

CREATE OR REPLACE PROCEDURE procedure P_Exception (

   v_num NUMBER)
IS

   ...
BEGIN
   ...
END;
/

Daniel Morgan Received on Mon Sep 23 2002 - 17:23:53 CEST