Use an Oracle API to Extract and Store Database Objects' DDL-2 : Page 2

Using Oracle Native APIs: The DBMS_METADATA Package
Oracle databases provide a rich set of prepackaged APIs in the form of Supplied PL/SQL Packages. One of the packages introduced in Oracle 9.2 version is exactly what you need: the DBMS_METADATA package. It contains APIs for retrieving database objects' definitions. Click here for the reference page for DBMS_METADATA.

The main API you'll be using is the DBMS_METADATA.GET_DDL function. This function returns an object definition SQL string as a CLOB. It takes the following IN parameters:

• object_type VARCHAR2
• name VARCHAR2
• schema VARCHAR2 DEFAULT NULL
• version VARCHAR2 DEFAULT 'COMPATIBLE'
• model VARCHAR2 DEFAULT 'ORACLE',
• transform VARCHAR2 DEFAULT 'DDL'

The code below creates a table EmpTest with indexes and constraints that you'll be using to test the code:

create table EmpTest
( 
  empNo     integer      not null,
  lastName  varchar2(30) not null,
  firstName varchar2(20) not null,
  job       varchar2(9)          '
  hireDate  date                 '
  isActive  number(1)
  constraint EmpTest_CK1
  check (isActive in (0,1))      ,
  salary    number(9,2)          ,
  commision number(9,2)         ,
  deptNo    number(2)            ,
  constraint EmpTest_PK
  primary key (empNo),
  constraint EmpTest_AK1
  unique (lastName, firstName)
);

create index EmpTest_HireDate_Salary
on EmpTest
(
   salary,
   hireDate
);

After running the above script, EmpTest table has been created with three indexes (two unique and one nonunique):

select index_name,
       index_type,
       uniqueness
  from user_indexes
 where table_name = ‘EMPTEST’;

INDEX_NAME	                 INDEX_TYPE	 UNIQUENESS

EMPTEST_AK1	                 NORMAL        UNIQUE
EMPTEST_HIREDATE_SALARY	 NORMAL        NONUNIQUE
EMPTEST_PK                              NORMAL        UNIQUE

EmpTest table also includes six constraints:

• one primary key—EmpTest_PK
• one alternate key—EmpTest_AK
• one check constraint—EmpTest_CK1
• three NOT NULL constraints with system-generated (SYS_*) names:

CONSTRAINT_NAME   CONSTRAINT_TYPE INDEX_NAME

SYS_C002144065    C	
SYS_C002144066    C	
SYS_C002144067    C	
EMPTEST_CK1       C	
EMPTEST_PK        P                EMPTEST_PK
EMPTEST_AK1       U                EMPTEST_AK1

Now, execute an anonymous PL/SQL code block that calls the DBMS_METADATA.GET_DDL function to retrieve EmpTest table definition.

The DBMS_OUTPUT package can only output strings up to 255 characters long, which is a problem because it's easy to exceed such a limit while working with tables' DDL strings. To overcome this limitation, the local procedure Show() is used (Listing 1).

Listing 1 produces the following output:

DDL length: 461

  CREATE TABLE "BORIS"."EMPTEST" 
   (	"EMPNO" NUMBER(*,0) NOT NULL ENABLE, 
	"LASTNAME" VARCHAR2(30) NOT NULL ENABLE, 
	"FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE, 
	"JOB" VARCHAR2(9), 
	"HIREDATE" DATE, 
	"ISACTIVE" NUMBER(1,0), 
	"SALARY" NUMBER(9,2), 
	"COMMISION" NUMBER(9,2), 
	"DEPTNO" NUMBER(2,0), 
	 CONSTRAINT "EMPTEST_CK1" CHECK (isActive in (0,1)) ENABLE, 
	 CONSTRAINT "EMPTEST_PK" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"  ENABLE,
	 CONSTRAINT "EMPTEST_AK1" UNIQUE ("LASTNAME", "FIRSTNAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 
MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
DEFAULT)
  TABLESPACE "TOOLS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 
MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TOOLS"

So far so good, though it returned the table's DDL string with the primary key EmpTest_PK, the alternate key EmpTest_AK1, and checked constraint EmpTest_CK1 altogether. Two unique indexes are created to support the primary and alternate key constraints. This isn't exactly what you want: you want a table without constraints and indexes to speed up the data load. Only when the data load is completed do you create indexes and constraints. Another reason to keep each object definition separate is flexibility: you may need to change the order of the objects' creation.

Now it's time to design a structure for storing all of your objects' metadata.

Previous Page

Next Page