Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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


advertisement
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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap