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
To execute the example, do the following:
Start SQL*Plus as user
system. You will be prompted for a password.
Install the demo, which is located in the file
For an explanation of what happens during this step, see "What Does the DBMS_METADATA Example Do?".
Connect as user
mddemo. You will be prompted for a password, which is also
SQL> CONNECT mddemo Enter password:
Set the following parameters so that query output will be complete and readable:
SQL> SET PAGESIZE 0 SQL> SET LONG 1000000
GET_PAYROLL_TABLES procedure, as follows:
SQL> CALL payroll_demo.get_payroll_tables();
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.