Re: Editing results of Import with show=y

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