chuntoon has asked for the wisdom of the Perl Monks concerning the following question:

I have a file containing a list of tables from an oracle schema.
I have to generate replication support for these tables, which is simple enough using the Oracle API.
The problem is coming when I try to generate the sql script from the file. It appears to be putting a newline at the end of the table names. Attached is the code to generate the script. I have tried to chomp() the table name once in the foreach loop, but that just returned a 1 in place of the table name. 
#!/usr/bin/perl -w

use strict;

my $schema = shift(@ARGV);
my $infile = "./".$schema."_tables.out";
my $outfile = "./gen_rep_support_".$schema."_tables.sql";
my $table;

open (INFILE, "< $infile") or die "Couldn't open $infile: $!";
my @tables = <INFILE>;
close (INFILE);

open (OUTFILE, "> $outfile") or die "Couldn't open $outfile: $!";

foreach $table (@tables) {
    print OUTFILE "BEGIN\n";
    print OUTFILE "   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (\n";
    print OUTFILE "      gname => 'beastdb_mstr_grp',\n";
    print OUTFILE "      type => 'TABLE',\n";
    print OUTFILE "      oname => '$table',\n";
    print OUTFILE "      sname => '$schema',\n";
    print OUTFILE "      use_existing_object => TRUE,\n";
    print OUTFILE "      copy_rows => FALSE);\n";
    print OUTFILE "END;\n";
    print OUTFILE "/\n\n";
}

close (OUTFILE);

[download]
Thanks for any help.

Replies are listed 'Best First'.
Re: Getting wierd results when looping through file contents
by mfriedman (Monk) on Jun 26, 2002 at 16:16 UTC
    Each line of the tables file does have a newline at the end, and that newline is at the end of each $table as you go through the loop. The reason the chomp didn't work is because it "returns the total number of characters removed from all its arguments." That's why you're getting a 1.

    Thus, all you need to do is change your foreach loop to: 

    foreach $table (@tables) {
    chomp $table;
    print OUTFILE "BEGIN\n";
    print OUTFILE "   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (\n";
    print OUTFILE "      gname => 'beastdb_mstr_grp',\n";
    print OUTFILE "      type => 'TABLE',\n";
    print OUTFILE "      oname => '$table',\n";
    print OUTFILE "      sname => '$schema',\n";
    print OUTFILE "      use_existing_object => TRUE,\n";
    print OUTFILE "      copy_rows => FALSE);\n";
    print OUTFILE "END;\n";
    print OUTFILE "/\n\n";
}

    [download]
[reply]
[d/l]
Re: Getting wierd results when looping through file contents
by Aristotle (Chancellor) on Jun 26, 2002 at 16:27 UTC
    Somewhat unrelated to the question, but please, please do use here documents. 
    foreach $table (@tables) {
    chomp $table;
    print OUTFILE <<"EOT";
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'beastdb_mstr_grp',
      type => 'TABLE',
      oname => '$table',
      sname => '$schema',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

EOT
}

    [download]
    Also, there's no reason to slurp the entire input file here. 
    open (INFILE, "< $infile") or die "Couldn't open $infile: $!";
open (OUTFILE, "> $outfile") or die "Couldn't open $outfile: $!";

chomp, print OUTFILE <<"EOT" while <INFILE>; # shorter version
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'beastdb_mstr_grp',
      type => 'TABLE',
      oname => '$_',
      sname => '$schema',
      use_existing_object => TRUE,
      copy_rows => FALSE);
END;
/

EOT

close (INFILE);
close (OUTFILE);

    [download]
    Update: If that looks too strange, you might want to do something like: 
    foreach $table (@tables) {
    print OUTFILE (map "$_\n",
        "BEGIN",
        "   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (",
        "      gname => 'beastdb_mstr_grp',",
        "      type => 'TABLE',",
        "      oname => '$table',",
        "      sname => '$schema',",
        "      use_existing_object => TRUE,",
        "      copy_rows => FALSE);",
        "END;",
        "/\n",
    );
}

    [download]
    ____________
    Makeshifts last the longest.
[reply]
[d/l]
[select]
      Are you really suggesting that your here document version is more readable? Your use of a here document completely messes up the visual organization of the code and is particularly less readable because the contents of the here document itself resembles code and has its own indent structure. This is surely not a case where a plain here document is a good solution.
[reply]
      A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom