15
Using the Metadata API

This chapter describes the Metadata application programming interface (API), which you can use to extract and manipulate complete representations of the metadata for database objects. The following topics are discussed in this chapter:

Introduction to the Metadata API

The Metadata API provides a centralized, simple, and flexible means for performing the following tasks:

Extracting complete definitions of database objects (metadata) as either XML or creation DDL
Transforming the metadata through industry-standard XSLT (Extensible Stylesheet Language Transformation).
Generating SQL DDL to re-create the database objects

The Metadata API is available as of Oracle9i release 9.0.1, whenever the instance is operational. It is not available in Oracle Lite.

Previous Methods Used to Extract Metadata

An object's metadata is distributed in normalized fashion across the database dictionary. In prior releases, you first had to understand how and where your object's metadata was represented in the dictionary, then you had to issue multiple queries to extract the object's full representation. Once the metadata was extracted, you would typically perform the following tasks:

Transform it in some way, such as changing the object's tablespace, changing a column datatype, changing an object's owner, and so on.
Convert it to SQL DDL text for execution on the source or some other database.

Before Oracle9i release 9.0.1, there was no assistance for either of these steps.

Metadata API Components

Underlying the Metadata API is an object model of the Oracle database dictionary consisting of a series of user-defined types (UDTs) and corresponding object views. The UDTs provide the aggregation of each object class's metadata, and the object views map the UDTs' attributes onto the appropriate base relational tables in the dictionary. The Metadata API generates queries against these object views to retrieve aggregated database object definitions.

The results of these queries are converted into XML documents by the XML SQL Utility (XSU), which was also introduced in Oracle9i release 9.0.1. When the caller requests DDL output, the Metadata API uses the appropriate implementation of the Oracle server's integral XML parser and XSL processor to convert the XML documents into creation DDL.

Metadata API Features

The Metadata API has the following features:

Provides a powerful PL/SQL interface for detailed programmatic control or casual browsing.
Supports retrieval of complete, aggregated database definitions for all dictionary objects that can be created with a SQL CREATE statement. For a full list of object types, see the DBMS_METADATA chapter in Oracle9i Supplied PL/SQL Packages and Types Reference.
Provides only complete representations of objects.

Note:
Subsetting of object attributes is not supported in this release except through XSLT transformation.
Provides database object metadata in an XML format that is easily transformable through XSLT by downstream processes.
Provides complete Oracle-specific creation DDL for all supported objects.
Provides flexible object selection. Can return multiple objects per query.
Supports daisy-chained transforms where the output of the first becomes the input to the second and so on.
Supports customization of DDL output through object type-specific transform parameters.

Internet Computing

The Metadata API uses two internet standards, XML and XSLT, for encoding and transforming object metadata. Use of an industry-standard format for metadata encoding (rather than a proprietary format) allows you to use standard tools to parse and transform the output.

There is currently no industry-standard XML model for database metadata, so the Metadata API uses a model optimized for generating Oracle DDL. Document element names are derived directly from attributes of the UDTs in the Oracle database dictionary model. As standard models emerge, the Metadata API will support the ability to plug them in. Older documents can be converted to alternate models with XSLT.

How Is the Metadata API Implemented?

The Metadata API is implemented using the PL/SQL DBMS_METADATA package. The DBMS_METADATA package allows you to retrieve metadata from the database dictionary. It provides a flexible and extensible means for object selection. You can use DBMS_METADATA to extract database object metadata in XML and DDL.

The DBMS_METADATA package has two types of interface:

DBMS_METADATA Programmatic Interface
DBMS_METADATA Browsing Interface

Note:
A description of the types and public interface defined by the Metadata API is in the following location:

$ORACLE_HOME/rdbms/admin/dbmsmeta.sql

DBMS_METADATA and Security

The object views of the Oracle metadata model implement security as follows:

Nonprivileged users can see the metadata of only their own objects.
SYS and users with SELECT_CATALOG_ROLE can see all objects.
Nonprivileged users can also retrieve object and system privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.
If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.
If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

DBMS_METADATA Programmatic Interface

The DBMS_METADATA programmatic interface is for fine-grained, detailed control:

The following procedures are provided: OPEN, SET_FILTER, SET_PARSE_ITEM, SET_COUNT, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx, CLOSE
Metadata is expressed as XML. This allows industry-standard metadata transformations using XSLT.
You can ask DBMS_METADATA to return metadata as DDL. The API uses XSL scripts internally to transparently perform the conversion.
You can invoke an XSL script, using either the Oracle XML parser or some third-party tool, to do an offline conversion of the XML representation.

Table 15-1 lists the procedures provided by the DBMS_METADATA programmatic interface and provides a brief description of each one. For more detailed descriptions, including syntax, see Oracle9i Supplied PL/SQL Packages and Types Reference.

Table 15-1 Procedures for the DBMS_METADATA Programmatic Interface

PL/SQL Procedure	Description
`DBMS_METADATA`.`OPEN()`	Specifies type of object to be retrieved, version of its metadata, and object model. Return value is an opaque context handle for the set of objects to be used in subsequent calls.
`BMS_METADATA`.`SET_FILTER()`	Specifies restrictions on objects to be retrieved, such as, object name or schema. Allows specification of base objects for dependent objects such as indexes and triggers.
`DBMS_METADATA`.`SET_COUNT()`	Specifies number of objects to be retrieved in a single `FETCH_xxx` call. By default, each call to `FETCH_xxx` returns one object.
`DBMS_METADATA`.`GET_QUERY()`	Returns text of query (or queries) used by `FETCH_xxx`. This text is provided to assist in debugging.
`DBMS_METADATA`.`SET_PARSE_ITEM()`	Enables output parsing and specifies an object attribute to be parsed and returned. This frees the caller from having to parse SQL DDL for key attributes.
`DBMS_METADATA`.`ADD_TRANSFORM()`	Specifies a transform that `FETCH_xxx` applies to the XML representation of retrieved objects. You can add more than one transform. By default (with no transforms added), objects are returned as XML documents. Call the `ADD_TRANSFORM` procedure to specify an XSLT script to transform the returned documents. If 'DDL' is specified, the objects' creation DDL is returned from subsequent `FETCH_xxx` calls. The `ADD_TRANSFORM` procedure returns an opaque transform handle different from that returned by `OPEN`.
`DBMS_METADATA`.`SET_TRANSFORM_PARAM()`	Specifies parameters to the XSLT stylesheet identified by the transform_handle returned from the `ADD_TRANSFORM` procedure. For the DDL transform, these parameters alter the form of the DDL. For example, constraints may be requested as column constraints or `ALTER` `TABLE` statements.
`DBMS_METADATA`.`FETCH_xxx()`	The `FETCH_xxx` routines return metadata for objects meeting the criteria established by the `OPEN`, `SET_FILTER`, `SET_COUNT`, and `ADD_TRANSFORM procedures`. `FETCH_XML` and `FETCH_DDL` return the metadata as XML and SQL DDL, respectively. The `FETCH_CLOB` routines return either XML or DDL as denoted by the transforms specified. The types used by these routines are described in Oracle9i Supplied PL/SQL Packages and Types Reference.
`DBMS_METADATA`.`CLOSE()`	Invalidates the handle returned by the `OPEN` procedure and cleans up the associated state.

Using the DBMS_METADATA.FETCH_XML Procedure

Figure 15-1 illustrates the steps in DBMS_METADATA.FETCH_XML() usage:

Open the object type using the DBMS_METADATA.OPEN() procedure. Object types that you can open include, but are not limited to, tables, indexes, types, packages, and synonyms.
Specify which objects to retrieve using the DBMS_METADATA.SET_FILTER()procedure.
Fetch the metadata of each qualifying object as an XML document using the DBMS_METADATA.FETCH_XML() procedure. The XML is processed; for example, it might be streamed to an export file.
If the result of this operation is NULL, then call the DBMS_METADATA.CLOSE()procedure.

Figure 15-1 Using DBMS_METADATA.FETCH_XML()

Text description of adxml091.gif follows

Text description of the illustration adxml091.gif

Using the DBMS_METADATA.FETCH_DDL Procedure

Figure 15-2 illustrates the steps in DBMS_METADATA.FETCH_DDL() usage:

Open the object type using the DBMS_METADATA.OPEN()procedure. Object types that you can open include, but are not limited to, tables, indexes, types, packages, and synonyms.
Specify which objects to retrieve using the DBMS_METADATA.SET_FILTER()procedure.
Specify what transforms are to be invoked on the output. Use the DBMS_METADATA.ADD_TRANSFORM() procedure to add a transform. The last transform added must be the "DDL" transform.
Use the DBMS_METADATA.SET_TRANSFORM_PARAM()procedure to customize the DDL. For example, you could use it to exclude storage clauses on table definitions. Transform parameters are specific to the object type chosen.
Fetch the DDL using the DBMS_METADATA.FETCH_DDL()procedure. An example of the DDL processing is re-creating objects in another schema or database.
If the result of this operation is NULL, then call the DBMS_METADATA.CLOSE()procedure.

Figure 15-2 Using DBMS_METADATA.FETCH_DDL()

Text description of adxml090.gif follows

Text description of the illustration adxml090.gif

Performance Tips for the Programmatic Interface of the Metadata API

This section describes how to enhance performance when using the programmatic interface of the Metadata API.

Fetch all of one type of object before fetching the next. For example, if you are retrieving the definitions of all objects in your schema, first fetch all the tables, then all the indexes, then all the triggers, and so on. This will be much faster than nesting OPEN contexts; that is, fetch one table then all of its indexes, grants, and triggers, then the next table and all of its indexes, grants, and triggers, and so on. The Metadata API Example reflects this second, less efficient means, but its purpose is to demonstrate most of the programmatic calls, which are best shown by this method.
Use the SET_COUNT procedure to retrieve more than one object at a time. This minimizes server round trips and eliminates many redundant function calls.
Use the procedure rather than function form of FETCH_CLOB. The procedure form returns the output CLOB by reference through the IN OUT NOCOPY specifier. The function form returns the output CLOB by value requiring an extra LOB copy.
When writing a PL/SQL package that calls the Metadata API, declare LOB variables and objects that contain LOBs (such as SYS.KU$_DDLS) at package scope rather than within individual functions. This eliminates the creation and deletion of LOB duration structures upon function entrance and exit, which are very expensive operations.

See Also:
Oracle9i Application Developer's Guide - Large Objects (LOBs)

DBMS_METADATA Browsing Interface

The DBMS_METADATA browsing interface is for casual use within SQL clients such as SQL*Plus. It is provided by the GET_xxx, GET_DEPENDENT_xxx, and GET_GRANTED_xxx functions.

The GET_DDL and GET_XML functions return metadata for a single named object. For example, the following query will show the DDL for all tables in the current user's schema:
```
SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;
```
The GET_DEPENDENT_XML, GET_DEPENDENT_DDL, GET_GRANTED_XML, and GET_GRANTED_DDL functions return metadata for one or more dependent or granted objects.

Table 15-2 lists the procedures provided by the DBMS_METADATA browsing interface and provides a brief description of each one. For more detailed descriptions, including syntax, see Oracle9i Supplied PL/SQL Packages and Types Reference.

Table 15-2 Procedures for the DBMS_METADATA Browsing Interface

PL/SQL Procedure Name Description

PL/SQL Procedure Name	Description
`DBMS_METADATA`.`GET_xxx()`	Provides a way to return metadata for a single object. Each `GET_xxx` call consists of an `OPEN` procedure, one or two `SET_FILTER` calls, optionally an `ADD_TRANSFORM` procedure, a `FETCH_xxx` call, and a `CLOSE` procedure. The object_type parameter has the same semantics as in the `OPEN procedure`. schema and name are used for filtering. If a transform is specified, session-level transform flags are inherited.
`DBMS_METADATA`.`GET_DEPENDENT_xxx()`	Returns the metadata for one or more dependent objects, specified as XML or DDL.
`DBMS_METADATA`.`GET_GRANTED_xxx()`	Returns the metadata for one or more granted objects, specified as XML or DDL.

DBMS_METADATA.GET_xxx()

Provides a way to return metadata for a single object. Each GET_xxx call consists of an OPEN procedure, one or two SET_FILTER calls, optionally an ADD_TRANSFORM procedure, a FETCH_xxx call, and a CLOSE procedure.

The object_type parameter has the same semantics as in the OPEN procedure. schema and name are used for filtering.

If a transform is specified, session-level transform flags are inherited.

DBMS_METADATA.GET_DEPENDENT_xxx()

Returns the metadata for one or more dependent objects, specified as XML or DDL.

DBMS_METADATA.GET_GRANTED_xxx()

Returns the metadata for one or more granted objects, specified as XML or DDL.

Example: Using the DBMS_METADATA Browsing Interface

The following SQL*Plus query will display the creation DDL for all tables in the current user's schema. To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;

Metadata API Example

The detailed Metadata API programming example in this section, PAYROLL_DEMO, retrieves the DDL for all tables in the MDDEMO schema that start with 'PAYROLL'. It then fetches the DDL for grants, indexes, and triggers defined on those tables. This script can be found in the file rdbms/demo/mddemo.sql in your Oracle home directory.

mddemo.sql

-- This script demonstrates how to use the Metadata API. It first
-- establishes a schema (MDDEMO) and some payroll users, then creates three
-- payroll-like tables within it along with associated indexes, triggers
-- and grants.

-- It then creates a package PAYROLL_DEMO that shows common usage of the
-- Metadata API. The procedure GET_PAYROLL_TABLES retrieves the DDL for the
-- two tables in this schema that start with 'PAYROLL' then for each one,
-- retrieves the DDL for its associate dependent objects; indexes, grants
-- and triggers. All the DDL is written to a table named "MDDEMO"."DDL".

-- First, Install the demo. cd to rdbms/demo:
-- > sqlplus system/manager
-- SQL> @mddemo

-- Then, run it.
-- > sqlplus mddemo/mddemo
-- SQL> set long 40000
-- SQL> set pages 0
-- SQL> call payroll_demo.get_payroll_tables();
-- SQL> select ddl from DDL order by seqno;

Rem Set up schema for demo pkg. PAYROLL_DEMO.

connect system/manager
drop user mddemo cascade;
drop user mddemo_clerk cascade;
drop user mddemo_mgr cascade;

create user mddemo identified by mddemo;
GRANT resource, connect, create session
     , create table
     , create procedure 
     , create sequence
     , create trigger
     , create view
     , create synonym
     , alter session
TO mddemo;

create user mddemo_clerk identified by clerk;
create user mddemo_mgr identified by mgr;

connect mddemo/mddemo

Rem Create some payroll-like tables...

create table payroll_emps
( lastname varchar2(60) not null,
  firstname varchar2(20) not null,
 mi varchar2(2),
 suffix varchar2(10),
 DOB date not null,
 badge_no number(6) primary key,
 exempt varchar(1) not null,
 salary number (9,2),
 hourly_rate number (7,2)
)
/
create table payroll_timecards
 badge_no number(6) references payroll_emps (badge_no),
 week number(2),
job_id number(5),
hours_worked number(4,2)
)
/
-- This is a dummy table used only to show that tables NOT starting with
-- 'PAYROLL' are NOT retrieved by payroll_demo.get_payroll_tables

create table audit_trail
(action_time DATE,
lastname VARCHAR2(60),
action LONG
)
/

Rem Then, create some grants...

grant update (salary,hourly_rate) on payroll_emps to mddemo_clerk;
grant ALL on payroll_emps to mddemo_mgr with grant option;

grant insert,update on payroll_timecards to mddemo_clerk;
grant ALL on payroll_timecards to mddemo_mgr with grant option;

Rem Then, create some indexes...

create index i_payroll_emps_name on payroll_emps(lastname);
create index i_payroll_emps_dob on payroll_emps(DOB);

create index i_payroll_timecards_badge on payroll_timecards(badge_no);

Rem Then, create some triggers (and required procedure)...

create or replace procedure check_sal( salary in number) as
begin
  return;  -- Fairly loose security here...
end;
/

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

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

--
-- Set up a table to hold the generated DDL
--
CREATE TABLE ddl (ddl CLOB, seqno NUMBER);

Rem Finally, create the PAYROLL_DEMO package itself.

CREATE OR REPLACE PACKAGE payroll_demo AS

   PROCEDURE get_payroll_tables;
END;
/
CREATE OR REPLACE PACKAGE BODY payroll_demo AS

-- GET_PAYROLL_TABLES: Fetch DDL for payroll tables and their dependent objects

PROCEDURE  get_payroll_tables IS

tableOpenHandle NUMBER;
depObjOpenHandle NUMBER;
tableTransHandle  NUMBER;
indexTransHandle NUMBER;
schemaName VARCHAR2(30);
tableName VARCHAR2(30);
tableDDLs sys.ku$_ddls;
tableDDL sys.ku$_ddl;
parsedItems   sys.ku$_parsed_items;
depObjDDL CLOB;
seqNo NUMBER := 1;

TYPE obj_array_t IS VARRAY(3) OF VARCHAR2(30);

-- Load this array with the dependent object classes to be retrieved...
obj_array obj_array_t := obj_array_t('OBJECT_GRANT', 'INDEX', 'TRIGGER');

BEGIN

-- Open a handle for tables in the current schema.
  tableOpenHandle := dbms_metadata.open('TABLE');

-- Tell mdAPI to retrieve one table at a time. This call is not actually
-- necessary since 1 is the default... just showing the call.
  dbms_metadata.set_count(tableOpenHandle, 1);

-- Retrieve tables whose name starts with 'PAYROLL'. When the filter is
-- 'NAME_EXPR', the filter value string must include the SQL operator. This
-- gives the caller flexibility to use LIKE, IN, NOT IN, subqueries, etc.
  dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''PAYROLL%''');

-- Tell the mdAPI to parse out each table's schema and name separately so we
-- can use them to set up the calls to retrieve its dependent objects.
  dbms_metadata.set_parse_item(tableOpenHandle, 'SCHEMA');
  dbms_metadata.set_parse_item(tableOpenHandle, 'NAME');

-- Add the DDL transform so we get SQL creation DDL
  tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');

-- Tell the XSL stylesheet we don't want physical storage information (storage,
-- tablespace, etc), and that we want a SQL terminator on each DDL. Notice that
-- these calls use the transform handle, not the open handle.
  dbms_metadata.set_transform_param(tableTransHandle,
     'SEGMENT_ATTRIBUTES', FALSE);
  dbms_metadata.set_transform_param(tableTransHandle,
    'SQLTERMINATOR', TRUE);

-- Ready to start fetching tables. We use the FETCH_DDL interface (rather than
-- FETCH_XML or FETCH_CLOB). This interface returns a SYS.KU$_DDLS; a table of
-- SYS.KU$_DDL objects. This is a table because some object types return
-- multiple DDL statements (like types / pkgs which have create header and 
-- body statements). Each KU$_DDL has a CLOB containing the 'CREATE foo'
-- statement plus a nested table of the parse items specified. In our case,
-- we asked for two parse items; Schema and Name. (NOTE: See admin/dbmsmeta.sql
-- for a more detailed description of these types)

  LOOP
    tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);
    EXIT WHEN tableDDLs IS NULL;  -- Get out when no more payroll tables

-- In our case, we know there is only one row in tableDDLs (a KU$_DDLS tbl obj)
-- for the current table. Sometimes tables have multiple DDL statements;
-- eg, if constraints are applied as ALTER TABLE statements, but we didn't ask
-- for that option. So, rather than writing code to loop through tableDDLs,
-- we'll just work with the 1st row.
--
-- First, write the CREATE TABLE text to our output table then retrieve the
-- parsed schema and table names.
    tableDDL := tableDDLs(1);
    INSERT INTO ddl VALUES(tableDDL.ddltext, seqNo);
    seqNo := seqNo + 1;
    parsedItems := tableDDL.parsedItems;

-- Must check the name of the returned parse items as ordering isn't guaranteed
    FOR i IN 1..2 LOOP
      IF parsedItems(i).item = 'SCHEMA'
      THEN
        schemaName := parsedItems(i).value;
      ELSE
        tableName  := parsedItems(i).value;
      END IF;
    END LOOP;
 
-- Now, we want to retrieve all the dependent objects defined on the current
-- table: indexes, triggers and grants. Since all 'dependent' object types
-- have BASE_OBJECT_NAME and BASE_OBJECT_SCHEMA in common as filter criteria,
-- we'll set up a loop to get all objects of the 3 types, just changing the
-- OPEN context in each pass through the loop. Transform parameters are
-- different for each object type, so we'll only use one that's common to all;
-- SQLTERMINATOR.

    FOR i IN 1..3 LOOP
      depObjOpenHandle := dbms_metadata.open(obj_array(i));
      dbms_metadata.set_filter(depObjOpenHandle,'BASE_OBJECT_SCHEMA',
                 schemaName);
      dbms_metadata.set_filter(depObjOpenHandle,'BASE_OBJECT_NAME',tableName);

-- Add the DDL transform and say we want a SQL terminator
      indexTransHandle := dbms_metadata.add_transform(depObjOpenHandle, 'DDL');
      dbms_metadata.set_transform_param(indexTransHandle,
        'SQLTERMINATOR', TRUE);

-- Retrieve dependent object DDLs as CLOBs and write them to table DDL.
      LOOP
        depObjDDL := dbms_metadata.fetch_clob(depObjOpenHandle);
        EXIT WHEN depObjDDL IS NULL;
        INSERT INTO ddl VALUES(depObjDDL, seqNo);
        seqNo := seqNo + 1;
      END LOOP;

-- Free resources allocated for current dependent object stream.
      dbms_metadata.close(depObjOpenHandle);

    END LOOP; -- End of fetch dependent objects loop

  END LOOP;   -- End of fetch table loop

-- Free resources allocated for table stream and close output file.
  dbms_metadata.close(tableOpenHandle);
  RETURN;

END;  -- of procedure get_payroll_tables

END payroll_demo;
/

PAYROLL_DEMO Output

This is the output obtained from executing the procedure, mddemo.payroll_demo.get_payroll_tables. The output is obtained by executing the following query as user mddemo:

SQL> SELECT ddl FROM ddl ORDER BY seqno;

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

15 Using the Metadata API