select a.object_id,
extractvalue(value(b), '/ace/@start_date',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"'),
extractvalue(value(b), '/ace/@end_date',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"'),
substr(extractvalue(value(b), '/ace/grant',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"'),
1, 5),
CASE existsNode(value(b), '/ace/invert',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"') WHEN 1
THEN 'true'
ELSE 'false' END,
CASE existsNode(value(b), '/ace/invert',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"') WHEN 1
THEN extractvalue(value(b), '/ace/invert/principal',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
ELSE extractvalue(value(b), '/ace/principal',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"') END,
extract(value(b), '/ace/privilege',
'xmlns="http://xmlns.oracle.com/xdb/acl.xsd"')
from xdb.xdb$acl a,
table(XMLSequence(extract(a.object_value, '/acl/ace'))) b
SELECT A.OBJECT_ID
,
EXTRACTVALUE(VALUE(B)
, '/ACE/@START_DATE'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"')
,
EXTRACTVALUE(VALUE(B)
, '/ACE/@END_DATE'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"')
,
SUBSTR(EXTRACTVALUE(VALUE(B)
, '/ACE/GRANT'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"')
,
1
, 5)
,
CASE EXISTSNODE(VALUE(B)
, '/ACE/INVERT'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"') WHEN 1
THEN 'TRUE'
ELSE 'FALSE' END
,
CASE EXISTSNODE(VALUE(B)
, '/ACE/INVERT'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"') WHEN 1
THEN EXTRACTVALUE(VALUE(B)
, '/ACE/INVERT/PRINCIPAL'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"')
ELSE EXTRACTVALUE(VALUE(B)
, '/ACE/PRINCIPAL'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"') END
,
EXTRACT(VALUE(B)
, '/ACE/PRIVILEGE'
,
'XMLNS="HTTP://XMLNS.ORACLE.COM/XDB/ACL.XSD"')
FROM XDB.XDB$ACL A
,
TABLE(XMLSEQUENCE(EXTRACT(A.OBJECT_VALUE
, '/ACL/ACE'))) B
|
|
|