Re: Editing results of Import with show=y

When I started using Oracle I was taught to save always any create table and create index script. In that case you simply wouldn't need to apply what you call a brute force edit. Also there are many tools either case tools (Designer, Erwin) or utilities (Toad) out there which will allow you to dump your statements to an Ascii file.
The options indexfile and show where primarily introduced as an emergency measure for those folks who didn't keep their scripts. Sorry to reply in this fashion, but IMO 'brute force edit' sounds like a gross overstatement to me.

Regards,

Sybrand Bakker, Oracle DBA

"Robert Wagner" <RobertWagner_at_alum.mit.edu> wrote in message news:vqod5.22322$9E6.123764_at_newsr1.maine.rr.com...
> Mark:
> In other words, there is no SIMPLE way of making SQL*Plus accept it: It's
 a
> BRUTE FORCE edit.
>
> I wonder why Oracle chose to do it this way.
>
> Thanks >>> Robert
>
> "Mark D Powell" <mark.powellNOmaSPAM_at_eds.com.invalid> wrote in message
> news:2ce411a6.d6ee5e53_at_usw-ex0102-015.remarq.com...
>
> (Original message from "Robert Wagner" <RobertWagner_at_alum.mit.edu> NOT
> reproduced here.)
>
> > Here is some code that is supposed to edit the terminal output
> > from the show option that I saved but I do not have a note
> > saying I tested it. Perhaps it will help you.
> >
> > #!/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
> >
> >
> >
> >
> >
> > -----------------------------------------------------------
> >
> > Got questions? Get answers over the phone at Keen.com.
> > Up to 100 minutes free!
> > http://www.keen.com
> >
>
>
Received on Wed Jul 19 2000 - 00:00:00 CDT