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
|
