| Oracle® Databaseユーティリティ 12cリリース1 (12.1.0.2) B71303-09 |
|
 前 |
 次 |
この項では、DBMS_METADATAおよびDBMS_METADATA_DIFFの取得インタフェース、比較インタフェースおよび送信インタフェースの使用例を示します。この例では、2つの表のメタデータをフェッチして比較し、1つの表を他方と同じ内容に変更するALTER文を生成します。わかりやすくするために、この例全体でファンクションの変形を使用しています。
例21-10 オブジェクト・メタデータの比較
次のように、2つの表TAB1、TAB2を作成します。
SQL> CREATE TABLE TAB1 2 ( "EMPNO" NUMBER(4,0), 3 "ENAME" VARCHAR2(10), 4 "JOB" VARCHAR2(9), 5 "DEPTNO" NUMBER(2,0) 6 ) ; Table created. SQL> CREATE TABLE TAB2 2 ( "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE, 3 "ENAME" VARCHAR2(20), 4 "MGR" NUMBER(4,0), 5 "DEPTNO" NUMBER(2,0) 6 ) ; Table created.
表TAB1とTAB2の差異に注意します。
表名が異なります。
TAB2には主キー制約がありますが、TAB1にはありません。
それぞれの表で、ENAME列の長さが異なります。
TAB1にはJOB列がありますが、TAB2にはありません。
TAB2にはMGR列がありますが、TAB1にはありません。
SXML形式の表メタデータを返すファンクションを作成します。DBMS_METADATA_DIFF APIを使用するときに、SXMLに関して注意する必要がある重要な点について示します。
SXMLはオブジェクト・メタデータのXML表現です。
返されるSXMLは、DBMS_METADATA.GET_XMLによって返されるXMLと同じではありません。その場合のXMLは、複雑かつ不透明であり、バイナリ値やインスタンス固有の値などが含まれます。
SXMLは、SQL作成DDLを直接XMLに変換したものと考えることができます。タグ名と構造は、『Oracle Database SQL言語リファレンス』で説明されている名前に対応しています。
SXMLは、編集と比較を行えるように設計されています。
ここでは例を単純にするため、変換パラメータを使用して物理プロパティを抑止します。
SQL> CREATE OR REPLACE FUNCTION get_table_sxml(name IN VARCHAR2) RETURN CLOB IS
2 open_handle NUMBER;
3 transform_handle NUMBER;
4 doc CLOB;
5 BEGIN
6 open_handle := DBMS_METADATA.OPEN('TABLE');
7 DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);
8 --
9 -- Use the 'SXML' transform to convert XML to SXML
10 --
11 transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML');
12 --
13 -- Use this transform parameter to suppress physical properties
14 --
15 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES',
16 FALSE);
17 doc := DBMS_METADATA.FETCH_CLOB(open_handle);
18 DBMS_METADATA.CLOSE(open_handle);
19 RETURN doc;
20 END;
21 /
Function created.
次のように、get_table_sxmlファンクションを使用して、2つの表の表SXMLをフェッチします。
SQL> SELECT get_table_sxml('TAB1') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB1</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>10</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>JOB</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>9</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
SQL> SELECT get_table_sxml('TAB2') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB2</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>MGR</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
</PRIMARY_KEY_CONSTRAINT_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
次のように、DBMS_METADATAブラウザAPIを使用して結果を比較します。
SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB1') FROM dual;
SQL> SELECT dbms_metadata.get_sxml('TABLE','TAB2') FROM dual;
DBMS_METADATA_DIFF APIを使用してファンクションを作成し、2つの表のメタデータを比較します。このファンクションでは、手順2で定義したget_table_sxmlファンクションを使用します。
SQL> CREATE OR REPLACE FUNCTION compare_table_sxml(name1 IN VARCHAR2,
2 name2 IN VARCHAR2) RETURN CLOB IS
3 doc1 CLOB;
4 doc2 CLOB;
5 diffdoc CLOB;
6 openc_handle NUMBER;
7 BEGIN
8 --
9 -- Fetch the SXML for the two tables
10 --
11 doc1 := get_table_sxml(name1);
12 doc2 := get_table_sxml(name2);
13 --
14 -- Specify the object type in the OPENC call
15 --
16 openc_handle := DBMS_METADATA_DIFF.OPENC('TABLE');
17 --
18 -- Add each document
19 --
20 DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc1);
21 DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc2);
22 --
23 -- Fetch the SXML difference document
24 --
25 diffdoc := DBMS_METADATA_DIFF.FETCH_CLOB(openc_handle);
26 DBMS_METADATA_DIFF.CLOSE(openc_handle);
27 RETURN diffdoc;
28 END;
29 /
Function created.
次のようにファンクションを使用して、2つの表のSXML差分ドキュメントをフェッチします。
SQL> SELECT compare_table_sxml('TAB1','TAB2') FROM dual;
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>SCOTT</SCHEMA>
<NAME value1="TAB1">TAB2</NAME>
<RELATIONAL_TABLE>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>ENAME</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH value1="10">20</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="1">
<NAME>JOB</NAME>
<DATATYPE>VARCHAR2</DATATYPE>
<LENGTH>9</LENGTH>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<NAME>DEPTNO</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>2</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
<COL_LIST_ITEM src="2">
<NAME>MGR</NAME>
<DATATYPE>NUMBER</DATATYPE>
<PRECISION>4</PRECISION>
<SCALE>0</SCALE>
</COL_LIST_ITEM>
</COL_LIST>
<PRIMARY_KEY_CONSTRAINT_LIST src="2">
<PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
<COL_LIST>
<COL_LIST_ITEM>
<NAME>EMPNO</NAME>
</COL_LIST_ITEM>
</COL_LIST>
</PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
</PRIMARY_KEY_CONSTRAINT_LIST>
</RELATIONAL_TABLE>
</TABLE>
1 row selected.
SXML差分ドキュメントでは、2つのSXMLドキュメントの結合内容が示され、差分を識別するXML属性value1およびsrcが使用されます。ある要素が1つのドキュメントのみに存在するときは、その要素がsrcでマーキングされます。したがって、<COL_LIST_ITEM src="1">は、この要素が最初のドキュメント(TAB1)に含まれていて、2番目のドキュメントに含まれていないことを表します。ある要素が両方のドキュメントに存在し、値がそれぞれ異なる場合、この要素の値は2番目のドキュメント内の値とされ、value1によって最初のドキュメント内のその値が示されます。たとえば、<LENGTH value1="10">20</LENGTH>は、長さがTAB1(最初のドキュメント)では10、TAB2では20であることを表します。
次のように、DBMS_METADATA_DIFFブラウザAPIを使用して、結果を比較します。
SQL> SELECT dbms_metadata_diff.compare_sxml('TABLE','TAB1','TAB2') FROM dual;
DBMS_METADATA.CONVERT APIを使用してファンクションを作成し、ALTERXMLドキュメントを生成します。これは、片方のオブジェクトをもう一方のオブジェクトと同様にするためのALTER文が含まれているXML文書です。解析項目を使用して、個々のALTER文に関する情報を取得することもできます。(この例ではこれまでに定義したファンクションを使用しています。)
SQL> CREATE OR REPLACE FUNCTION get_table_alterxml(name1 IN VARCHAR2,
2 name2 IN VARCHAR2) RETURN CLOB IS
3 diffdoc CLOB;
4 openw_handle NUMBER;
5 transform_handle NUMBER;
6 alterxml CLOB;
7 BEGIN
8 --
9 -- Use the function just defined to get the difference document
10 --
11 diffdoc := compare_table_sxml(name1,name2);
12 --
13 -- Specify the object type in the OPENW call
14 --
15 openw_handle := DBMS_METADATA.OPENW('TABLE');
16 --
17 -- Use the ALTERXML transform to generate the ALTER_XML document
18 --
19 transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERXML');
20 --
21 -- Request parse items
22 --
23 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'CLAUSE_TYPE');
24 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'NAME');
25 DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'COLUMN_ATTRIBUTE');
26 --
27 -- Create a temporary LOB
28 --
29 DBMS_LOB.CREATETEMPORARY(alterxml, TRUE );
30 --
31 -- Call CONVERT to do the transform
32 --
33 DBMS_METADATA.CONVERT(openw_handle,diffdoc,alterxml);
34 --
35 -- Close context and return the result
36 --
37 DBMS_METADATA.CLOSE(openw_handle);
38 RETURN alterxml;
39 END;
40 /
Function created.
次のようにファンクションを使用して、ALTER_XML文書をフェッチします。
SQL> SELECT get_table_alterxml('TAB1','TAB2') FROM dual;
<ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
<OBJECT1>
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB1</NAME>
</OBJECT1>
<OBJECT2>
<SCHEMA>SCOTT</SCHEMA>
<NAME>TAB2</NAME>
</OBJECT2>
<ALTER_LIST>
<ALTER_LIST_ITEM>
<PARSE_LIST>
<PARSE_LIST_ITEM>
<ITEM>NAME</ITEM>
<VALUE>MGR</VALUE>
</PARSE_LIST_ITEM>
<PARSE_LIST_ITEM>
<ITEM>CLAUSE_TYPE</ITEM>
<VALUE>ADD_COLUMN</VALUE>
</PARSE_LIST_ITEM>
</PARSE_LIST>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<PARSE_LIST>
<PARSE_LIST_ITEM>
<ITEM>NAME</ITEM>
<VALUE>JOB</VALUE>
</PARSE_LIST_ITEM>
<PARSE_LIST_ITEM>
<ITEM>CLAUSE_TYPE</ITEM>
<VALUE>DROP_COLUMN</VALUE>
</PARSE_LIST_ITEM>
</PARSE_LIST>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<PARSE_LIST>
<PARSE_LIST_ITEM>
<ITEM>NAME</ITEM>
<VALUE>ENAME</VALUE>
</PARSE_LIST_ITEM>
<PARSE_LIST_ITEM>
<ITEM>CLAUSE_TYPE</ITEM>
<VALUE>MODIFY_COLUMN</VALUE>
</PARSE_LIST_ITEM>
<PARSE_LIST_ITEM>
<ITEM>COLUMN_ATTRIBUTE</ITEM>
<VALUE> SIZE_INCREASE</VALUE>
</PARSE_LIST_ITEM>
</PARSE_LIST>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "SCOTT"."TAB1" MODIFY
("ENAME" VARCHAR2(20))
</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<PARSE_LIST>
<PARSE_LIST_ITEM>
<ITEM>CLAUSE_TYPE</ITEM>
<VALUE>ADD_CONSTRAINT</VALUE>
</PARSE_LIST_ITEM>
</PARSE_LIST>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY
("EMPNO") ENABLE
</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
<ALTER_LIST_ITEM>
<PARSE_LIST>
<PARSE_LIST_ITEM>
<ITEM>NAME</ITEM>
<VALUE>TAB1</VALUE>
</PARSE_LIST_ITEM>
<PARSE_LIST_ITEM>
<ITEM>CLAUSE_TYPE</ITEM>
<VALUE>RENAME_TABLE</VALUE>
</PARSE_LIST_ITEM>
</PARSE_LIST>
<SQL_LIST>
<SQL_LIST_ITEM>
<TEXT>ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"</TEXT>
</SQL_LIST_ITEM>
</SQL_LIST>
</ALTER_LIST_ITEM>
</ALTER_LIST>
</ALTER_XML>
1 row selected.
次のように、DBMS_METADATA_DIFFブラウザAPIを使用して、結果を比較します。
SQL> SELECT dbms_metadata_diff.compare_alter_xml('TABLE','TAB1','TAB2') FROM dual;
ALTER_XML文書には、それぞれの変更のALTER_LISTが含まれています。それぞれのALTER_LIST_ITEMには、名前と値のペアとしての解析項目が含まれたPARSE_LISTと、特定の変更のSQLが含まれたSQL_LISTがあります。PARSE_LIST内の情報を使用してこの文書を解析し、どのSQL文を実行するかを決定できます。(たとえば、この場合は、変更の1つがDROP_COLUMNであり、それを実行しないことを決定できます。)
次のように、DBMS_METADATA.CONVERT APIおよびALTER DDL変換を使用する最後の1つのファンクションを作成して、ALTER_XML文書をSQL DDLに変換します。
SQL> CREATE OR REPLACE FUNCTION get_table_alterddl(name1 IN VARCHAR2,
2 name2 IN VARCHAR2) RETURN CLOB IS
3 alterxml CLOB;
4 openw_handle NUMBER;
5 transform_handle NUMBER;
6 alterddl CLOB;
7 BEGIN
8 --
9 -- Use the function just defined to get the ALTER_XML document
10 --
11 alterxml := get_table_alterxml(name1,name2);
12 --
13 -- Specify the object type in the OPENW call
14 --
15 openw_handle := DBMS_METADATA.OPENW('TABLE');
16 --
17 -- Use ALTERDDL transform to convert the ALTER_XML document to SQL DDL
18 --
19 transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERDDL');
20 --
21 -- Use the SQLTERMINATOR transform parameter to append a terminator
22 -- to each SQL statement
23 --
24 DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'SQLTERMINATOR',true);
25 --
26 -- Create a temporary lob
27 --
28 DBMS_LOB.CREATETEMPORARY(alterddl, TRUE );
29 --
30 -- Call CONVERT to do the transform
31 --
32 DBMS_METADATA.CONVERT(openw_handle,alterxml,alterddl);
33 --
34 -- Close context and return the result
35 --
36 DBMS_METADATA.CLOSE(openw_handle);
37 RETURN alterddl;
38 END;
39 /
Function created.
次のようにファンクションを使用して、SQL ALTER文をフェッチします。
SQL> SELECT get_table_alterddl('TAB1','TAB2') FROM dual;
ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))
/
ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")
/
ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20))
/
ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY ("EMPNO") ENABLE
/
ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"
/
1 row selected.
次のように、DBMS_METADATA_DIFFブラウザAPIを使用して、結果を比較します。
SQL> SELECT dbms_metadata_diff.compare_alter('TABLE','TAB1','TAB2') FROM dual;
ALTER TABLE "SCOTT"."TAB1" ADD ("MGR" NUMBER(4,0))
ALTER TABLE "SCOTT"."TAB1" DROP ("JOB")
ALTER TABLE "SCOTT"."TAB1" MODIFY ("ENAME" VARCHAR2(20))
ALTER TABLE "SCOTT"."TAB1" ADD PRIMARY KEY ("EMPNO") USING INDEX
PCTFREE 10 INITRANS 2 STORAGE ( INITIAL 16384 NEXT 16384 MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) ENABLE ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"
1 row selected.
