There are many ways to crack this problem. This technique changes the line separator to be two new lines:

use strict;
use warnings;

my $filename = "MyFileName0000";

local $/ = "\n\n";

while (<DATA>) {
    chomp;
    ++$filename;
    print "**** Open file $filename here\n";
    print "$_\n";
    print "**** Close file $filename here\n";
}

__DATA__
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only
go

CREATE TABLE dbo.sec_iccc
(
user_nt_id_c char(16) NOT NULL,
unit_id_c char(4) NOT NULL
)
LOCK ALLPAGES
go
GRANT SELECT ON sec_iccc TO developer_read_only
go

CREATE TABLE sac_recon(
rec_number int NOT NULL,
rec_grp_number int NOT NULL,
)
go
[download]

Prints:

**** Open file MyFileName0001 here
CREATE TABLE sec_afs
(
rpt_per_typ_c char(1) NOT NULL,
rpt_per_typ_t varchar(20) NULL,
LOCK ALLPAGES
go
EXEC sp_primarykey 'sec_afs', rpt_per_typ_c
go
GRANT SELECT ON sec_afs TO developer_read_only
go
**** Close file MyFileName0001 here
**** Open file MyFileName0002 here
CREATE TABLE dbo.sec_iccc
(
user_nt_id_c char(16) NOT NULL,
unit_id_c char(4) NOT NULL
)
LOCK ALLPAGES
go
GRANT SELECT ON sec_iccc TO developer_read_only
go
**** Close file MyFileName0002 here
**** Open file MyFileName0003 here
CREATE TABLE sac_recon(
rec_number int NOT NULL,
rec_grp_number int NOT NULL,
)
go

**** Close file MyFileName0003 here
[download]

Update: Note cdarke's++ improvement of using "" to get 'paragraph' behavior.

Hi Thanks for replying...I wrote the following script,

#!/sw/perl5/1.1/bin/perl

use lib ("/fbcapps/buc/perl_lib");
use strict;

my $filename = "/home/smarter/alltables";

local $/ = "";

while (<DATA>) {
    chomp;
    ++$filename;
    print "**** Open file $filename here\n";
    print "$_\n";
    print "**** Close file $filename here\n";
}
[download]

It doesn't work however.It just exits.Please help.

The DATA filehandle is special; it reads from whatever comes after a __DATA__ line in your program. If you want to read from the file specified by $filename instead, you have to open it specifically.

open my $filehandle, '<', $filename
    or die "Can't read '$filename': $!";
while ( <$filehandle> ) {
    # etc.
}
close $filehandle or die "Fail on close '$filename': $!";
[download]

In addition to kyle's comment, you should note that ++$filename uses Perl magic to generate a sequence of file names. Unless you add a few digits to the end of the 'seed' file name the result may be other than you expect. Try running the following:

my $fn = 'xxx0';

print ++$fn, ' ' for 1 .. 11;
print "\n";

$fn = 'xx00';
print ++$fn, ' ' for 1 .. 11;
[download]

---

Perl reduces RSI - it saves typing

local $/ = "";
[download]

Hi All, Thanks for your feedback, I modified my script..it runs but does not split the files.

#!/sw/perl5/1.1/bin/perl
use strict;
#use warnings;

my $filename = "/home/smarter/tables/MyFileName0000";

local $/ = "\n\n";
my $temp_line;
my $filehandle;

#open (my $filehandle, '<', $filename ) ||
open ( INFILE, $filename ) ||
    ( die "Cant read $filename: $!");

#while ( <$filehandle> ) {
while ( $temp_line = <INFILE> ) {
    chomp;
    ++$filename;
    print "**** Open file $filename here\n";
    print "$_\n";
    print "**** Close file $filename here\n";
}
#close $filehandle or die "Fail on close '$filename': $!";
close(INFILE);
[download]

the output is :

**** Close file 126 here
**** Open file 127 here

**** Close file 127 here
**** Open file 128 here

**** Close file 128 here
**** Open file 129 here

**** Close file 129 here
**** Open file 130 here
[download]

Please help!

change this  while ( $temp_line = <INFILE> ) to this  while ( <INFILE> ) and it will work.

Also, note that if you want the filenames to be generated as MyFileName00* you'll have to drop the path from your $filename variable.

if ($line =~ /CREATE TABLE/) {
   close (FILE)
   ($filename) = $line =~ /CREATE TABLE ([\S]*)/;
   open (FILE, >$filename);
}
[download]