1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
|
SQL> r
1 With xml_datagram AS (
2 SELECT XmlType(DBMS_METADATA.GET_DEPENDENT_XML('CONSTRAINT', 'EMP', 'SCOTT')) d
at
3 FROM dual
4 )
5 Select column_name, ltrim(rtrim(column_value,''''),'''') col_value
6 From(
7 Select column_name, SubStr(search_condition,Instr(search_condition,'(') + 1 ,
8 Instr(search_condition,')') - Instr(sear
ch_condition,'(')-1) val
9 From (
10 SELECT extractvalue(x.column_value,'/ROW/CONSTRAINT_T/CON1/COL_LIST/COL_LIST_ITEM[1]
/COL/NAME') column_name,
11 extractvalue(x.column_value,'/ROW/CONSTRAINT_T/CON1/CONDITION') search_condit
ion
12 FROM xml_datagram,
13 TABLE(xmlsequence(extract(xml_datagram.dat,'/ROWSET/ROW')))(+) x
14 )
15* ) t, Table(str2tbl(t.val,','))
COLUMN_NAM COL_VALUE
---------- ------------------------------
JOB ANALYST
JOB CLERK
JOB MANAGER
JOB PRESIDENT
JOB SALESMAN |
Partager