Oracle Databaseユーティリティ 11g リリース1(11.1) E05768-02 |
|
この章では、次の作業を行うためのメタデータApplication Programming Interface(API)について説明します。
この章の内容は、次のとおりです。
Oracle Databaseの長期の使用に伴い、ディレクトリからメタデータを抽出し、そのメタデータに対し列の追加やデータ型の変更などを行い、さらにDDLに変換して元のまたは異なるデータベースのオブジェクトを再作成するために、ユーザーが独自のコードを開発する機会が多くなりました。ディクショナリの新機能をサポートするよう、そのコードを更新された状態に保つことは困難です。
メタデータAPIを使用すると、メタデータを抽出するためにコードを記述して管理する必要がなくなります。メタデータAPIは、ディクショナリ・メタデータの抽出、操作および再送信に対する集中的な機能を提供します。また、すべてのディクショナリ・オブジェクトを最新のレベルでサポートしています。
メタデータAPIは、記述および管理するカスタム・コードの量を大幅に削減しますが、通常のデータベース手順の変更はありません。メタデータAPIは、データ・ディクショナリ・ビューと同様、データベースのインストール時にcatproc
.sql
を実行し、SQLスクリプトを起動してインストールします。インストール後は、制限モードであっても、インスタンスが動作中はいつでも使用可能です。
メタデータAPIは、異なるOracleのバージョン間で上位互換性があるため、データベースのバージョンを変更しても、ソース・コードの変更は必要ありません。1つのバージョンで取得したXML文書を、同じまたはそれ以上のバージョンの送信インタフェースで処理できます。たとえば、Oracle9i で取得したXML文書は、Oracle Database 10g に送信できます。
メタデータAPIにおいては、データベース内のすべてのエンティティは、オブジェクト型に属するオブジェクトとして形成されています。たとえば、scott.emp
表はオブジェクトであり、オブジェクト型はTABLE
です。オブジェクトのメタデータをフェッチする場合は、オブジェクト型を指定する必要があります。
オブジェクト型の特定のオブジェクトまたは一連のオブジェクトをフェッチするには、フィルタを指定します。各オブジェクト型に対して異なるフィルタが定義されます。たとえば、TABLE
オブジェクト型に対して定義されている2つのフィルタはSCHEMA
およびNAME
です。これにより、たとえば、スキーマがscott
で名前がemp
である表を必要としていることを表現できます。
メタデータAPIは、XML(Extensible Markup Language)およびXSLT(Extensible Stylesheet Language Transformation)を使用します。XMLが解析や変換が容易な汎用形式であるため、メタデータAPIは、オブジェクト・メタデータをXMLとして表現します。メタデータAPIは、XSLTを使用してXML文書を他のXML文書またはSQL DDLのいずれかに変換します。
メタデータAPIを使用して、メタデータのフェッチの際(または再送信の際)に、1つ以上の変換(XSLTスクリプト)をXMLに適用するよう指定できます。APIには、XML文書をSQL作成DDLに変換する名前付きDDL以外にも、いくつかの事前定義変換があります。
次に変換パラメータを使用して、変換の条件を指定できます。また、オブジェクトのメタデータの特定の属性にアクセスするために、オプションの解析項目も指定できます。これらのオプションの詳細と実装例については、次の項を参照してください。
メタデータAPIの取得インタフェースでは、取得するオブジェクトの種類を指定できます。種類は、特定のオブジェクト型(表、索引、プロシージャなど)または論理単位を形成する異なるオブジェクト型のコレクション(データベース・エクスポート、スキーマ・エクスポートなど)のいずれかです。デフォルトでは、フェッチされたメタデータがXML文書に返されます。
プログラム・インタフェースを使用して、簡単な参照を実行したり、アプリケーションを開発できます。システム・メタデータの非定型の問合せを行う場合は、ブラウザ・インタフェースを使用します。アプリケーションの一部として、ディクショナリ・メタデータを抽出する場合は、プログラム・インタフェースを使用します。その場合、同じことをするために使用しているSQLスクリプトおよびカスタマイズされたコードのかわりに、メタデータAPIで提供されているプロシージャを使用することもできます。
メタデータを取得する場合、メタデータAPIのプロシージャを含むDBMS_METADATA
PL/SQLパッケージを使用します。次に、プログラム・インタフェースおよびブラウザ・インタフェースの例を示します。
例19-1では、メタデータAPIプログラム・インタフェースを使用して1つの表のメタデータを取得する基本的な方法を説明します。get_table_md
というファンクションを作成するメタデータAPIを作成します。ファンクションは、1つの表のメタデータを返します。
hr
スキーマ内の1つの表(timecards
)のメタデータを返すファンクションget_table_md
を作成するメタデータAPIプログラムを作成します。プログラムの内容は次のようになります。(この例ではプログラムに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"
例19-2に示すとおり、ブラウザ・インタフェースを使用しても同じ結果を得られます。
SQL> SET PAGESIZE 0 SQL> SET LONG 1000000 SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TIMECARDS','HR') FROM dual;
結果は、例19-1の手順5と同様になります。
例19-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;
例19-3に、構成メンバーの使用および複数のオブジェクトの取得を示します。この例では、ユーザーscott
として接続します。パスワードは、tiger
です。
-- Because not all objects can be returned, they are stored in a table and queried at the end. 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 the procedure. EXECUTE get_tables_md; -- See what was retrieved. SET LONG 9000000 SET PAGES 0 SELECT * FROM my_metadata;
変換パラメータを使用して、追加する変換の条件を指定できます。SET_TRANSFORM_PARAM
プロシージャを使用します。たとえば、TABLE
オブジェクトに対してDDL
変換を追加した場合、SEGMENT_ATTRIBUTES
変換パラメータを指定して、物理、ストレージ、ロギングなどのセグメント属性をDLLに表示しないようにすることができます。デフォルトでは、セグメント属性がDDLに表示されます。
例19-4に、SET_TRANSFORM_PARAM
プロシージャの使用方法を示します。
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つの変換を使用しました。メタデータAPIでは、複数の変換を指定できます。その場合、最初の出力が2番目の入力になり、2番目の出力が3番目の入力になります。
Oracleには、XML文書を変更するMODIFY
と呼ばれる変換があります。スキーマ名や表領域名の変更などが行えます。これには、再マップ・パラメータおよびSET_REMAP_PARAM
プロシージャを使用します。
例19-5に、SET_REMAP_PARAM
プロシージャの使用例を示します。まずMODIFY
変換を追加し、再マップ・パラメータを指定して、スキーマ名をhr
からscott
に変更します。次にDDL
変換を追加します。MODIFY
変換の出力は、DDL
変換への入力となるXML文書です。最終的な結果は、timecards
表の作成DDLであり、hr
スキーマのすべてのインスタンスはscott
に変更されます。
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; /
SQL文(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を処理できます。
名前やスキーマなどのオブジェクトのメタデータの特定の属性へアクセスすることが必要な場合もあります。属性の情報は返されたメタデータを解析し取得できますが、メタデータAPIには、別の機能もあります。メタデータから解析され、別のデータ構造に返される特定の属性を解析項目に指定できます。これを行うには、SET_PARSE_ITEM
プロシージャを使用します。
例19-6では、スキーマ内のすべての表をフェッチします。各表では、名前を取得するために解析項目を使用します。次に表のすべての索引を取得するために名前を使用します。次の例に、sys
.ku$_ddls
オブジェクトのメタデータを返すFETCH_DDL
ファンクションの使用を示します。
この例では、いくつかの表および索引を含むスキーマに接続していることとします。また、my_metadata
という名前の表も作成します。
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 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 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); -- Set up the inner loop: fetch the INDEX objects. LOOP DDL := DBMS_METADATA.FETCH_CLOB(h2); -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL. EXIT WHEN ddl IS NULL; -- Store the metadata in our table. INSERT INTO my_metadata(object_type, name, md) VALUES ('INDEX',NULL,ddl); COMMIT; END LOOP; DBMS_METADATA.CLOSE(h2); END LOOP; DBMS_METADATA.CLOSE(h1); END; / -- Execute the procedure. EXECUTE get_tables_and_indexes; -- Perform a query to check what was retrieved. SET LONG 9000000 SET PAGES 0 SELECT * FROM my_metadata;
オブジェクトに対しメタデータをフェッチする場合、そのメタデータを使用して、オブジェクトを異なるデータベースまたはスキーマで再作成することがあります。
メタデータをフェッチする際に、再マップの実行を決定していない場合もあります。また、この決定を先送りにしたい場合があります。再マップを先送りにするには、メタデータをXMLとしてフェッチし、ファイルまたは表に格納します。後で送信インタフェースを使用しオブジェクトを再作成します。
送信インタフェースは取得インタフェースに類似した構成です。送信インタフェースには、作成するオブジェクトのオブジェクト型を指定するOPENW
プロシージャが存在します。変換の指定、パラメータの変換および項目の解析ができます。CONVERT
ファンクションをコールしてXMLからDDLに変換する、またはPUT
ファンクションをコールしてXMLをDDLに変換し、DDLを送信してオブジェクトを作成できます。
例19-7は、1つのスキーマの表のXMLをフェッチし、次に送信インタフェースを使用して別のスキーマの表を再作成します。
-- Connect as a privileged user. CONNECT system Enter password: password -- Create an invoker's rights package to hold the procedure -- because access to objects in another schema requires the -- SELECT_CATALOG_ROLE role. In a definer's rights PL/SQL object -- (such as a procedure or function), roles are disabled. 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; / -- Now try it: create a table in SCOTT... 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 -- ...and copy it to SYSTEM. DROP TABLE my_example; EXECUTE example_pkg.move_table('MY_EXAMPLE','SCOTT','SYSTEM'); -- Verify that it worked. SELECT DBMS_METADATA.GET_DDL('TABLE','MY_EXAMPLE') FROM dual;
異なる型のオブジェクトであるにもかかわらず、1つの論理単位を形成するオブジェクトのコレクションを取得する必要がある場合があります。たとえば、データベースまたはスキーマのすべてのオブジェクト、または表および依存する索引、制約、権限付与、監査などすべてを取得する必要があるとします。メタデータAPIでは、こうした取得を可能にするために、複数の異種オブジェクト型を提供しています。異種オブジェクト型は、オブジェクト型の順序付けられた集合です。
Oracleでは、いくつかの異種オブジェクト型を提供しています。
これらのオブジェクト型は、データ・ポンプ・エクスポート・ユーティリティで使用するために開発されましたが、ユーザー独自のアプリケーションでも使用できます。
これらの型では、ブラウザ・インタフェースまたは送信インタフェースではなく、プログラム取得インタフェース(OPEN
, FETCH
, CLOSE
)のみが使用できます。
同種型と同様に、異種オブジェクト型に対してもフィルタを指定できます。たとえば、TABLE_EXPORT
に対してSCHEMA
およびNAME
フィルタを指定でき、SCHEMA_EXPORT
に対してSCHEMA
フィルタを指定できます。
例19-8に、scott
スキーマ内のオブジェクト型を取得する方法を示します。ユーザーscott
として接続します。パスワードは、tiger
です。
-- Create a table to store the retrieved objects. 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 the procedure. EXECUTE get_schema_md; -- See what was retrieved. 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
カタログ・ビューを参照してください。
例19-9に、返されるオブジェクトに対して、より詳細な制御を指定するためのSET_FILTER
の使用方法を示します。ユーザーscott
として接続します。パスワードは、tiger
です。
-- Create a table to store the retrieved objects. 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 the procedure. EXECUTE get_schema_md2; -- See what was retrieved. SET LONG 9000000 SET PAGESIZE 0 SELECT * FROM my_metadata;
この項では、メタデータAPIのプログラム・インタフェース使用時にパフォーマンスを向上する方法について説明します。
OPEN
コンテキストをネストする方法(表をフェッチした後でそのすべての索引、権限およびトリガーをフェッチし、その後、次の表をフェッチした後でそのすべての索引、権限およびトリガーをフェッチするという方法)より非常に高速です。「メタデータAPIの使用例」では、この非効率な後者の方法が示されていますが、その例の目的は多くのプログラム・コールを示すことであり、その目的には、この方法が最適であるためです。
SET_COUNT
プロシージャを使用して、複数のオブジェクトを一度に取得します。これによってサーバーのラウンドトリップが最小化され、多くの冗長なファンクション・コールが削減されます。
SYS
.KU
$_DDLS
など)は、個々のファンクション内ではなく、パッケージ・スコープで宣言します。これによって、ファンクションの開始および終了時に、負荷の高い操作である、LOBの存続時間構造の作成および削除が実行されなくなります。この項では、メタデータAPIの使用例を示します。デモを自動的に実行するスクリプトの手順は、次のとおりです。
MDDEMO
)および何人かの従業員名簿のユーザーを確立します。
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
この手順で行われる処理の詳細は、「メタデータAPIの例で行われる処理」を参照してください。
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> 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; /
CREATE TABLE ddl (ddl CLOB, seqno NUMBER);
DBMS_METADATA
プロシージャを使用できる例を提供するPAYROLL_DEMO
パッケージを作成します。
SQL> CREATE OR REPLACE PACKAGE payroll_demo AS PROCEDURE get_payroll_tables; END; /
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" ;
この項では、メタデータAPIによって提供されるプロシージャを簡単に説明します。プロシージャの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
表19-1では、複数オブジェクトの取得に使用する、DBMS_METADATA
プログラム・インタフェースのプロシージャを簡単に説明します。
表19-2では、DBMS_METADATA
ブラウザ・インタフェースで提供されるプロシージャを簡単に説明します。これらのファンクションは、1つ以上の依存オブジェクトまたは権限付与されたオブジェクトのメタデータを返します。これらのプロシージャでは、異種オブジェクト型はサポートされません。
表19-3では、XMLの送信で使用されるDBMS_METADATA
プロシージャおよびファンクションを簡単に説明します。
|
![]() Copyright © 2007 Oracle Corporation. All Rights Reserved. |
|