SI Object Browser for Oracle 18 ReadOnly Editionを使用しています。 下記のように再帰WITH句の後にWITH句を続けて書くと、『SELECT 文以外の SQL 文を実行する事は出来ません。』というエラーが発生してしまい、SQLを実行する事ができません。 SI Object Browser for Oracle Ver.11 ReadOnly Editionを使用していた際には問題なく実行できていました。 SQL*Plusでは問題なく実行できます。 お忙しい所恐縮ですが、原因の調査を行って頂けますでしょうか。
WITH TEMP1(LVL ,TYPE ,OWNER ,NAME ,ID ,REFERENCED_TYPE ,REFERENCED_OWNER ,REFERENCED_NAME ,REFERENCE_PATH ) AS ( SELECT 1 ,TYPE ,OWNER ,NAME ,OWNER || '.' || NAME ,REFERENCED_TYPE ,REFERENCED_OWNER ,REFERENCED_NAME ,OWNER || '.' || NAME || '-' || REFERENCED_OWNER || '.' || REFERENCED_NAME FROM DBA_DEPENDENCIES WHERE OWNER = UPPER('SYS') AND NAME = UPPER('V$SESSION') AND REFERENCED_OWNER = 'SYS' UNION ALL SELECT T.LVL + 1 ,DBAD2.TYPE ,DBAD2.OWNER ,DBAD2.NAME ,DBAD2.OWNER || '.' || DBAD2.NAME ,DBAD2.REFERENCED_TYPE ,DBAD2.REFERENCED_OWNER ,DBAD2.REFERENCED_NAME ,T.REFERENCE_PATH || '-' || DBAD2.REFERENCED_OWNER || '.' || DBAD2.REFERENCED_NAME FROM DBA_DEPENDENCIES DBAD2 ,TEMP1 T WHERE DBAD2.OWNER = T.REFERENCED_OWNER AND DBAD2.NAME = T.REFERENCED_NAME AND DBAD2.REFERENCED_OWNER = 'SYS' ) SEARCH DEPTH FIRST BY NAME SET ORDER1 CYCLE ID SET ISLOOP TO 'Y' DEFAULT 'N' ,DEPENDENCIES AS ( SELECT UPPER('SYS.V$SESSION') AS ROOT ,0 ,NULL ,NULL ,NULL ,CASE LISTAGG(OBJECT_TYPE,',') WITHIN GROUP (order by OBJECT_TYPE) WHEN 'MATERIALIZED VIEW,TABLE' THEN 'MATERIALIZED VIEW' WHEN 'PACKAGE BODY,PACKAGE' THEN 'PACKAGE BODY' ELSE LISTAGG(OBJECT_TYPE,',') WITHIN GROUP (order by OBJECT_TYPE) END ,OWNER ,OBJECT_NAME ,0 ,UPPER('SYS.V$SESSION') FROM DBA_OBJECTS WHERE OWNER = UPPER('SYS') AND OBJECT_NAME = UPPER('V$SESSION') GROUP BY OWNER,OBJECT_NAME UNION ALL SELECT UPPER('SYS.V$SESSION') AS ROOT ,TEMP1.LVL ,TEMP1.TYPE ,TEMP1.OWNER ,TEMP1.NAME ,CASE WHEN TEMP1.REFERENCED_TYPE = 'TABLE' AND DBA_MVIEWS.MVIEW_NAME IS NOT NULL THEN 'MATERIALIZED VIEW' ELSE TEMP1.REFERENCED_TYPE END ,TEMP1.REFERENCED_OWNER ,TEMP1.REFERENCED_NAME ,TEMP1.ORDER1 ,TEMP1.REFERENCE_PATH FROM TEMP1 LEFT OUTER JOIN DBA_MVIEWS ON TEMP1.REFERENCED_OWNER = DBA_MVIEWS.OWNER AND TEMP1.REFERENCED_NAME = DBA_MVIEWS.MVIEW_NAME WHERE ISLOOP = 'N' --訪問済みノードを表示しない ) SELECT * FROM DEPENDENCIES
|