Re: PreCreate Oracle Tables

From: Jared Still <jkstill_at_cybcon.com>
Date: Thu, 19 Feb 2004 07:36:04 -0800
Message-Id: <1077204964.11030.69.camel@poirot>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Thu Feb 19 2004 - 09:36:04 CST