using imp to print DDL statements

From: Bjorn Borud <borud_at_guardian.no>
Date: 1998/05/20
Message-ID: <m2btst7zto.fsf@lucifer.guardian.no>#1/1

I tried the trick that someone told me about on this newsgroup about using "exp" to export a schema to file and then use "imp" with "show=y" to print the DDL statements and such, but I am a bit disappointed with the output:

<excerpt from output>

 "CREATE TABLE "PERSON" ("ID" NUMBER, "LOPENR" VARCHAR2(20), "FORNAVN" VARCHA"
"R2(40), "ETTERNAVN" VARCHAR2(40), "SX_FORNAVN" CHAR(6), "SX_ETTERNAVN" CHAR"
"(6), "FODSELSDATO" DATE, "KLASSETRINN" NUMBER, "EPOST_ADR" VARCHAR2(60), "P"
"ERSONNR" NUMBER(5, 0), "STUDIEPROGRAM" NUMBER, "OPPTAKSAR" NUMBER(4, 0)) P"
"CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 3395584 NEXT 1"
"683456 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50 FREELISTS 1 FREELIST GROU"
"PS 1) TABLESPACE "BDB_DATA01""

 . . skipping table "PERSON"

</excerpt from output>

what I was looking for was something like:

CREATE TABLE person (

  Id              NUMBER PRIMARY KEY,
  Lopenr          VARCHAR2(20),
  Fornavn         VARCHAR2(40),
  Etternavn       VARCHAR2(20),
  Sx_fornavn      CHAR(6),
  Sx_etternavn    CHAR(6),
  Fodselsdato     DATE,
  personnr        NUMBER(5),
  Opptaksar       NUMBER(4),
  Studieprogram   NUMBER REFERENCES studieprogram(id),
  Klassetrinn     NUMBER,
  Epost_adr       VARCHAR2(60),

  UNIQUE (Lopenr)
);

that is: something that I could use to generate DDL statements for other databases.

if anyone knows of a way to do this with existing code I'd be happy to hear about it, because it would save me the job of writing a program to reverse engineer Oracle databases by mucking about in the data dictionary.

I also have another question: will "imp" with "show=Y" do anything with the state of the schema used at all? in other words, can I safely do this without the risk of actually importing stuff into the schema used?

-Bjørn

-- 
 Bjørn Borud <borud_at_guardian.no>       | "The Net interprets censorship 
 <URL:http://www.pvv.unit.no/~borud/>  | as damage and routes around it."
 UNIX person, one of "them"            |         - John Gilmore

Received on Wed May 20 1998 - 00:00:00 CDT