#!/usr/local/bin/perl

# ***************************************************************************
# Filename:     ddl_extract.perl
#
# Description:  Script utility for extracting DDL from standard ORACLE exports.
#
# Parameters:   None
#
# Principles used in script:
#		 As ORACLE Exports are mainly TEXT format this program mearly
#		 searches for key pattern matches and formats/writes to output files.
#
# Notes :  As new ORACLE DDL formats and options arise this script should be updated.
#
# ******************************************************************************
# *  Date     Who        Request    Ver   Comments                             *
# * --------- ---------  -------    ---   ----------------------------------   *
# * Pre-Hist   R.Chapman            0.5   Original Version                     *
# * 18-Aug-98  J.Hallam             1.0   Re-Written and Re-formatted          *
# * 18-Aug-98  J.Hallam             1.1   Changed to read compressed files     *
# * 02-Nov-00  P.Brys               1.2   Changed to add table extraction      *
# ******************************************************************************

system("clear");

print "|************************************************************************************************|\n";
print "|                                                                                                |\n";
print "|         ORACLE DDL EXTRACTION PROGRAM  							|\n";
print "|												|\n";
print "|         Designed to extract DDL from standard Compressed or uncompress ORACLE export Files 	|\n";
print "|												|\n";
print "|         Version 1.2 										|\n";
print "|                                                                                                |\n";
print "|************************************************************************************************|\n";

print "\n\n\n\n";

system("ls -alrt *.dmp*|more");

print "\n\n\n";

#---------------------------------------------------------------#
#     Populate parameter values with user input                 #
#---------------------------------------------------------------#

print "Please Enter The ORACLE Dump File Name - can be a multiple file export (eg EXP_LRGE*.dmp.Z) \n";
$DumpFileName = <STDIN>;

chomp $DumpFileName;  				# removes new line & null terminator !

$DumpFilLength = length($DumpFileName);

$IsCompressed = substr($DumpFileName,$DumpFilLength - 1,1);

if ($IsCompressed eq "Z") {

   $| = 1;					# Flush "WRITE" buffer to disk !

   print "\n\n";
   print "   -->  File is Compressed - Extracting with : ";
   print "\n";
   print("\t\t cat $DumpFileName | uncompress |");
   print "\n\n";

   open (IN_SOURCE_DMP,"cat $DumpFileName |compress - |") || die "Cannot Open $DumpFileName for Reading \n";

} else {

   $| = 1;                                      # Flush "WRITE" buffer to disk !
   print "\n\n";
   print "   -->  File is NOT Compressed - Extracting with : ";
   print "\n";
   print("\t\t $DumpFileName ");
   print "\n\n";

   open (IN_SOURCE_DMP,"$DumpFileName") || die "Cannot Open $DumpFileName for Reading \n";

}

print "Please Enter The Output DDL File PREFIX eg GFA_404 - default (OUT) \n";
$DDL_PREFIX = <STDIN>;

chop($DDL_PREFIX);				# removes null terminator !


if ($DDL_PREFIX eq "") {
   $DDL_PREFIX = "OUT";
}

#---------------------------------------------------------------#
#     Initiate Files for Reading/Writing                        #
#---------------------------------------------------------------#

open (IN_SOURCE_DMP,"+<$DumpFileName") || die "Cannot Open $DumpFileName for Reading \n";
open (OUT_TABLESPACES_DDL,">${DDL_PREFIX}_1_tablespaces_ddl.sql") ;
open (OUT_USERS_DDL,">${DDL_PREFIX}_2_users_ddl.sql") ;
open (OUT_TABLES_DDL,">${DDL_PREFIX}_3_tables_ddl.sql") ;
open (OUT_ROLLBACKS_DDL,">${DDL_PREFIX}_4_rollbacks_ddl.sql") ;

#---------------------------------------------------------------#
#     Open Main Loop and Read File line by line			#
#---------------------------------------------------------------#

while (<IN_SOURCE_DMP>)
{

#---------------------------------------------------------------#
#  Remove all " from the input line and trim down white space   #
#---------------------------------------------------------------#

	s/"/ /g;
	s/  / /g;
	s/  / /g;
        s/ *$//g;

#-----------------------------------#
# Find Required Regular Expressions #
#-----------------------------------#

  if (/CREATE TABLESPACE/ || /ALTER TABLESPACE/) {

             $OUTPUT_FILE="OUT_TABLESPACES_DDL";
             &format_line;


  } elsif (/CREATE USER/ || /ALTER USER/) {

             $OUTPUT_FILE="OUT_USERS_DDL";
             &format_line;

  } elsif (/CREATE TABLE/ || /ALTER TABLE/) {

             $OUTPUT_FILE="OUT_TABLES_DDL";
             &format_line;

  } elsif (/CREATE ROLLBACK/ || /ALTER ROLLBACK/) {

            $OUTPUT_FILE="OUT_ROLLBACKS_DDL";
            &format_line;

  }
}

sub format_line {

#-------------------------------------------#
#  Setup variables for string parsing       #
#-------------------------------------------#

  $CURSOR_POSITION=1;
  $LINE_END=1;
  $LINE_START=0;
  $STRING_LENGTH = length($_);
  $FOUND_END = 0;

#----------------------------------------------------------------------------------------#
# --- Work out the type of DDL for extraction					         #
# --- Then break each line down into 80 char segments (next space " " after char 70).... #
#----------------------------------------------------------------------------------------#

    while ($CURSOR_POSITION < $STRING_LENGTH) {

      if ($STRING_LENGTH - $CURSOR_POSITION > 80) {

        $LINE_END=$LINE_END + 70;

        while ($FOUND_END < 1) {                             #
           $CURSOR_POSITION=index($_," ",$LINE_END);         # Find position of end of line
           $BEFORE_SPACE=substr($_,$CURSOR_POSITION - 1,1);  # that doesn't fall between ' '
                                                             # and position cursor there
           if ($BEFORE_SPACE eq "'") {                       #
              $FOUND_END = 0;                                #
              $LINE_END=$LINE_END + 1;                       #
           }else {                                           #
              last;                                          #
           }                                                 #
        }                                                    #

        $LINE=substr($_,$LINE_START,$CURSOR_POSITION-$LINE_START);
        $LINE_START=$CURSOR_POSITION;
        print $OUTPUT_FILE "$LINE \n";
      } else {
        $LINE=substr($_,$LINE_START,$STRING_LENGTH-$LINE_START);
        print $OUTPUT_FILE "$LINE ;\n\n";
        $CURSOR_POSITION=$STRING_LENGTH;
      }
    }
}

#---------------------------#
#   Close All Open Files    #
#---------------------------#

close (OUT_TABLESPACES_DDL);
close (OUT_USERS_DDL);
close (OUT_TABLES_DDL);
close (OUT_ROLLBACKS_DDL);
close (IN_SOURCE_DMP);