Re: export user definitions

On Wednesday 12 September 2001 21:05, Deshpande, Kirti wrote:
> I thought Joe was working on some kewl tool to address processing of
> exported dump file to get some meaningful SQL out ....

Maybe this would work?

Far from perfect, it mostly massages the table definitions into something manageable.

use it like:

imp userid=scott/tiger indexfile=ddl.txt file=expdat.dmp indexfile_filter < ddl.txt > ddl.sql

Jared

--

#!/usr/bin/perl


=pod

        indexfile_filter

        create usable SQL from 'imp indexfile=myfile.sql'

        e.g. indexfile_filter < myfile.sql > newfile.sql

=cut

my $ddl = undef;

while(<>) {

        chomp;
        # don't remove quotes!
        # they are your friend for odd object names
        #s/\"//g;                       # remove quotes
        s/^REM\s+//;    # uncomment

        next if /^\s*$/; # blank line
        next if /^CONNECT/;

        # process current sql if defined and new command starting

        if ( /^CREATE|ALTER/ ) {
                if ( $ddl ) {
                        sqlFormat(\$ddl);
                        print "$ddl\n\n";
                        $ddl = '';
                }
        }

        $ddl .= $_;
}

sqlFormat(\$ddl);
print "$ddl\n";

sub sqlFormat {

        my ($sqlRef) = @_;

        # 
        
        my $parsed = ${$sqlRef};

        if ( ${$sqlRef} =~ /^CREATE\sTABLE/ ) {
                $parsed =~ s/
                        ^([\w\s\d\"\.\$]+)      # match to first paren
                        (\(.+\){1}?)                    # get column definitions
                        (.+)                                            # the junk in 
the middle
                        (\([\w\s\d\$\"]+\))     # last set of parens
                        (.*)$                                           # to the end 
of the line
                        /undef/x;                               # can replace with junk
                                                                                # as 
we have parsed text

                my @sqlParts = ( $1, $2, $3, $4, $5 );

                #$sqlParts[0] = ${$sqlRef};
                for $i ( 0 .. $#sqlParts ) {
                        if ( $sqlParts[$i] =~ /,/ ) {
                                # newline after opening paren
                                $sqlParts[$i] =~ s/^(.*)(\()(\")(.+)$/$1$2\n   $3$4/;
                                # newline before comma following closing paren
                                $sqlParts[$i] =~ s/\),/\)\n   ,/g;
                                # newline before comma following data type e.g. DATE
                                $sqlParts[$i] =~ s/(\w),/$1\n   ,/g;
                                # newline after closing paren
                                $sqlParts[$i] =~ s/\)$/\n\)/;
                        }
                }

                ${$sqlRef} = join("\n",@sqlParts);
                        
        }

}


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).