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 LONG 1000000
  5. Execute the GET_PAYROLL_TABLES procedure, as follows:

    SQL> CALL payroll_demo.get_payroll_tables();
  6. Execute the following SQL query:


    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.