What Does the DBMS_METADATA Example Do?

When the mddemo script is run, the following steps take place. You can adapt these steps to your own situation.

1. Drops users as follows, if they exist. This will ensure that you are starting out with fresh data. If the users do not exist, then a message to that effect is displayed, no harm is done, and the demo continues to execute.

   CONNECT system
   Enter password: password
   SQL> DROP USER mddemo CASCADE;
   SQL> DROP USER mddemo_clerk CASCADE;
   SQL> DROP USER mddemo_mgr CASCADE;
   

2. Creates user mddemo, identified by 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;
   

3. Creates user mddemo_clerk, identified by clerk:

   CREATE USER mddemo_clerk IDENTIFIED BY clerk;
   

4. Creates user mddemo_mgr, identified by mgr:

   CREATE USER mddemo_mgr IDENTIFIED BY mgr;
   

5. Connect to SQL*Plus as mddemo (the password is also mddemo):

   CONNECT mddemo
   Enter password:
   

6. Creates some payroll-type tables:

   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 /
   

7. Creates a dummy table, audit_trail. This table is used to show that tables that do not start with payroll are not retrieved by the GET_PAYROLL_TABLES procedure.

   SQL> CREATE TABLE audit_trail 
     2  (action_time DATE,
     3  lastname VARCHAR2(60),
     4  action LONG )
     5  /
   

8. Creates some grants on the tables just created:

   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;
   

9. Creates some indexes on the tables just created:

   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);
   

10. Creates some triggers on the tables just created:

    SQL> CREATE OR REPLACE PROCEDURE check_sal( salary in number) AS BEGIN
      2  RETURN;
      3  END;
      4  /
    

    Note that the security is kept fairly loose to keep the example simple.

    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;
    /
    

11. Sets up a table to hold the generated DDL:

    CREATE TABLE ddl (ddl CLOB, seqno NUMBER);
    

12. Creates the PAYROLL_DEMO package, which provides examples of how DBMS_METADATA procedures can be used.

    SQL> CREATE OR REPLACE PACKAGE payroll_demo AS PROCEDURE get_payroll_tables;
    END;
    /
    

    Note:

    To see the entire script for this example, including the contents of the PAYROLL_DEMO package, see the file mddemo.sql located in your $ORACLE_HOME/rdbms/demo directory.