Need Perl Script to Re-Generate SQL Table Creation Script

sdruss
sdruss used Ask the Experts™
on
Need perl script to parse DDL table creation script.  Table script file which is typically generated is missing the “use tablespace  clause” for table that contains the XMLType datatype.  So, I want to add the table clause only to tables that contain this datatype.   Probably will want to save the original file and generate new file with the  For example script file contains:

CREATE TABLE TB1 (
          Col1      VARCHAR2(38) NOT NULL,
      Col2      NUMBER(10) NOT NULL,
      Col3      NUMBER(10),
      Col4      NUMBER(19),
      Col5      VARCHAR2(4000)
);

CREATE TABLE TB2 (
          Col1      VARCHAR2(38) NOT NULL,
      Col2      XMLTYPE NOT NULL,
      Col3      NUMBER(10),
      Col4      NUMBER(19),
      Col5      CLOB
);

CREATE TABLE TB3 (
          Col1      VARCHAR2(38) NOT NULL,
      Col2      XMLTYPE NOT NULL,
      Col3      NUMBER(10)
);

CREATE TABLE TB4 (
          Col1      NUMBER(10)  NOT NULL,
      Col2      NUMBER(10) NOT NULL,
      Col3      NUMBER(10),
      Col4      CLOB,
      Col5      BLOB
);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Not sure I understand the reason for this.

If you have a database that has the objects, why reinvent the wheel.

You can use dbms_metadata to generate the DDL

select dbms_metadata.get_ddl('TABLE','TB3') from dual;

Author

Commented:
This is a development process.  This script will be used eventually in production sites to create all the objects.
If the format of the DDL follows your example then this one-liner should work...
perl -i.bak -pe '$found = 1 if (m{\bxmltype\b}i); if ($found and s{^\s*)\s*;\s*$}{) tablespace some_tbs;}) { undef $found }'

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If the above script doesn't work for you, I would look at brute force and an editor.

With 'vi' I could probably change all the objects in a fraction of the time it has taken to wait for a solution to this question.

Author

Commented:
No, "vi" editor is definitely not an option.  These needs to be as much as possible auto-generated.

Author

Commented:
wilcoxon:  can you expand your perl example to single actual statement per line.  I will need to execute script on a continuing basis.
Sure...
#!/bin/perl
use strict;
use warnings;
use File::Copy qw(mv);
die "Usage: $0 files_to_process\n" unless @ARGV;
foreach my $fil (@ARGV) {
    my $new = $fil . '.tmp';
    open IN, $fil or die "could not open $fil: $!";
    open OUT, '>', $new or die "could not write $new: $!";
    my $found;
    while (<IN>) {
        if (m{\bxmltype\b}i) {
            $found = 1;
        } elsif ($found and s{^\s*)\s*;}{) tablespace $ts;}) {
            undef $found;
        }
    }
    close OUT;
    close IN;
    mv $fil, "$fil.bak" or die "could not mv $fil to backup: $!";
    mv $new, $fil or die "could not mv $new to $fil: $!";
}

Open in new window

Author

Commented:
wilcoxon:  Errors with script.  Probably don't understand the global expression and subtitutions.  Better example of "create table" entry in file:

          CREATE TABLE TB3 (
                    Col1      VARCHAR2(38) NOT NULL,
                   Col2      XMLTYPE NOT NULL,
                  Col3      NUMBER(10)
)
;

So, believe this what I need to do:

 1.  locate "XMLTYPE" string
     2.  locate ") <CR>;"  --right Paren, crlf, semi-colon first character new line
          3.  replace last ")"  with "TABLESPACE  XRLTP"
Just noticed a major error with the previous script - before line 17, there should be a line "print OUT $_;".  However, that won't fix the newline between ) and ;

I think this will work (unless the sql file is huge):
#!/bin/perl
use strict;
use warnings;
use File::Copy qw(mv);
use File::Slurp;
die "Usage: $0 files_to_process\n" unless @ARGV;
my $ts = 'tablespace_you_want_to_use';
foreach my $fil (@ARGV) {
    my $new = $fil . '.tmp';
    my $sql = read_file($fil) or die "could not read $fil: $!";
    open OUT, '>', $new or die "could not write $new: $!";
    $sql =~ s{(\bxmltype\b.*?\))\s*;}{$1 tablespace $ts;}msgi; # may need to change \s*; to .*?;
    print OUT $sql;
    close OUT;
    mv $fil, "$fil.bak" or die "could not mv $fil to backup: $!";
    mv $new, $fil or die "could not mv $new to $fil: $!";
}

Open in new window

Author

Commented:
wilcoxon:
My environment does not have access to, use File::Slurp.  So there suspect "read_file" is not available.  Can you guide me through a poor man's version of the already available but I can't get "read_file".
Sure.  Here's a modified version to use poor-man's File::Slurp.  It will work but may not be as good performance as using File::Slurp;
#!/bin/perl
use strict;
use warnings;
use File::Copy qw(mv);
die "Usage: $0 files_to_process\n" unless @ARGV;
my $ts = 'tablespace_you_want_to_use';
foreach my $fil (@ARGV) {
    my $new = $fil . '.tmp';
    open my $fh, '<:unix', $fil or die "Couldn't open $fil: $!";
    read $fh, my $sql, -s $fh or die "Couldn't read $filename: $!";
    close $fh;
    open OUT, '>', $new or die "could not write $new: $!";
    $sql =~ s{(\bxmltype\b.*?\))\s*;}{$1 tablespace $ts;}msgi; # may need to change \s*; to .*?;
    print OUT $sql;
    close OUT;
    mv $fil, "$fil.bak" or die "could not mv $fil to backup: $!";
    mv $new, $fil or die "could not mv $new to $fil: $!";
}

Open in new window

If your environment errors on <:unix open then try this one:
#!/bin/perl
use strict;
use warnings;
use File::Copy qw(mv);
die "Usage: $0 files_to_process\n" unless @ARGV;
my $ts = 'tablespace_you_want_to_use';
foreach my $fil (@ARGV) {
    my $new = $fil . '.tmp';
    my $sql;
    { # closure
        open my $fh, '<', $fil or die "could not open $fil: $!";
        local $/ = undef;
        $sql = <$fh>;
        close $fh;
    }
    open OUT, '>', $new or die "could not write $new: $!";
    $sql =~ s{(\bxmltype\b.*?\))\s*;}{$1 tablespace $ts;}msgi; # may need to change \s*; to .*?;
    print OUT $sql;
    close OUT;
    mv $fil, "$fil.bak" or die "could not mv $fil to backup: $!";
    mv $new, $fil or die "could not mv $new to $fil: $!";
}

Open in new window

Author

Commented:
wilcoxon:  Excellent, appreciate your expertise.  Will try Monday.  Thanks!

Author

Commented:
wilcoxon:  
 Closer, script executes without error, but does not do what I expect.  Matter of fact only end up with a single line in my new file.  Probably need to understand the global expression syntax.  Can you attempt to guide me through the basics.  Here is what I think:

$sql =~ s{(\bxmltype\b.*?\))\s*;}{$1 tablespace $ts;}msgi;

     1.  Whole file is read into variable $sql
     2.  perform substitution, looking for " xmlytype "
     3.  then looking for any number of spaces then " );"
     4.  ....
     5.  .....
     6.  .....
When you say you are only getting one line in the new file, what do you mean?  All the data from the original file is there but without any linebreaks, only the first line of the original file exists, or something else?  If it is only the first line of the original file appears in the new file, can you verify that reading the whole file into $sql is working?  Which version did you end up using?

1) Yes, whole file is read into $sql in the lines before the regex
2) $sql =~ s{(\bxmltype\b.*?\))\s*;}{$1 tablespace $ts}msgi means:
a) break (\b)
b) xmltype
c) break
d) 0 or more any character to the first right paren
* capture a-d into $1
e) 0 or more whitespace
f) semi-colong
and replace a-f with capture of a-d (via $1) tablespace $ts;

The flags say to treat it as single line (so \s should match newline - if not, replace \s with .), multi-line for purposes of ^ and $, and replace globally while ignoring case.

Author

Commented:
Thanks!  Got it.  It is now working perfectly for me.  Again really,
 really appreciate.

Author

Commented:
wilcoxon gave me outstanding support.  Extremely knowledgeable and very patient.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial