drop table t;

Table dropped.

drop table ff;

Table dropped.

create table t(id, name) as select rownum, 'name' from dual;

Table created.

create table ff as select 1 id, 1 sign from dual;

Table created.

create unique index idx on ff (id, case  when sign is null then 1 else 0 end );

Index created.


select distinct v2.dt
  from    (select v1.*,
                  (select max(1)
                     from t
                    where v1.m = t.id)
                     x
             from (select date '1010-10-10' dt,
                          b_id,
                          (select max(1) from dual) m,
                          ff.*
                     from (select 1 a_id, 1 c_id from dual) a
                          join (select 1 a_id, 777 b_id from dual) b
                             on b.a_id = a.a_id
                          left join ff
                             on ff.id = a.c_id) v1) v2
       join
          t
       on v2.x = t.id
 where b_id = 777;
                  (select max(1)
                         *
ERROR at line 3:
ORA-03113: end-of-file on communication channel
Process ID: 10949
Session ID: 681 Serial number: 41007

create table t as select 'ZZZ' value from dual;

SQL> select *
  2    from (select distinct x.*
  3            from xmltable('/A' passing xmltype('<A><B>ZZZ</B></A>') columns b varchar2(255) path 'B') x
  4           where rownum >= 1
  5         ) x1,
  6         t
  7   where t.value = x1.b;

B     VAL
----- ---
ZZZ   ZZZ

SQL> select *
  2    from (select distinct x.*
  3            from xmltable('/A' passing xmltype('<A><B>ZZZ</B></A>') columns b varchar2(255) path 'B') x
  4         --where rownum >= 1
  5         ) x1,
  6         t
  7   where t.value = x1.b;
select *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel