Re: exp/imp DDL

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Sat, 22 Jan 2000 14:03:05 +0100
Message-ID: <948546297.9660.0.pluto.d4ee154e@news.demon.nl>

If you are on Unix use the following korn shell script written by Steve Hauser, called ugly_ddl

#!/bin/ksh

# Make DDL usable for Oracle svrmgrl or sqlplus from the Oracle
# export/import utilities.
# GPL Copyleft.
# More stuff at http://www.tc.umn.edu/~hause011/
# NOTE: Solaris use nawk or gawk instead of awk.

# How to do it: Use Oracle imp (import) utility to get the DDL from a
# oracle exp (export) file. Then filter textfile with this awk.
# The result can be used by sqlplus or used to convert to a better
# database product.

# Example:
# exp name/password full=y rows=n ...
# imp name/password show=y full=y file=xxxnamexx.dmp > textfile
# ugly_DDL.ksh textfile > bunch_of_SQL.sql

# Note: to remove storage declarations uncomment the line:
# '# remove_tablespace' near the bottom of this script.

# Find lines with beginning 'space doublequote' and length < 78.
# Remove beginning 'space doublequote' and end doublequote, add space at end
# as they end at whole tokens.
# Find lines with beginning 'space doublequote' and length = 78.
# Remove beginning 'space doublequote' and end doublequote
# Break at the beginning of DDL statements.
get_statements () {

    awk '

      /^ "/ { if (length($0)==78) {gsub("\"$","",$0);}
                 else  {gsub("\"$"," ",$0);};
              gsub("^ \"","",$0);
              sub("^CREATE ","\nCREATE ",$0);
              sub("^ALTER ","\nALTER ",$0);
              sub("^COMMENT ","\nCOMMENT ",$0);
              sub("^GRANT ","\nGRANT ",$0);
              print }

    '
}

# Grab whole blank line delimited records, put a semicolon at the
# end of a record then join the broken tokens by removing \n in the records.
join_tokens () {

    awk '

          BEGIN { RS="" }
          { gsub("\n","",$0); }
          { print $0";\n" }

    '
}

# Remove tablespace and extent declarations.
remove_tablespace () {

    awk '

         { sub(" PCTFREE.*;",";",$0); }
         { print }

    '
}

# Fix line lengths; sqlplus and vi have problems with long lines.
semicolon_break () {

    awk '

         /;.*/ && /CREATE TRIGGER/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE PACKAGE/ { gsub(";",";\n",$0); }
         /;.*/ && /CREATE FUNCTION/ { gsub(";",";\n",$0); }
         { print }

    '
}

# Fix line lengths; sqlplus and vi have problems with long lines.
comma_break () {

    awk '

         length($0)> 1024  { gsub(",",",\n",$0); }
         { print }

    '
}

# Usage message.

usage () {

    echo;
    echo 'Usage: '$0 '<text file name from export/import>'     echo;
    exit
}

###################################

# Main

#

test $# -eq 1 || usage

cat $1|
get_statements |
join_tokens |
#remove_tablespace |
semicolon_break |
comma_break

If you are on NT/windows, get toad at www.toadsoft.com

--
Sybrand Bakker, Oracle DBA
Buck Turgidson <jcmanNOSPAM_at_worldnet.att.net> wrote in message news:86c8d0$dgs$1_at_bgtnsc01.worldnet.att.net... > When using export/import to capture DDL, it adds quotation marks to it. SQL

> Plus complains about this.  Is there a way to get around this?
>
> Sample Output:
>
>
>
>  "CREATE TABLE "T" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1
> MAXTRA"
>  "NS 255 LOGGING STORAGE(INITIAL 524288) TABLESPACE "UTILS""
>  "CREATE INDEX "DUMMY" ON "T" ("X" )  PCTFREE 10 INITRANS 2 MAXTRANS 255
> STOR"
>  "AGE(INITIAL 524288) TABLESPACE "UTILS" LOGGING"
>
>
> SQL> "CREATE TABLE "T" ("X" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1
> MAXTRA"
> unknown command beginning ""CREATE TA..." - rest of line ignored.
> SQL> "NS 255 LOGGING STORAGE(INITIAL 2048) TABLESPACE "USER""
> unknown command beginning ""NS 255 LO..." - rest of line ignored.
> SQL> "CREATE INDEX "DUMMY" ON "T" ("X" ) PCTFREE 10 INITRANS 2 MAXTRANS

255

> STOR"
> unknown command beginning ""CREATE IN..." - rest of line ignored.
> SQL> "AGE(INITIAL 524288) TABLESPACE "USER" LOGGING"
> unknown command beginning ""AGE(INITI..." - rest of line ignored.
> For a list of known commands enter HELP
> and to leave enter EXIT.
>
>

Received on Sat Jan 22 2000 - 07:03:05 CST