Skip Headers

Oracle® Application Developer's Guide - XML 10g (9.0.4) Part Number B12099-01 |
Home | Contents | Index

# 13
Using Metadata API

This chapter describes the following sections:

- Introduction to Metadata API

- What is DBMS_METADATA?

- DBMS_METADATA Programmatic Interface

  - Performance Tips

- DBMS_METADATA Browsing Interface

- Metadata API Example: Retrieving DDL for Tables

## Introduction to 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 via industry-standard XSL-T (XML Stylesheet Transformation language).

- Generating SQL DDL to recreate the database objects

The Metadata API is available on Oracle whenever the instance is operational. It is not available on Oracle Lite.

### Previous Methods Used to Extract Metadata

An object's metadata is distributed in normalized fashion across the 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:

1. Transform it in some way, such as, change the object's tablespace, change a column datatype, change an object's owner, and so on.

2. Convert it to SQL DDL text for execution on the source or some other database.

In prior releases, there was no assistance for either of these steps.

### Metadata API Components

Underlying the Metadata API is an object model of the Oracle Dictionary comprised 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 from these queries are converted into XML documents by the XML / SQL utility (also new in Oracle). When the caller requests DDL output, the Metadata API uses 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 object definitions for the following classes of objects:

  - All types of tables (including relational, object, index-organized, nested, temporary and partitioned)

  - Indexes (including functional and domain indexes)

  - User-defined types

  - Procedures, functions, and packages

  - Operators

  - Indextypes

  - Relational and object views

  - Triggers

  - Synonyms

  - Grants (object and system privilege grants)

  - Outlines

- 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 via XSL-T 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.

- DDL output can be customized via object type-specific transform parameters.

### Internet Computing

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 Metadata API uses one optimized for generating Oracle DDL. Document element names are derived directly from attributes of the UDTs in the Oracle Dictionary model. As standard models emerge, Metadata API will support the ability to plug them in. Older documents can be converted to alternate models with XSLT.

With n-tier Internet Computing, it is natural for Metadata API to be bound to the server, close to the metadata. Hence, the Metadata API's implementation chose PL/SQL, which is callable from any other language including Java.

## What is DBMS_METADATA?

DBMS_METADATA is the PL/SQL package that implements Metadata API. It allows callers 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.

DBMS_METADATA has two types of interface:

- Programmatic interface for fine-grained, detailed control:

  - The following routines are provided and explained later: 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 simply invoke an XSL script, using either Oracle's XML parser or some third-party tool, to do an off-line conversion of the XML representation.

- Browsing interface for casual use within SQL clients such as SQL*Plus:

  - GET_DDL, GET_XML. For example, the following query will show the DDL for all tables in the current user's schema:

        SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;

  - Session-level "sticky" transform parameters

### DBMS_METADATA and Security

The object views of the Oracle metadata model implement security. Non-privileged users can see the metadata of just their own objects. SYS and those users with SELECT_CATALOG_ROLE can see all objects. Non-privileged 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.

Note: If non-privileged 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.

Note: The types and public interface defined by the Metadata API can be found in: `$ORACLE_HOME/rdbms/admin/dbmsmeta.sql`

See Also: Oracle9i Supplied PL/SQL Packages Reference

## DBMS_METADATA Programmatic Interface

Table 13-1 lists the DBMS_METADATA programmatic interface procedures.

##### Table 13-1 DBMS_METADATA Procedures: Programmatic Interface

PL/SQL Procedure | Syntax | Description
--- | --- | ---
DBMS_METADATA.OPEN() | FUNCTION open (object_type IN VARCHAR2, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE' ) RETURN NUMBER; | 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.
DBMS_METADATA.SET_FILTER() | PROCEDURE set_filter (handle IN NUMBER, name IN VARCHAR2, value IN VARCHAR2); PROCEDURE set_filter (handle IN NUMBER, name IN VARCHAR2, value IN BOOLEAN DEFAULT TRUE); | Specifies restrictions on objects to be retrieved, such as, object name or schema. Allows specification of base object(s) for dependent objects such as INDEXes and TRIGGERs.
DBMS_METADATA.SET_COUNT() | PROCEDURE set_count (handle IN NUMBER, value IN NUMBER); | 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() | FUNCTION get_query (handle IN NUMBER ) RETURN VARCHAR2; | Returns text of query (or queries) used by FETCH_xxx. Provided to assist in debugging.
DBMS_METADATA.SET_PARSE_ITEM() | PROCEDURE set_parse_item (handle IN NUMBER, name IN VARCHAR2); | 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() | FUNCTION add_transform (handle IN NUMBER, name IN VARCHAR2 encoding IN VARCHAR2 DEFAULT NULL) ) RETURN NUMBER; | 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 ADD_TRANSFORM 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. ADD_TRANSFORM returns an opaque transform handle different from that returned by OPEN. Specify encoding if: The XSL stylesheet pointed to by an external URL is encoded in a character set that is not a subset of UTF-8, or The XSL stylesheet pointed to by a DB-internal URL is encoded in a character set that is not a subset of the database's character set.
DBMS_METADATA.SET_TRANSFORM_PARAM() | PROCEDURE set_transform_param (transform_handle IN NUMBER, name IN VARCHAR2, value IN VARCHAR2); PROCEDURE set_transform_param (transform_handle IN NUMBER, name IN VARCHAR2, value IN BOOLEAN DEFAULT TRUE); | Specifies parameters to the XSLT stylesheet identified by transform_handle returned from ADD_TRANSFORM. 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() | FUNCTION fetch_xml (handle IN NUMBER) RETURN XMLType; FUNCTION fetch_ddl (handle IN NUMBER) RETURN sys.ku$_ddls; FUNCTION fetch_clob (handle IN NUMBER) RETURN CLOB; PROCEDURE fetch_clob (handle IN NUMBER, doc IN OUT NOCOPY CLOB); | The FETCH_xxx routines return metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM... 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 Reference.
DBMS_METADATA.CLOSE() | PROCEDURE close (handle IN NUMBER); | Invalidates the handle returned by OPEN and cleans up associated state.

See Also: Oracle9i Supplied PL/SQL Packages Reference Chapter 5, "Database Support for XML" for the specification of XMLType.

### DBMS_METADATA.FETCH_XML

Figure 13-1 illustrates `DBMS_METADATA.FETCH_XML()` usage:

1. Open the object type using `DBMS_METADATA.OPEN().`

2. Specify which objects to retrieve using `DBMS_METADATA.SET_FILTER().`

3. Fetch each qualifying object's metadata as an XML document using `DBMS_METADATA.FETCH_XML().`

4. If the result of this operation is NULL, then `DBMS_METADATA.CLOSE().`

#### Figure 13-1 Using DBMS_METADATA.FETCH_XML()

Text description of the illustration adxml091.gif

### DBMS_METADATA.FETCH_DDL()

Figure 13-2 illustrates DBMS_METADATA.FETCH_DDL() usage:

1. Open the object type using `DBMS_METADATA.OPEN()`.

2. Specify which objects to retrieve using `DBMS_METADATA.SET_FILTER()`.

3. Specify what transforms are to be invoked on the output:

   - `DBMS_METADATA.ADD_TRANSFORM()` to add a transform. The last transform added must be the "DDL" transform.

4. `DBMS_METADATA.SET_TRANSFORM_PARAM()`. This allows you to customize the DDL; for example, to exclude storage clauses on table definitions. Transform parameters are specific to the object type chosen.

5. Fetch the DDL using `DBMS_METADATA.FETCH_DDL()`.

6. If the result of this operation is NULL, then `DBMS_METADATA.CLOSE().`

#### Figure 13-2 Using DBMS_METADATA.FETCH_DDL()

Text description of the illustration adxml090.gif

### Performance Tips

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

1. 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 example at the end of this chapter actually reflects this second, less efficient means, but its purpose is to demonstrate most of the programmatic calls which are best shown by this method.

2. Use the SET_COUNT procedure to retrieve more than one object at a time. This minimizes server round trips as well as eliminates many redundant function calls.

3. Use the procedure rather than function form of FETCH_CLOB. The procedure form returns the output CLOB by reference via the IN OUT NOCOPY specifier. The function form returns the output CLOB by value requiring an extra LOB copy.

4. When writing a PL/SQL package that calls 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 provided by the GET_XML and GET_DDL functions.

Table 13-2 lists the browsing APIs, their syntax, and a brief description.

##### Table 13-2 DBMS_METADATA Procedures: Browsing Interface

PL/SQL Procedure Name | Syntax | Description
--- | --- | ---
DBMS_METADATA.GET_xxx() | FUNCTION get_xml ( object_type IN VARCHAR2, name IN VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT NULL) RETURN CLOB;
FUNCTION get_ddl ( object_type IN VARCHAR2, name IN VARCHAR2, schema IN VARCHAR2 DEFAULT NULL, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE', transform IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB; | Provides a way to return metadata for a single object. Each GET_xxx call is comprised of an OPEN, one or two SET_FILTER calls, optionally an ADD_TRANSFORM, a FETCH_xxx and a CLOSE. The object_type parameter has the same semantics as in OPEN. schema and name are used for filtering. If a transform is specified, session-level transform flags are inherited.

#### Example

The following SQL*Plus command will display the creation DDL for all tables in the current user's schema:

    SQL> SELECT dbms_metadata.get_ddl('TABLE', table_name) FROM user_tables;

## Metadata API Example: Retrieving DDL for Tables

Here is a detailed Metadata API programming example, PAYROLL_DEMO, that 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%''');

    -- There are no index-organized tables in the MDDEMO schema, so tell the API.
    -- This eliminates one of the views it'll need to look in.
      dbms_metadata.set_filter(tableOpenHandle, 'IOT', FALSE);

    -- 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 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 OPTI
    ON;

      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" ("LASTN
    AME")
      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 payro
    ll_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 OP
    TION;
      GRANT ON COMMIT REFRESH ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH G
    RANT OPTION;
      GRANT QUERY REWRITE ON "MDDEMO"."PAYROLL_TIMECARDS" TO "MDDEMO_MGR" WITH GRANT
     OPTION;

      CREATE INDEX "MDDEMO"."I_PAYROLL_TIMECARDS_BADGE" ON "MDDEMO"."PAYROLL_TIMECAR
    DS" ("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" ;

| Copyright © 2001, 2003 Oracle Corporation. All Rights Reserved. |
Home | Contents | Index