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