This is a somewhat obscure bug so you're most unlikely to encounter it...UNLESS you are using the Oracle Designer product. The PL/SQL engine in 8.1.7 struggles with SQL's that have include where clauses of the type
where ( x, y ) in ( ( func(a), p ), ( func(b), q ), ( func(c), r ), ( func(d), s ) )
The key ingredients here are:
PL/SQL will spin endlessly (easily detectable by the smoke coming from your CPU) when trying to run OR compile such code. Unfortunately, Designer 6 and Designer 6i both have such code (scheduled to be fixed in their next patch versions respectively). An example of the error is easy to reproduce in SQL Plus
SQL> variable l1 varchar2(10) SQL> variable l2 varchar2(10) SQL> variable l3 varchar2(10) SQL> variable l4 varchar2(10) SQL> create table demo1 ( x varchar2(10), y varchar2(10)); Table created. SQL> begin 2 delete from demo1 3 where ( x,y) in ( 4 ( upper(:l1), 'x' ), 5 ( upper(:l2), 'x' ), 6 ( upper(:l3), 'x' ), 7 ( upper(:l4), 'x' ) 8 ); 9 end; 10 / (hang...)Because it is a PL/SQL and not a SQL error, the workaround (without totally rewriting the code) is to wrap with "execute immediate", for example,
SQL> begin 2 execute immediate 'delete from demo1 3 where ( x,y) in ( 4 ( upper(:b1), ''x'' ), 5 ( upper(:b2), ''x'' ), 6 ( upper(:b3), ''x'' ), 7 ( upper(:b4), ''x'' ) 8 )' using :l1, :l2, :l3, :l4; 9 end; 10 / PL/SQL completed successfully.