On Wed, 2004-02-18 at 12:52, Daniel Fink wrote:
> In that case, #3 would be my choice. Using the indexfile is acceptable, but
> it can be hard to read and edit at times. #3 also has the added benefit of

True. The following Perl may be of use to someone here. :)

Jared

#!/export/home/oracle/perl/bin/perl


=pod

 indexfile_filter

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

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

 see 'fixddl' for formatting extremely long CREATE
 statements. some CREATE statements can reach the
 line limit length for SQL*Plus

=cut

my $statement ='';

while(<>) {

 chomp;
 # don't remove the quotes. Some colum names may
 # reqire quotes
 #s/\"//g;      # remove quotes
 s/^REM\s+//;  # uncomment

 # get past CONNECT statement
 /^\s*$/ && do { next };
 /^CONNECT/ && do { next };

 $statement .= $_;
 /;$/ && do {
   $statement =~ s/\s+TABLESPACE\s+/\nTABLESPACE /goi;
   $statement =~ s/\s+PCTFREE\s+/\nPCTFREE /goi;
   $statement =~ s/\s+STORAGE\s*\(/\nSTORAGE( /goi;
   $statement =~ s/, \"/\n\t, \"/go;
   print "$statement\n\n";;
   $statement = '';
 };

}


--------------------

#!/export/home/oracle/perl/bin/perl

=pod

see 'indexfile_filter' for extracting SQL DDL from
files created via 'imp indexfile=<filename>'

=cut


while(<>) {

 chomp;
 next if /^ALTER.*MODIFY.*DEFAULT NULL/io;
 my $sql = $_;
 my $create = $_;
 my $columns = $_;
 my $storage = $_;
 my @columns = ();

 $create =~ s/(^CREATE.*?)(\(.*$)/$1/;
 $columns =~ s/(^CREATE.*?)(\(.*\)).*STORAGE.*$/$2/;
 $storage =~ s/(^CREATE.*?)(\(.*\))(.*STORAGE.*$)/$3/;


 #print "SQL: $sql\n\n";
 #print "CREATE: $create\n\n";
 #print "COLUMNS: $columns\n\n";
 #print "STORAGE: $storage\n\n";

 @columns = split(/\,\s+\"/ ,$columns);

 print $create, "\n",
   join(",\n\t\"", @columns), "\n",
   $storage, "\n\n";

}





----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------