| Oracle® Databaseユーティリティ 11gリリース2 (11.2) B56303-08 |
|
 前 |
 次 |
この章では、メタデータAPI、DBMS_METADATAおよびDBMS_METADATA_DIFFの使用方法を説明します。
DBMS_METADATA APIを使用すると、次の操作を行うことができます。
オブジェクトのメタデータのXMLとしての取得
SQL DDLへの変換を含む様々な方法でのXMLの変換
取得で抽出したオブジェクトを再作成するためのXMLの送信
DBMS_METADATA_DIFF APIを使用すると、データベース間でオブジェクトを比較し、同じ型のオブジェクトのうち時間の経過によってメタデータが変化したものを識別できます。
この章の内容は、次のとおりです。
Oracle Databaseの長期の使用に伴い、ディレクトリからメタデータを抽出し、そのメタデータに対し列の追加やデータ型の変更などを行い、さらにDDLに変換して元のまたは異なるデータベースのオブジェクトを再作成するために、ユーザーが独自のコードを開発する機会が多くなりました。ディクショナリの新機能をサポートするよう、そのコードを更新された状態に保つことは困難です。
DBMS_METADATA APIを使用すると、メタデータを抽出するために独自のコードを記述して管理する必要がなくなります。ディクショナリ・メタデータの抽出、操作および再作成に関する集中的な機能が提供されます。また、すべてのディクショナリ・オブジェクトを最新のレベルでサポートしています。
DBMS_METADATA APIは、記述および管理するカスタム・コードの量を大幅に削減しますが、通常のデータベース手順の変更はありません。DBMS_METADATA APIは、データ・ディクショナリ・ビューと同様、データベースのインストール時にcatproc.sqlを実行し、SQLスクリプトを起動してインストールします。インストール後は、制限モードであっても、インスタンスが動作中はいつでも使用可能です。
DBMS_METADATA APIは、異なるOracleのリリース間で上位互換性があるため、データベースのリリースを変更しても、ソース・コードの変更は必要ありません。1つのリリースで取得したXML文書を、同じまたはそれ以上のリリースの送信インタフェースで処理できます。たとえば、Oracle9iデータベースで取得したXML文書は、Oracle Database 10gに送信できます。
DBMS_METADATA APIにおいては、データベース内のすべてのエンティティは、オブジェクト型に属するオブジェクトとして形成されています。たとえば、scott.emp表はオブジェクトであり、オブジェクト型はTABLEです。オブジェクトのメタデータをフェッチする場合は、オブジェクト型を指定する必要があります。
オブジェクト型の特定のオブジェクトまたは一連のオブジェクトをフェッチするには、フィルタを指定します。各オブジェクト型に対して異なるフィルタが定義されます。たとえば、TABLEオブジェクト型に対して定義されている2つのフィルタはSCHEMAおよびNAMEです。これにより、たとえば、スキーマがscottで名前がempである表を必要としていることを表現できます。
DBMS_METADATA APIは、XML(Extensible Markup Language)およびXSLT(Extensible Stylesheet Language Transformation)を使用します。XMLは解析や変換が容易な汎用形式であるため、DBMS_METADATA APIは、オブジェクト・メタデータをXMLとして表現します。DBMS_METADATA APIは、XSLTを使用してXML文書を他のXML文書またはSQL DDLのいずれかに変換します。
DBMS_METADATA APIを使用して、メタデータをフェッチするとき(または再送信するとき)に、1つ以上の変換(XSLTスクリプト)をXMLに適用するよう指定できます。APIには、XML文書をSQL作成DDLに変換する名前付きDDL以外にも、いくつかの事前定義変換があります。
次に変換パラメータを使用して、変換の条件を指定できます。また、オブジェクトのメタデータの特定の属性にアクセスするために、オプションの解析項目も指定できます。これらのオプションの詳細と実装例については、次の項を参照してください。
DBMS_METADATA APIの取得インタフェースでは、取得するオブジェクトの種類を指定できます。種類は、特定のオブジェクト型(表、索引、プロシージャなど)または論理単位を形成する異なるオブジェクト型のコレクション(データベース・エクスポート、スキーマ・エクスポートなど)のいずれかです。デフォルトでは、フェッチされたメタデータがXML文書に返されます。
|
注意: スキーマにないオブジェクトにアクセスするにはSELECT_CATALOG_ROLEロールが必要です。ただし、ロールは多くのPL/SQLオブジェクト(ストアド・プロシージャ、ファンクション、定義者権限API)内で無効とされます。そのため、他のスキーマのオブジェクト(一般的には、SELECT_CATALOG_ROLEロールを必要とするすべてのオブジェクト)内にアクセスするPL/SQLプログラムを記述する場合は、実行者権限APIにコードを入力する必要があります。 |
プログラム・インタフェースを使用して、簡単な参照を実行したり、アプリケーションを開発できます。システム・メタデータの非定型の問合せを行う場合は、ブラウザ・インタフェースを使用します。アプリケーションの一部として、ディクショナリ・メタデータを抽出する場合は、プログラム・インタフェースを使用します。その場合、同じことをするために現在使用しているSQLスクリプトおよびカスタマイズしたコードのかわりに、DBMS_METADATA APIで提供されているプロシージャを使用することもできます。
メタデータを取得する場合は、DBMS_METADATA PL/SQL APIを使用します。次に、プログラム・インタフェースおよびブラウザ・インタフェースの例を示します。
例20-1では、DBMS_METADATAプログラム・インタフェースを使用して1つの表のメタデータを取得する基本的な方法を説明します。この例では、get_table_mdというファンクションを作成するDBMS_METADATAプログラムを作成します。ファンクションは、1つの表のメタデータを返します。
例20-1 データ取得のためのDBMS_METADATAプログラム・インタフェースの使用
hrスキーマ内の1つの表(timecards)のメタデータを返すファンクションget_table_mdを作成するDBMS_METADATAプログラムを作成します。プログラムの内容は次のようになります。(この例ではプログラムにmetadata_program.sqlという名前を付けます)。
CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
-- Define local variables.
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);
-- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/
ユーザーhrとして接続します。
プログラムを実行してget_table_mdファンクションを作成します。
SQL> @metadata_program
新しく作成したget_table_mdファンクションを選択操作で使用します。完全で中断のない出力を生成するには、問合せの実行前に、次のとおり、PAGESIZEを0(ゼロ)にし、またLONGを大きい数に設定します。
SQL> SET PAGESIZE 0 SQL> SET LONG 1000000 SQL> SELECT get_table_md FROM dual;
hrスキーマのtimecards表のメタデータを示す出力は、次のようになります。
CREATE TABLE "HR"."TIMECARDS"
( "EMPLOYEE_ID" NUMBER(6,0),
"WEEK" NUMBER(2,0),
"JOB_ID" VARCHAR2(10),
"HOURS_WORKED" NUMBER(4,2),
FOREIGN KEY ("EMPLOYEE_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EXAMPLE"
例20-2に示すとおり、ブラウザ・インタフェースを使用しても同じ結果を得られます。
例20-2 データ取得のためのDBMS_METADATAブラウザ・インタフェースの使用
SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TIMECARDS','HR') FROM dual;
結果は、例20-1の手順5と同様になります。
例20-1では、オブジェクトが1つしか存在しないことがわかっていたため、FETCH_CLOBプロシージャは1回のみコールされました。scottスキーマのすべての表などの複数のオブジェクトも取得できます。それを行うには、次の構成メンバーを使用する必要があります。
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);
--
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
--
EXIT WHEN doc IS NULL;
END LOOP;
例20-3に、構成メンバーの使用および複数のオブジェクトの取得を示します。この例では、ユーザーscottとして接続します。パスワードは、tigerです。
例20-3 複数のオブジェクトの取得
次のように、my_metadataという名前の表と、get_tables_mdという名前のプロシージャを作成します。すべてのオブジェクトを返すことはできないため、それらを表に格納して最後に問合せを実行します。
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md clob);
CREATE OR REPLACE PROCEDURE get_tables_md IS
-- Define local variables
h NUMBER; -- handle returned by 'OPEN'
th NUMBER; -- handle returned by 'ADD_TRANSFORM'
doc CLOB; -- metadata is returned in a CLOB
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
-- Store the metadata in a table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;
-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/
次のようにプロシージャを実行します。
EXECUTE get_tables_md;
my_metadata表を問い合せて、取得された内容を確認します。
SET LONG 9000000 SET PAGES 0 SELECT * FROM my_metadata;
変換パラメータを使用して、追加する変換の条件を指定できます。SET_TRANSFORM_PARAMプロシージャを使用します。たとえば、TABLEオブジェクトに対してDDL変換を追加した場合、SEGMENT_ATTRIBUTES変換パラメータを指定して、物理、ストレージ、ロギングなどのセグメント属性をDDLに表示しないようにすることができます。デフォルトでは、セグメント属性がDDLに表示されます。
例20-4に、SET_TRANSFORM_PARAMプロシージャの使用方法を示します。
例20-4 変換の条件指定
次のように、get_table_mdという名前のファンクションを作成します。
CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
-- Define local variables.
h NUMBER; -- handle returned by 'OPEN'
th NUMBER; -- handle returned by 'ADD_TRANSFORM'
doc CLOB;
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
-- Request that the metadata be transformed into creation DDL.
th := dbms_metadata.add_transform(h,'DDL');
-- Specify that segment attributes are not to be returned.
-- Note that this call uses the TRANSFORM handle, not the OPEN handle.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false);
-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);
-- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/
次の問合せを実行します。
SQL> SELECT get_table_md FROM dual;
出力は、次のようになります。
CREATE TABLE "HR"."TIMECARDS"
( "EMPLOYEE_ID" NUMBER(6,0),
"WEEK" NUMBER(2,0),
"JOB_ID" VARCHAR2(10),
"HOURS_WORKED" NUMBER(4,2),
FOREIGN KEY ("EMPLOYEE_ID")
REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
)
ここまでの例ではDDL変換という1つの変換を使用しました。DBMS_METADATA APIでは、最初の出力が2番目の入力になり、2番目の出力が3番目の入力になるというように、複数の変換を指定することもできます。
Oracleには、XML文書を変更するMODIFYと呼ばれる変換があります。スキーマ名や表領域名の変更などが行えます。これには、再マップ・パラメータおよびSET_REMAP_PARAMプロシージャを使用します。
例20-5に、SET_REMAP_PARAMプロシージャの使用例を示します。まずMODIFY変換を追加し、再マップ・パラメータを指定して、スキーマ名をhrからscottに変更します。次にDDL変換を追加します。MODIFY変換の出力は、DDL変換への入力となるXML文書です。最終的な結果は、timecards表の作成DDLであり、hrスキーマのすべてのインスタンスはscottに変更されます。
例20-5 XML文書の変更
次のように、remap_schemaという名前のファンクションを作成します。
CREATE OR REPLACE FUNCTION remap_schema RETURN CLOB IS
-- Define local variables.
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
-- Request that the schema name be modified.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR','SCOTT');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- Specify that segment attributes are not to be returned.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false);
-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);
-- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/
次の問合せを実行します。
SELECT remap_schema FROM dual;
出力は、次のようになります。
CREATE TABLE "SCOTT"."TIMECARDS"
( "EMPLOYEE_ID" NUMBER(6,0),
"WEEK" NUMBER(2,0),
"JOB_ID" VARCHAR2(10),
"HOURS_WORKED" NUMBER(4,2),
FOREIGN KEY ("EMPLOYEE_ID")
REFERENCES "SCOTT"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
)
XSLTをよく理解しているユーザーであれば、独自の変換を追加してXMLを処理できます。
名前やスキーマなどのオブジェクトのメタデータの特定の属性へアクセスすることが必要な場合もあります。属性の情報は返されたメタデータを解析して取得できますが、DBMS_METADATA APIには、別の機能もあります。メタデータから解析され、別のデータ構造に返される特定の属性を解析項目に指定できます。これを行うには、SET_PARSE_ITEMプロシージャを使用します。
例20-6では、スキーマ内のすべての表をフェッチします。各表では、名前を取得するために解析項目を使用します。次に表のすべての索引を取得するために名前を使用します。次の例に、sys.ku$_ddlsオブジェクトのメタデータを返すFETCH_DDLファンクションの使用を示します。
この例では、いくつかの表および索引を含むスキーマに接続していることとします。また、my_metadataという名前の表も作成します。
例20-6 特定のメタデータ属性にアクセスするための解析項目の使用
次のように、my_metadataという名前の表と、get_tables_and_indexesという名前のプロシージャを作成します。
DROP TABLE my_metadata;
CREATE TABLE my_metadata (
object_type VARCHAR2(30),
name VARCHAR2(30),
md CLOB);
CREATE OR REPLACE PROCEDURE get_tables_and_indexes IS
-- Define local variables.
h1 NUMBER; -- handle returned by OPEN for tables
h2 NUMBER; -- handle returned by OPEN for indexes
th1 NUMBER; -- handle returned by ADD_TRANSFORM for tables
th2 NUMBER; -- handle returned by ADD_TRANSFORM for indexes
doc sys.ku$_ddls; -- metadata is returned in sys.ku$_ddls,
-- a nested table of sys.ku$_ddl objects
ddl CLOB; -- creation DDL for an object
pi sys.ku$_parsed_items; -- parse items are returned in this object
-- which is contained in sys.ku$_ddl
objname VARCHAR2(30); -- the parsed object name
idxddls sys.ku$_ddls; -- metadata is returned in sys.ku$_ddls,
-- a nested table of sys.ku$_ddl objects
idxname VARCHAR2(30); -- the parsed index name
BEGIN
-- This procedure has an outer loop that fetches tables,
-- and an inner loop that fetches indexes.
-- Specify the object type: TABLE.
h1 := DBMS_METADATA.OPEN('TABLE');
-- Request that the table name be returned as a parse item.
DBMS_METADATA.SET_PARSE_ITEM(h1,'NAME');
-- Request that the metadata be transformed into creation DDL.
th1 := DBMS_METADATA.ADD_TRANSFORM(h1,'DDL');
-- Specify that segment attributes are not to be returned.
DBMS_METADATA.SET_TRANSFORM_PARAM(th1,'SEGMENT_ATTRIBUTES',false);
-- Set up the outer loop: fetch the TABLE objects.
LOOP
doc := dbms_metadata.fetch_ddl(h1);
-- When there are no more objects to be retrieved, FETCH_DDL returns NULL.
EXIT WHEN doc IS NULL;
-- Loop through the rows of the ku$_ddls nested table.
FOR i IN doc.FIRST..doc.LAST LOOP
ddl := doc(i).ddlText;
pi := doc(i).parsedItems;
-- Loop through the returned parse items.
IF pi IS NOT NULL AND pi.COUNT > 0 THEN
FOR j IN pi.FIRST..pi.LAST LOOP
IF pi(j).item='NAME' THEN
objname := pi(j).value;
END IF;
END LOOP;
END IF;
-- Insert information about this object into our table.
INSERT INTO my_metadata(object_type, name, md)
VALUES ('TABLE',objname,ddl);
COMMIT;
END LOOP;
-- Now fetch indexes using the parsed table name as
-- a BASE_OBJECT_NAME filter.
-- Specify the object type.
h2 := DBMS_METADATA.OPEN('INDEX');
-- The base object is the table retrieved in the outer loop.
DBMS_METADATA.SET_FILTER(h2,'BASE_OBJECT_NAME',objname);
-- Exclude system-generated indexes.
DBMS_METADATA.SET_FILTER(h2,'SYSTEM_GENERATED',false);
-- Request that the index name be returned as a parse item.
DBMS_METADATA.SET_PARSE_ITEM(h2,'NAME');
-- Request that the metadata be transformed into creation DDL.
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');
-- Specify that segment attributes are not to be returned.
DBMS_METADATA.SET_TRANSFORM_PARAM(th2,'SEGMENT_ATTRIBUTES',false);
LOOP
idxddls := dbms_metadata.fetch_ddl(h2);
-- When there are no more objects to be retrieved, FETCH_DDL returns NULL.
EXIT WHEN idxddls IS NULL;
FOR i in idxddls.FIRST..idxddls.LAST LOOP
ddl := idxddls(i).ddlText;
pi := idxddls(i).parsedItems;
-- Loop through the returned parse items.
IF pi IS NOT NULL AND pi.COUNT > 0 THEN
FOR j IN pi.FIRST..pi.LAST LOOP
IF pi(j).item='NAME' THEN
idxname := pi(j).value;
END IF;
END LOOP;
END IF;
-- Store the metadata in our table.
INSERT INTO my_metadata(object_type, name, md)
VALUES ('INDEX',idxname,ddl);
COMMIT;
END LOOP; -- for loop
END LOOP;
DBMS_METADATA.CLOSE(h2);
END LOOP;
DBMS_METADATA.CLOSE(h1);
END;
/
次のようにプロシージャを実行します。
EXECUTE get_tables_and_indexes;
次の問合せを実行して、取得された内容を確認します。
SET LONG 9000000 SET PAGES 0 SELECT * FROM my_metadata;
オブジェクトに対しメタデータをフェッチする場合、そのメタデータを使用して、オブジェクトを異なるデータベースまたはスキーマで再作成することがあります。
メタデータをフェッチする際に、再マップの実行を決定していない場合もあります。また、この決定を先送りにしたい場合があります。再マップを先送りにするには、メタデータをXMLとしてフェッチし、ファイルまたは表に格納します。後で送信インタフェースを使用しオブジェクトを再作成します。
送信インタフェースは取得インタフェースに類似した構成です。送信インタフェースには、作成するオブジェクトのオブジェクト型を指定するOPENWプロシージャが存在します。変換の指定、パラメータの変換および項目の解析ができます。CONVERTファンクションをコールしてXMLからDDLに変換する、またはPUTファンクションをコールしてXMLをDDLに変換し、DDLを送信してオブジェクトを作成できます。
例20-7は、1つのスキーマの表のXMLをフェッチし、次に送信インタフェースを使用して別のスキーマの表を再作成します。
例20-7 取得したオブジェクトを再作成するための送信インタフェースの使用
次のように、権限を持つユーザーとして接続します。
CONNECT system
Enter password: password
実行者権限パッケージを作成してプロシージャを保持します。これは、別のスキーマ内のオブジェクトにアクセスするには、SELECT_CATALOG_ROLEロールが必要なためです。定義者権限PL/SQLオブジェクト(プロシージャ、ファンクションなど)では、ロールは使用できません。
CREATE OR REPLACE PACKAGE example_pkg AUTHID current_user IS
PROCEDURE move_table(
table_name in VARCHAR2,
from_schema in VARCHAR2,
to_schema in VARCHAR2 );
END example_pkg;
/
CREATE OR REPLACE PACKAGE BODY example_pkg IS
PROCEDURE move_table(
table_name in VARCHAR2,
from_schema in VARCHAR2,
to_schema in VARCHAR2 ) IS
-- Define local variables.
h1 NUMBER; -- handle returned by OPEN
h2 NUMBER; -- handle returned by OPENW
th1 NUMBER; -- handle returned by ADD_TRANSFORM for MODIFY
th2 NUMBER; -- handle returned by ADD_TRANSFORM for DDL
xml CLOB; -- XML document
errs sys.ku$_SubmitResults := sys.ku$_SubmitResults();
err sys.ku$_SubmitResult;
result BOOLEAN;
BEGIN
-- Specify the object type.
h1 := DBMS_METADATA.OPEN('TABLE');
-- Use filters to specify the name and schema of the table.
DBMS_METADATA.SET_FILTER(h1,'NAME',table_name);
DBMS_METADATA.SET_FILTER(h1,'SCHEMA',from_schema);
-- Fetch the XML.
xml := DBMS_METADATA.FETCH_CLOB(h1);
IF xml IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Table ' || from_schema || '.' || table_name
|| ' not found');
RETURN;
END IF;
-- Release resources.
DBMS_METADATA.CLOSE(h1);
-- Use the submit interface to re-create the object in another schema.
-- Specify the object type using OPENW (instead of OPEN).
h2 := DBMS_METADATA.OPENW('TABLE');
-- First, add the MODIFY transform.
th1 := DBMS_METADATA.ADD_TRANSFORM(h2,'MODIFY');
-- Specify the desired modification: remap the schema name.
DBMS_METADATA.SET_REMAP_PARAM(th1,'REMAP_SCHEMA',from_schema,to_schema);
-- Now add the DDL transform so that the modified XML can be
-- transformed into creation DDL.
th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');
-- Call PUT to re-create the object.
result := DBMS_METADATA.PUT(h2,xml,0,errs);
DBMS_METADATA.CLOSE(h2);
IF NOT result THEN
-- Process the error information.
FOR i IN errs.FIRST..errs.LAST LOOP
err := errs(i);
FOR j IN err.errorLines.FIRST..err.errorLines.LAST LOOP
dbms_output.put_line(err.errorLines(j).errorText);
END LOOP;
END LOOP;
END IF;
END;
END example_pkg;
/
ここで、次のように、スキーマSCOTTでmy_exampleという名前の表を作成します。
CONNECT scott
Enter password:
-- The password is tiger.
DROP TABLE my_example;
CREATE TABLE my_example (a NUMBER, b VARCHAR2(30));
CONNECT system
Enter password: password
SET LONG 9000000
SET PAGESIZE 0
SET SERVEROUTPUT ON SIZE 100000
次のように、my_example表をSYSTEMスキーマにコピーします。
DROP TABLE my_example;
EXECUTE example_pkg.move_table('MY_EXAMPLE','SCOTT','SYSTEM');
次の問合せを実行して、正しく動作したことを確認します。
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_EXAMPLE') FROM dual;
異なる型のオブジェクトであるにもかかわらず、1つの論理単位を形成するオブジェクトのコレクションを取得する必要がある場合があります。たとえば、データベースまたはスキーマのすべてのオブジェクト、または表および依存する索引、制約、権限付与、監査などすべてを取得する必要があるとします。DBMS_METADATA APIでは、こうした取得を可能にするために、複数の異種オブジェクト型を提供しています。異種オブジェクト型は、オブジェクト型の順序付けられた集合です。
Oracleでは、次の異種オブジェクト型を提供しています。
TABLE_EXPORT: 表およびその依存オブジェクト
SCHEMA_EXPORT: スキーマおよびその内容
DATABASE_EXPORT: データベース内のオブジェクト
これらのオブジェクト型は、データ・ポンプ・エクスポート・ユーティリティで使用するために開発されましたが、ユーザー独自のアプリケーションでも使用できます。
これらの型では、ブラウザ・インタフェースまたは送信インタフェースではなく、プログラム取得インタフェース(OPEN, FETCH, CLOSE)のみが使用できます。
同種型と同様に、異種オブジェクト型に対してもフィルタを指定できます。たとえば、TABLE_EXPORTに対してSCHEMAおよびNAMEフィルタを指定でき、SCHEMA_EXPORTに対してSCHEMAフィルタを指定できます。
例20-8に、scottスキーマ内のオブジェクト型を取得する方法を示します。ユーザーscottとして接続します。パスワードは、tigerです。
例20-8 異種オブジェクト型の取得方法
取得したオブジェクトを格納するための表を作成します。
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md IS
-- Define local variables.
h NUMBER; -- handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB; -- metadata is returned in a CLOB
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
-- Store the metadata in the table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;
-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/
次のようにプロシージャを実行します。
EXECUTE get_schema_md;
次の問合せを実行して、取得された内容を確認します。
SET LONG 9000000 SET PAGESIZE 0 SELECT * FROM my_metadata;
この例では、オブジェクトは、オブジェクト型による順序で返されます。たとえば、すべての表が返され、次に表のすべての権限が返され、次に表のすべての索引が返されます。順序は一般的に有効な作成順序となります。このため、返された順序でオブジェクトを取得し、送信インタフェースを使用して別のスキーマまたはデータベースで同じ順序で再作成した場合、通常、エラーは発生しません。(通常、例外は、循環参照の場合に発生します。たとえば、パッケージAにパッケージBへのコールが含まれ、パッケージBにパッケージAへのコールが含まれる場合、2回目にいずれかのパッケージを再コンパイルする必要があります。)
返されるオブジェクトに対して、より詳細な制御を行うには、SET_FILTERプロシージャを使用して特定のメンバー型にのみフィルタを適用するよう指定します。これを行うには、SET_FILTERの4つ目のパラメータとして、メンバー型のパス名を指定します。また、EXCLUDE_PATH_EXPRフィルタを使用して、オブジェクト型のすべてのオブジェクトを除外できます。有効なパス名のリストは、TABLE_EXPORT_OBJECTSカタログ・ビューを参照してください。
例20-9に、返されるオブジェクトに対して、より詳細な制御を指定するためのSET_FILTERの使用方法を示します。ユーザーscottとして接続します。パスワードは、tigerです。
例20-9 異種オブジェクト型の返りのフィルタ
取得したオブジェクトを格納するための表my_metadataを作成します。さらに、プロシージャget_schema_md2を作成します。
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md2 IS
-- Define local variables.
h NUMBER; -- handle returned by 'OPEN'
th NUMBER; -- handle returned by 'ADD_TRANSFORM'
doc CLOB; -- metadata is returned in a CLOB
BEGIN
-- Specify the object type.
h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
-- Use the fourth parameter to SET_FILTER to specify a filter
-- that applies to a specific member object type.
DBMS_METADATA.SET_FILTER(h,'NAME_EXPR','!=''MY_METADATA''','TABLE');
-- Use the EXCLUDE_PATH_EXPR filter to exclude procedures.
DBMS_METADATA.SET_FILTER(h,'EXCLUDE_PATH_EXPR','=''PROCEDURE''');
-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
-- Use the fourth parameter to SET_TRANSFORM_PARAM to specify a parameter
-- that applies to a specific member object type.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false,'TABLE');
-- Fetch the objects.
LOOP
doc := dbms_metadata.fetch_clob(h);
-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;
-- Store the metadata in the table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;
-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/
次のようにプロシージャを実行します。
EXECUTE get_schema_md2;
次の問合せを実行して、取得された内容を確認します。
SET LONG 9000000 SET PAGESIZE 0 SELECT * FROM my_metadata;
この項では、DBMS_METADATAおよびDBMS_METADATA_DIFFの取得インタフェース、比較インタフェースおよび送信インタフェースの使用例を示します。この例では、2つの表のメタデータをフェッチして比較し、1つの表を他方と同じ内容に変更するALTER文を生成します。わかりやすくするために、この例全体でファンクションの変形を使用しています。
例20-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.
この項では、DBMS_METADATA APIのプログラム・インタフェース使用時にパフォーマンスを向上する方法について説明します。
ある型のすべてのオブジェクトを、次の型のオブジェクトをフェッチする前にフェッチします。たとえば、スキーマ内に含まれるすべてのオブジェクトの定義を取得する場合は、まずすべての表をフェッチし、次にすべての索引をフェッチし、その次にすべてのトリガーをフェッチします。この方法は、OPENコンテキストをネストする方法(表をフェッチした後でそのすべての索引、権限およびトリガーをフェッチし、その後、次の表をフェッチした後でそのすべての索引、権限およびトリガーをフェッチするという方法)より非常に高速です。「DBMS_METADATA APIの使用例」では、この非効率な後者の方法が示されていますが、その例の目的はほとんどのプログラム・コールを示すことであり、この方法がその目的に最適です。
SET_COUNTプロシージャを使用して、複数のオブジェクトを一度に取得します。これによってサーバーのラウンドトリップが最小化され、多くの冗長なファンクション・コールが削減されます。
DBMS_METADATA APIをコールするPL/SQLパッケージを記述する場合、LOB変数およびLOBを含むオブジェクト(SYS.KU$_DDLSなど)は、個々のファンクション内ではなく、パッケージ・スコープで宣言します。これによって、ファンクションの開始および終了時に、負荷の高い操作である、LOBの存続時間構造の作成および削除が実行されなくなります。
|
参照: Oracle Database SecureFiles and Large Objects開発者ガイド |
この項では、DBMS_METADATA APIの使用例を示します。デモを自動的に実行するスクリプトの手順は、次のとおりです。
スキーマ(MDDEMO)および何人かの従業員名簿のユーザーを確立します。
スキーマ内に従業員名簿に似せた表を3つ作成し、関連する索引、トリガーおよび権限を作成します。
DBMS_METADATA APIを使用するPAYROLL_DEMOというパッケージを作成します。PAYROLL_DEMOパッケージには、PAYROLLから始まるMDDEMOスキーマ内の2つの表のDDLを取得するGET_PAYROLL_TABLESというプロシージャを含めます。各表に対して、表の関連する依存オブジェクト、索引、権限およびトリガーのDDLを取得します。すべてのDDLは、MDDEMO.DDLという名前の表に書き込まれます。
例を実行するには、次の手順に従います。
ユーザーsystemとしてSQL*Plusを起動します。パスワードを入力するように要求されます。
sqlplus system
デモをインストールします。このデモは、rdbms/demoにあるファイルmddemo.sqlに含まれています。
SQL> @mddemo
この手順で行われる処理の詳細は、「DBMS_METADATAの例で行われる処理」を参照してください。
ユーザーmddemoとして接続します。パスワードを入力するように要求されます。パスワードもmddemoです。
SQL> CONNECT mddemo Enter password:
問合せの出力を完全なものにして読取り可能にするために、次のパラメータを設定します
SQL> SET PAGESIZE 0 SQL> SET LONG 1000000
次のとおりGET_PAYROLL_TABLESプロシージャを実行します。
SQL> CALL payroll_demo.get_payroll_tables();
次のSQL問合せを実行します。
SQL> SELECT ddl FROM DDL ORDER BY SEQNO;
生成される出力は、GET_PAYROLL_TABLESプロシージャの実行結果です。デモのインストール時に、手順2で行われたすべてのDDLが示されます。実際の出力のリストについては、「GET_PAYROLL_TABLESプロシージャで生成される出力」を参照してください。
mddemoスクリプトを実行すると、次の手順が行われます。これらの手順をユーザーの状況に合わせることができます。
ユーザーが存在する場合は、次のとおり削除します。これで、新しいデータを使用した作業が開始できます。ユーザーが存在しない場合は、その影響を示すメッセージが表示されますが、問題はありません。デモは継続して実行されます。
CONNECT system
Enter password: password
SQL> DROP USER mddemo CASCADE;
SQL> DROP USER mddemo_clerk CASCADE;
SQL> DROP USER mddemo_mgr CASCADE;
mddemoで識別されるユーザーmddemoを作成します。
SQL> CREATE USER mddemo IDENTIFIED BY mddemo; SQL> GRANT resource, connect, create session, 1 create table, 2 create procedure, 3 create sequence, 4 create trigger, 5 create view, 6 create synonym, 7 alter session, 8 TO mddemo;
clerkで識別されるユーザーmddemo_clerkを作成します。
CREATE USER mddemo_clerk IDENTIFIED BY clerk;
mgrで識別されるユーザーmddemo_mgrを作成します。
CREATE USER mddemo_mgr IDENTIFIED BY mgr;
mddemoとしてSQL*Plusに接続します(パスワードもmddemoです)。
CONNECT mddemo Enter password:
いくつかの従業員名簿型の表を作成します。
SQL> CREATE TABLE payroll_emps 2 ( lastname VARCHAR2(60) NOT NULL, 3 firstname VARCHAR2(20) NOT NULL, 4 mi VARCHAR2(2), 5 suffix VARCHAR2(10), 6 dob DATE NOT NULL, 7 badge_no NUMBER(6) PRIMARY KEY, 8 exempt VARCHAR(1) NOT NULL, 9 salary NUMBER (9,2), 10 hourly_rate NUMBER (7,2) ) 11 / SQL> CREATE TABLE payroll_timecards 2 (badge_no NUMBER(6) REFERENCES payroll_emps (badge_no), 3 week NUMBER(2), 4 job_id NUMBER(5), 5 hours_worked NUMBER(4,2) ) 6 /
audit_trailというダミーの表を作成します。この表は、payrollで始まらない表は、GET_PAYROLL_TABLESプロシージャでは取得されないことを示すために使用します。
SQL> CREATE TABLE audit_trail 2 (action_time DATE, 3 lastname VARCHAR2(60), 4 action LONG ) 5 /
作成された表の権限を作成します。
SQL> GRANT UPDATE (salary,hourly_rate) ON payroll_emps TO mddemo_clerk; SQL> GRANT ALL ON payroll_emps TO mddemo_mgr WITH GRANT OPTION; SQL> GRANT INSERT,UPDATE ON payroll_timecards TO mddemo_clerk; SQL> GRANT ALL ON payroll_timecards TO mddemo_mgr WITH GRANT OPTION;
作成された表の索引を作成します。
SQL> CREATE INDEX i_payroll_emps_name ON payroll_emps(lastname); SQL> CREATE INDEX i_payroll_emps_dob ON payroll_emps(dob); SQL> CREATE INDEX i_payroll_timecards_badge ON payroll_timecards(badge_no);
作成された表のトリガーを作成します。
SQL> CREATE OR REPLACE PROCEDURE check_sal( salary in number) AS BEGIN 2 RETURN; 3 END; 4 /
例を簡単にするために、セキュリティは比較的低く設定しています。
SQL> CREATE OR REPLACE TRIGGER salary_trigger BEFORE INSERT OR UPDATE OF salary ON payroll_emps FOR EACH ROW WHEN (new.salary > 150000) CALL check_sal(:new.salary) / SQL> CREATE OR REPLACE TRIGGER hourly_trigger BEFORE UPDATE OF hourly_rate ON payroll_emps FOR EACH ROW BEGIN :new.hourly_rate:=:old.hourly_rate;END; /
生成されたDDLを保持する表を設定します。
CREATE TABLE ddl (ddl CLOB, seqno NUMBER);
DBMS_METADATAプロシージャを使用できる例を提供するPAYROLL_DEMOパッケージを作成します。
SQL> CREATE OR REPLACE PACKAGE payroll_demo AS PROCEDURE get_payroll_tables; END; /
|
注意: PAYROLL_DEMOパッケージの内容を含めて、この例のスクリプト全体を参照するには、$ORACLE_HOME/rdbms/demoディレクトリにあるファイルmddemo.sqlを参照してください。 |
mddemo.payroll_demo.get_payroll_tables プロシージャの実行後に、次の問合せを実行できます。
SQL> SELECT ddl FROM ddl ORDER BY seqno;
結果は次のとおりです。前述の項で説明したとおり、スクリプトによって実行されたすべてのDDLが反映されます。
CREATE TABLE "MDDEMO"."PAYROLL_EMPS"
( "LASTNAME" VARCHAR2(60) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,
"MI" VARCHAR2(2),
"SUFFIX" VARCHAR2(10),
"DOB" DATE NOT NULL ENABLE,
"BADGE_NO" NUMBER(6,0),
"EXEMPT" VARCHAR2(1) NOT NULL ENABLE,
"SALARY" NUMBER(9,2),
"HOURLY_RATE" NUMBER(7,2),
PRIMARY KEY ("BADGE_NO") ENABLE
) ;
GRANT UPDATE ("SALARY") ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_CLERK";
GRANT UPDATE ("HOURLY_RATE") ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_CLERK";
GRANT ALTER ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT DELETE ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT INDEX ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT INSERT ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT SELECT ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT UPDATE ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT REFERENCES ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT ON COMMIT REFRESH ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT QUERY REWRITE ON "MDDEMO"."PAYROLL_EMPS" TO "MDDEMO_MGR" WITH GRANT OPTION;
CREATE INDEX "MDDEMO"."I_PAYROLL_EMPS_DOB" ON "MDDEMO"."PAYROLL_EMPS" ("DOB")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;
CREATE INDEX "MDDEMO"."I_PAYROLL_EMPS_NAME" ON "MDDEMO"."PAYROLL_EMPS" ("LASTNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;
CREATE OR REPLACE TRIGGER hourly_trigger before update of hourly_rate on payroll_emps
for each row
begin :new.hourly_rate:=:old.hourly_rate;end;
/
ALTER TRIGGER "MDDEMO"."HOURLY_TRIGGER" ENABLE;
CREATE OR REPLACE TRIGGER salary_trigger before insert or update of salary on payroll_emps
for each row
WHEN (new.salary > 150000) CALL check_sal(:new.salary)
/
ALTER TRIGGER "MDDEMO"."SALARY_TRIGGER" ENABLE;
CREATE TABLE "MDDEMO"."PAYROLL_TIMECARDS"
( "BADGE_NO" NUMBER(6,0),
"WEEK" NUMBER(2,0),
"JOB_ID" NUMBER(5,0),
"HOURS_WORKED" NUMBER(4,2),
FOREIGN KEY ("BADGE_NO")
REFERENCES "MDDEMO"."PAYROLL_EMPS" ("BADGE_NO") ENABLE
) ;
GRANT INSERT ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_CLERK";
GRANT UPDATE ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_CLERK";
GRANT ALTER ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT DELETE ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT INDEX ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT INSERT ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT SELECT ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT UPDATE ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT REFERENCES ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT ON COMMIT REFRESH ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
GRANT QUERY REWRITE ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT OPTION;
CREATE INDEX "MDDEMO"."I_PAYROLL_TIMECARDS_BADGE" ON "MDDEMO"."PAYROLL_TIMECARDS" ("BADGE_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ;
この項では、DBMS_METADATA APIによって提供されるプロシージャを簡単に説明します。これらのプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
表20-1では、複数オブジェクトの取得に使用する、DBMS_METADATAプログラム・インタフェースのプロシージャを簡単に説明します。
表20-1 複数オブジェクトの取得に使用するDBMS_METADATAプロシージャ
| PL/SQLプロシージャ名 | 説明 |
|---|---|
DBMS_METADATA.OPEN() |
取得するオブジェクトの型、メタデータのバージョンおよびオブジェクト・モデルを指定します。 |
DBMS_METADATA.SET_FILTER() |
取得するオブジェクトに、オブジェクト名、スキーマなどの制限を指定します。 |
DBMS_METADATA.SET_COUNT() |
1回の |
DBMS_METADATA.GET_QUERY() |
|
DBMS_METADATA.SET_PARSE_ITEM() |
解析して返すオブジェクトの属性を指定して、出力を解析に利用します。 |
DBMS_METADATA.ADD_TRANSFORM() |
取得したオブジェクトのXML表現に、 |
DBMS_METADATA.SET_TRANSFORM_PARAM() |
|
DBMS_METADATA.SET_REMAP_PARAM() |
|
DBMS_METADATA.FETCH_xxx() |
|
DBMS_METADATA.CLOSE() |
|
表20-2では、DBMS_METADATAブラウザ・インタフェースで提供されるプロシージャを簡単に説明します。これらのファンクションは、1つ以上の依存オブジェクトまたは権限付与されたオブジェクトのメタデータを返します。これらのプロシージャでは、異種オブジェクト型はサポートされません。
表20-2 ブラウザ・インタフェースで使用するDBMS_METADATAプロシージャ
| PL/SQLプロシージャ名 | 説明 |
|---|---|
DBMS_METADATA.GET_xxx() |
シングル・オブジェクトに対してメタデータを返す方法を提供します。各
変換を指定した場合、セッション・レベルの変換フラグが継承されます。 |
DBMS_METADATA.GET_DEPENDENT_xxx() |
XMLまたはDDLで指定したとおり、1つ以上の依存オブジェクトに対してメタデータが返されます。 |
DBMS_METADATA.GET_GRANTED_xxx() |
XMLまたはDDLで指定したとおり、権限が付与された1つ以上のオブジェクトに対してメタデータが返されます。 |
表20-3では、XMLの送信で使用されるDBMS_METADATAプロシージャおよびファンクションを簡単に説明します。
表20-3 XMLデータの送信に使用するDBMS_METADATAのプロシージャおよびファンクション
| PL/SQL名 | 説明 |
|---|---|
DBMS_METADATA.OPENW() |
書込みコンテキストをオープンします。 |
DBMS_METADATA.ADD_TRANSFORM() |
XML文書の変換を指定します。 |
DBMS_METADATA.SET_TRANSFORM_PARAM() and DBMS_METADATA.SET_REMAP_PARAM() |
|
DBMS_METADATA.SET_PARSE_ITEM() |
解析するオブジェクト属性を指定します。 |
DBMS_METADATA.CONVERT() |
XML文書をDDLに変換します。 |
DBMS_METADATA.PUT() |
データベースにXML文書を送信します。 |
DBMS_METADATA.CLOSE() |
|
この項では、DBMS_METADATA_DIFF APIによって提供されるプロシージャおよびファンクションを簡単に説明します。これらのプロシージャの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
