ORA-32034: unsupported use of WITH clause

Nested WITH clauses

Nested with clauses are not supported. The following statement tries to nest such clauses nevertheless which results in ORA-32034: unsupported use of WITH clause:

with
    a as (select 4   n from dual),
    b as (with c as (select n+3 n from a)
select
   * from b;

Using the WITH_PLSQL hint

When using local functions in a with clause, the with_plsql clause needs to be used to prevent ORA-32034: unsupported use of WITH clause.

We need a demonstration table:

create table tq84_t (n number);

The following statement causes ORA-32034: unsupported use of WITH clause:

insert into tq84_t
   with function f(i number) return number as begin
        return i*2;
   end f;
select
   f(level)
from
   dual connect by level <= 10;
/

The following statement uses the with_plsql hint to prevent this error:

insert /*+ with_plsql */ into tq84_t
   with function f(i number) return number as begin
        return i*2;
   end f;
select
   f(level)
from
   dual connect by level <= 10;
/

Testing the result and cleaninug up:

select * from tq84_t;

drop table tq84_t;

ORA-32034: unsupported use of WITH clause

Nested WITH clauses

Using the WITH_PLSQL hint

See also