To rephrase your problem in a way that may make the answer more obvious, you need to:

1. Edit the file in-place
2. Keep track of the most recent CREATE TABLE line
3. Extract the table name out of it if it exists, and use it to keep up-to-date what table we're in
4. Otherwise, replace every occurrance of the string "PRIMARY KEY" with the string "CONTRAINT PK_(table name) PRIMARY KEY"

Armed with this knowledge, we can go about writing something that does this. I don't know enough about your problem to do #3 above reliably, so you'll probably have to change that regex in the following code:

#!/usr/bin/perl -pi
# The -p wraps the following code in a loop for each line,
# and the -i tells perl we're editing the file in-place.
# Give this program the filename(s) to change as arguments
# on the command line.

# Extract and update the table name if this is a CREATE TABLE line
$table = $1 if (/CREATE TABLE "SO"."([^"]+)"/);

# Change the "PRIMARY KEY" bit if we find it.
s/(PRIMARY KEY)/CONTRAINT PK_$table $1/;
[download]

I hope that makes some amount of sense.

perl -pe '"I lo*`+$^X$\"$]!$/"=~m%(.*)%s;$_=$1;y^`+*^e v^#$&V"+@( NO CARRIER'

my $sql = <<'END_OF_SQL' ;
CREATE TABLE "SO"."EL_HAND_RECEIPT"
(
    EL_ID             NUMBER(20) NOT NULL,
    UIC               VARCHAR2(6) NULL,
    ISSUE_RECEIPT_CD          VARCHAR2(1) NOT NULL,
    PRIMARY KEY (EL_ID)
)
TABLESPACE DATA
/
END_OF_SQL

my $table = 'EL_HAND_RECEIPT' ;

$sql =~ s/PRIMARY KEY/CONSTRAINT PK_$table PRIMARY KEY/;

print $sql ;
[download]

Those who know that they are profound strive for clarity. Those who
would like to seem profound to the crowd strive for obscurity.
            --Friedrich Nietzsche

Maybe something like this would do the trick?

#!/usr/local/bin/perl
use strict;

my $data = join( '', <DATA> );

$data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+)(PRIMARY KEY)/$1$2$3CONSTR
+AINT PK_$2 $4/ms;

print $data;

__DATA__
CREATE TABLE "SO"."EL_HAND_RECEIPT"
(
    EL_ID             NUMBER(20) NOT NULL,
    UIC               VARCHAR2(6) NULL,
    ISSUE_RECEIPT_CD          VARCHAR2(1) NOT NULL,
    PRIMARY KEY (EL_ID)
)
TABLESPACE DATA
[download]

Outputs...

:!test.pl
CREATE TABLE "SO"."EL_HAND_RECEIPT"
(
    EL_ID             NUMBER(20) NOT NULL,
    UIC               VARCHAR2(6) NULL,
    ISSUE_RECEIPT_CD          VARCHAR2(1) NOT NULL,
    CONSTRAINT PK_EL_HAND_RECEIPT PRIMARY KEY (EL_ID)
)
TABLESPACE DATA
[download]

Wonko

Here is what I consturcted from your example. However, it only returns the first line where there is a creat table statment. I get nothing like what you got. Could you please take a look and let me know where I went wrong.
Thanks, Bobby

#!/usr/bin/perl -w
use strict;

my $file = "go.txt";

print "\nOpening $file\n";

open(FILE, "< $file") || die "OLD FILE ERROR: Unable to open file: $!\
+n";
open(OUT, "> $file.new") || die"NEW FILE ERROR: Unable to create new f
+ile: $!\n";



while (<FILE>)
{
    my $data = join('', <FILE>);
    
    $data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+)(PRIMARY KEY)/$1 $2 $3
+ CONSTRAINT PK_$2 $4/ms;

    print OUT;    
}


print "Closing $file\n";

close(OUT) || die "Unable to close OUT file: $!\n";
close(FILE) || die "Unable to close OLD file: $!\n";
[download]

First, a note: the join operator is already reading the whole file into memory (the <FILE> is evaluated in list context, so it returns all the lines from the file), so there is no need for the while loop. You should just say:

#!/usr/bin/perl -w
use strict;

my $file = "go.txt";

print "\nOpening $file\n";

open(FILE, "< $file") || die "OLD FILE ERROR: Unable to open file: $!\
+n";
open(OUT, "> $file.new") || die"NEW FILE ERROR: Unable to create new f
+ile: $!\n";

my $data=join('',<FILE>);
[download]

Anyway, there is an error in the regexp, and one in the modifiers. The line should be:

$data =~ s/(CREATE TABLE "SO"\.")([^"]+)(.+?)(PRIMARY KEY)/$1$2$3CONSTRAINT PK_$2 $4/msg;

I changed two things:

• I changed from (.+) to (.+?), so that it matches the shortest string. Otherwise, when there are more than one "CREATE TABLE" in the file, it would match the first "CREATE" with the last "PRIMARY KEY".
• I added the g modifier, so that every match will be changed.

And, of course, then you have to print the result:

print OUT $data;

print "Closing $file\n";

close(OUT) || die "Unable to close OUT file: $!\n";
close(FILE) || die "Unable to close OLD file: $!\n";
[download]

-- 
        dakkar - Mobilis in mobile

...PRIMARY KEY (EL_ID) USING INDEX TABLESPACE INDEXES
STORAGE (INITIAL ... NEXT ... MAXEXTENTS UNLIMITED PCTINCEASE 0)
[download]

rdfield

OK, I have this working for the most part. However, I have one question how do I get USING INDEX TABLESPACE INDEXES added after the closing )?


s/(PRIMARY KEY)/CONSTRAINT PK_$table $1/;
[download]

can anyone help.


Thanks,

Bobby

s/(PRIMARY KEY\([^)]\))/CONSTRAINT PK_$table $1 USING INDEX TABLESPACE
+ INDEXES/;
[download]

as long as you have the primary key columns defined within brackets after "PRIMARY KEY".

rdfield