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

Today I wrote a preparation script for the oracle sqlloader. Some of the records I needed to load were expected to be already in the table. The script should take a file and generate delete statements for each records primary key. I thought Perls inplace edit feature would be a quick way to generate the list and I came up with the following solution. 
cp insert.data delete.sql

perl -F';' -ani -e 'print "delete from atable where afield = $F[0]\n";
+ END {print "quit\n"}' delete.sql

sqlplus ...  @delete.sql

sqlldr loadme.ctl

[download]
Everything seemed to work except the "quit" line I added to quit sqlplus. It was printed to STDOUT instead delete.sql
Why is it not added as the last line of delete.sql? I fixed it with echo quit >> delete.sql
The second question I have is a general one and OT. Is this a reasonable way to update an Oracle 8 Database table?

Replies are listed 'Best First'.
Re: inplace edit and END block
by Roy Johnson (Monsignor) on Apr 18, 2006 at 17:50 UTC
    The END block is executed outside the loop that -n created. If you had two input files, it would still execute only once. I think this will do what you wanted: 
    perl -F';' -ani -e 'print "delete from atable where afield = $F[0]\n";
+ print "quit\n" if eof' delete.sql

    [download]
    It looks like a reasonable, albeit quick-and-dirty, way to update a table to me.
    Caution: Contents may have been coded under pressure.
[reply]
[d/l]
      I thought in the edited file one could add headers in the CHECK or BEGIN stage and trailers END stage of execution. That would have been a nice feature, hm?
      It prevents checking for $.==1 or eof in every line.

      OT: After experimenting a little with the -i switch I found a nice obfuscated way of deleting files. ;-) 
      #!/usr/bin/perl -Xi
# usage $0 file_a file_b ...
while(<>){}

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom