PL/SQL bug in 8.1.7

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:

A vector comparison, that is, more than one item in the IN list "(x,y)"
Some expression on the comparatives "func(a)"
At least 4 comparatives

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.