Perl Script to get DDL from Indexfile

From: mohammed bhatti <mkb125_at_yahoo.com>
Date: Wed, 02 Jan 2002 16:53:06 -0800
Message-ID: <F001.003E5896.20020102163518@fatcity.com>

I've always wanted an easy way to extract DDL info from an import indexfile so I wrote a little perl script that will extract create table ddl from an indexfile. Seems to work and is a lot faster than hand editing. Would appreciate any feedback/criticism or ways to improve the script.

#!/usr/local/bin/perl -w

################
# Extract CREATE TABLE statements from export file
# By: MKB Jan 02 2002
# Inputs: Location of log file and file output
# Outputs: Writes to file output
# Modification History:
#
################

use strict;
my ($infile, $outfile);
my $usage = "usage: <input file> <output file>\n"; $usage = $usage . "Example: /home/oracle/file.log /home/oracle/table.log \n";

if ($#ARGV != 1) { die($usage) }
else { $infile = $ARGV[0];

       $outfile = $ARGV[1];
     };

open my $fh, $infile or die print "can't open"; open my $fh_out, ">$outfile" or die

"Can't create $!";

my @strg;
my $i = 0;
my $long_strg = "";

while (<$fh>) {

   $_=~ s/^REM //;
   $_=~ s/\n//;
   @strg = split / /, $_;
   # print all elements in array
   # and make one long string
   for ($i = 0; $i <= $#strg; $i++) {

      if ($strg[$i] ne ";") {
         $long_strg = $long_strg . " " . $strg[$i];
      }
      else {
         if ($long_strg =~ m/CREATE TABLE/) {
            $long_strg =~ s/"//g;
            $long_strg =~ s/CREATE TABLE
((\w+).(\w+))/CREATE TABLE $1 \n/;
            $long_strg =~ s/(\w\D),/$1,\n/g;
            $long_strg =~ s/INITRANS/\n INITRANS/g;
            $long_strg =~ s/MINEXTENTS/\n
MINEXTENTS/g;
            $long_strg =~ s/PCTINCREASE/\n
PPCTINCREASE/g;
            $long_strg =~ s/PCTFREE (\d+) /\n PCTFREE
$1 /g;
            $long_strg =~ s/(TABLESPACE
(\w+|\W+))|(TABLESPACE (\w+\W+) LOB)/\n $1/g;
            $long_strg =~ s/STORE AS/\n STORE AS/;
            $long_strg =~ s/ STORAGE\(/\n STORAGE
\(/g;
            print $fh_out $long_strg . ";\n\n";
        }
         $long_strg = "";
      }

}
}

close $fh;
close $fh_out;

Do You Yahoo!?
Send your FREE holiday greetings online! http://greetings.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: mohammed bhatti
  INET: mkb125_at_yahoo.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).

Received on Wed Jan 02 2002 - 18:53:06 CST

Perl Script to get DDL from Indexfile - Part1