Oracle 26ai

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Source - https://stackoverflow.com/q/80007355
-- Posted by Schrei, modified by community. See post 'Timeline' for change history
-- Retrieved 2026-10-01, License - CC BY-SA 4.0

CREATE OR REPLACE FORCE EDITIONABLE VIEW view_abcd (DUMMY) AS
SELECT 1 as DUMMY
FROM dual a
OUTER APPLY (
    SELECT *
    FROM dual b
    JOIN dual c
    ON c.DUMMY = (
        SELECT d.DUMMY
        FROM dual d
        WHERE d.DUMMY = a.DUMMY
        FETCH FIRST 1 ROW ONLY
     )
);
variable x clob;
set autoprint on long 1000 pagesize 1000
exec dbms_utility.expand_sql_text ( input_sql_text  => 'SELECT 1 AS one_row FROM dual WHERE   EXISTS (SELECT * FROM view_abcd) AND NOT EXISTS (SELECT * FROM view_abcd)', output_sql_text => :x )

PL/SQL procedure successfully completed.


X
--------------------------------------------------------------------------------
SELECT 1 "ONE_ROW" FROM "SYS"."DUAL" "A1" WHERE  EXISTS (SELECT 0 FROM	(SELECT
1 "DUMMY" FROM	(SELECT "A14"."DUMMY" "DUMMY","A13"."QCSJ_C000000000800000_1" "Q
CSJ_C000000000800000","A13"."QCSJ_C000000000800001_0" "QCSJ_C000000000800001" FR
OM "SYS"."DUAL" "A14", LATERAL( (SELECT "A15"."QCSJ_C000000000800001_1" "QCSJ_C0
00000000800001_0","A15"."QCSJ_C000000000800000_0" "QCSJ_C000000000800000_1" FROM
  LATERAL( (SELECT "A16"."QCSJ_C000000000800000_0" "QCSJ_C000000000800000_0","A1
6"."QCSJ_C000000000800001_1" "QCSJ_C000000000800001_1" FROM  (SELECT "A18"."DUMM
Y" "QCSJ_C000000000800000_0","A17"."DUMMY" "QCSJ_C000000000800001_1" FROM "SYS".
"DUAL" "A18","SYS"."DUAL" "A17" WHERE "A17"."DUMMY"= (SELECT "A20"."DUMMY" "DUMM
Y" FROM  (SELECT "A19"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "A19" WHERE "A19"."DUMM
Y"="A14"."DUMMY") "A20" WHERE ROWNUM<=1)) "A16")) "A15" WHERE 1=1))(+) "A13") "A
12") "A3") AND	NOT EXISTS (SELECT 0 FROM  (SELECT 1 "DUMMY" FROM  (SELECT "A6".
"DUMMY" "DUMMY","A5"."QCSJ_C000000001700



ALTER SESSION SET "_fix_control" = '35915968:0';
variable x clob;
set autoprint on long 1000 pagesize 1000
exec dbms_utility.expand_sql_text ( input_sql_text  => 'SELECT 1 AS one_row FROM dual WHERE   EXISTS (SELECT * FROM view_abcd) AND NOT EXISTS (SELECT * FROM view_abcd)', output_sql_text => :x )

Session altered.


PL/SQL procedure successfully completed.


X
--------------------------------------------------------------------------------
SELECT 1 "ONE_ROW" FROM "SYS"."DUAL" "A1" WHERE  EXISTS (SELECT 0 FROM	(SELECT
1 "DUMMY" FROM	(SELECT "A14"."DUMMY" "DUMMY","A13"."QCSJ_C000000000800000_1" "Q
CSJ_C000000000800000","A13"."QCSJ_C000000000800001_0" "QCSJ_C000000000800001" FR
OM "SYS"."DUAL" "A14", LATERAL( (SELECT "A15"."QCSJ_C000000000800001_1" "QCSJ_C0
00000000800001_0","A15"."QCSJ_C000000000800000_0" "QCSJ_C000000000800000_1" FROM
  LATERAL( (SELECT "A16"."QCSJ_C000000000800000_0" "QCSJ_C000000000800000_0","A1
6"."QCSJ_C000000000800001_1" "QCSJ_C000000000800001_1" FROM  (SELECT "A18"."DUMM
Y" "QCSJ_C000000000800000_0","A17"."DUMMY" "QCSJ_C000000000800001_1" FROM "SYS".
"DUAL" "A18","SYS"."DUAL" "A17" WHERE "A17"."DUMMY"= (SELECT "A20"."DUMMY" "DUMM
Y" FROM  (SELECT "A19"."DUMMY" "DUMMY",ROW_NUMBER() OVER ( ORDER BY NULL) "rowli
mit_$$_rownumber" FROM "SYS"."DUAL" "A19" WHERE "A19"."DUMMY"="A14"."DUMMY") "A2
0" WHERE "A20"."rowlimit_$$_rownumber"<=1)) "A16")) "A15" WHERE 1=1))(+) "A13")
"A12") "A3") AND  NOT EXISTS (SELECT 0 F



select * from v$version;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
Version 23.26.3.0.0
Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free
0