René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Undefine [SQL*Plus]

undefines a variable in SQL*Plus that was previously defined with define.
... yet to be finished ... 
set feedback off

create table t (
  name varchar2(10), 
  num  number,
  dept varchar2(10)
);

insert into t values ('John', 1, 'acc');
insert into t values ('Iain', 2, 'acc');
insert into t values ('Marc', 3, 'sal');
insert into t values ('Bob' , 4, 'it' );

undefine dept

select * from t where dept = 
case when length('&&dept') > 0 then 
  '&dept'
else 
  dept
end;

drop table t;