Example Usage of the DBMS_METADATA API

This section provides an example of how the DBMS_METADATA API could be used. A script is provided that automatically runs the demo for you by performing the following actions:

• Establishes a schema (MDDEMO) and some payroll users.

• Creates three payroll-like tables within the schema and any associated indexes, triggers, and grants.

• Creates a package, PAYROLL_DEMO, that uses the DBMS_METADATA API. The PAYROLL_DEMO package contains a procedure, GET_PAYROLL_TABLES, that retrieves the DDL for the two tables in the MDDEMO schema that start with PAYROLL. For each table, it retrieves the DDL for the table's associated dependent objects; indexes, grants, and triggers. All the DDL is written to a table named MDDEMO.DDL.

To execute the example, do the following:

1. Start SQL*Plus as user system. You will be prompted for a password.

   sqlplus system
   

2. Install the demo, which is located in the file mddemo.sql in rdbms/demo:

   SQL> @mddemo
   

   For an explanation of what happens during this step, see "What Does the DBMS_METADATA Example Do?".

3. Connect as user mddemo. You will be prompted for a password, which is also mddemo.

   SQL> CONNECT mddemo
   Enter password:
   

4. Set the following parameters so that query output will be complete and readable:

   SQL> SET PAGESIZE 0
   SQL> SET LONG 1000000
   

5. Execute the GET_PAYROLL_TABLES procedure, as follows:

   SQL> CALL payroll_demo.get_payroll_tables();
   

6. Execute the following SQL query:

   SQL> SELECT ddl FROM DDL ORDER BY SEQNO;
   

   The output generated is the result of the execution of the GET_PAYROLL_TABLES procedure. It shows all the DDL that was performed in Step 2 when the demo was installed. See "Output Generated from the GET_PAYROLL_TABLES Procedure " for a listing of the actual output.