#!/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]

**** 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]

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.

Thanks Kubrat, thanks all, I am able to separate the files now, code for the same as is as follows:

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


my $filename = "MyFileName0000";

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


open ( INFILE, $filename ) ||
    ( die "Cant read $filename: $!");


while ( <INFILE> ) {
    chomp;
    ++$filename;
    print "**** Open file $filename here\n";
    open ( IFILE, ">>$filename" );

    print IFILE "$_\n";
    close(IFILE);
    print "**** Close file $filename here\n";
}

select INFILE;
close(INFILE);
[download]

but the files created are MyFileName0001,MyFileName0002 etc...is there a way i can have the file anme as the tabels name itself...i tried following:

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

my $filename = "MyFileName0000";

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

open ( INFILE, $filename ) ||
    ( die "Cant read $filename: $!");

while (<INFILE> ) {
    chomp;
  #++$filename;
$scriptname=~ /CREATE TABLE ([\S]*)/;
   print "**** Open file $scriptname here\n";
    open ( IFILE, ">>$scriptname" );
    print IFILE "$_\n";
    close(IFILE);
    print "**** Close file $scriptname here\n";


}
select INFILE;
close(INFILE);
[download]

But the scriptname is not being extracted properly..i am getting null.... Thanks