Use command line argument in script

[homer4all]

Experts, How can we use value in sub which was accepted at script run?

DB: oracle 11gR2

I tried using $days = $ARGV[1], not sure but it doesn't work.

Usage: ./delete_data.pl -days 30

sub deleteData
{
$days = <how to have 10 here??>;

my $sth = $dbh->prepare("DELETE FROM TEST_TABLE
                    WHERE date = trunc(sysdate) - ?");
$sth->execute( $days ) or die $DBI::errstr;
print "Number of rows deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;
}

Does above delete all days together or it will use cursor and delete one day at a time? Deleting one day at a time using bind variable will give better performance. Thanks...

Update

Experts, How can we use value in sub which was accepted at script run?

DB: oracle 11gR2

I tried using $days = $ARGV[0], not sure but it doesn't work.

Usage: ./delete_data.pl -days 30

my $days;

sub deleteData
{
my $rc;

if (@ARGV)
   {
     $days = $ARGV[0];
     $logfile-> logmessage ("Days in sub $days ...)
   }
    
  $days = shift (@ARGV);

 my $SQL = qq{ DELETE from TEST_TABLE
               WHERE trunc(date) < trunc(sysdate) - $days
             };
 
    $sth->prepareCursor( $SQL ) or die $DBI::errstr;
          print "Number of rows deleted :" + $sth->rows;
    $sth->execute();
           $rc++
    $sth->finish()
}

I'm not able to display value for $days even in logfile. Not sure why above just don't work, I have used suggestion solution from comment. Thanks...

[ww]

To your first question: we can't be sure the number of days is the SECOND CLI argument (which is what you're extracting in what you've shown -- $ARGV[1]); if the first CLI argument is what you want to feed to $days, then use $ARGV[0].

Here are two examples without the overhead of module loading:

#!/usr/bin/perl
use 5.018;
use warnings;

# 1110785

my $days;

if (@ARGV) {
    $days = $ARGV[0];
    say $days;
}else{
    say "n\t Usage: 1110785.pl n \n";
}

deleteData($days);

sub deleteData {
    $days = shift (@_);
    say "days in sub: $days";
}

say "back in main.";

=head EXECUTION

C:\>D:\_Perl_\PMonks\1110785.pl 3
3
days in sub: 3
back in main.

=cut

or (some would say better style):

 # ...
# 1110785a

my $days;

sub deleteData {

if (@ARGV) {
    $days = $ARGV[0];
    say $days;
}else{
    say "n\t Usage: 1110785.pl n \n";
}

$days = shift (@ARGV);
say "days in sub: $days";
#   ...
}

deleteData;
say "back in main.";

=head EXECUTION

C:\>D:\_Perl_\PMonks\1110785a.pl 8
8
days in sub: 8
back in main.

=cut

As to the your second question, why not 'try it to see?'

[homer4all]

Thank you for the syntax and explanation but somehow I'm not even able to display value for $days... could somebody please help.

[ww]

Please read the preceding response -- re $ARGV[n] -- and if that's not the issue, show us more of what you're doing because your problem statement is ambiguous.

[soonix]

to your second question: the DELETE deletes all records matching the WHERE clause, and does so in one go

This usually has better performance than doing it one record at a time. If you want to delete a date range (or all records with a date earlier than x), then the most effective way would be to modify your WHERE clause accordingly - of course, after having tested it extensively :)

[Anonymous Monk]

use Getopt::Long;

my $days = 30;
GetOptions( 'days=i', \$days );
print $days, "\n";

Usage:

$ perl script.pl
30

$ perl script.pl -days 100
100

$ perl script.pl -days
Option days requires an argument
30

$ perl script.pl -days many
Value "many" invalid for option days (number expected)
30

[chacham]

WHERE trunc(date) < trunc(sysdate) - $days

trunc(date) is redundant and makes the query take longer.

It is redundant as the purpose is to delete entries by the day. Truncating the date will still be the same day and not be less than the truncated sysdate. And if it is less, it would have been less even without being truncated.

It makes it take longer because it negates the use of any index and the function itself takes time. (Unless there is a function-based index there, which, silly as it may be, would make this the correct way to write the statement.)

FWIW, "FROM" is optional in a DELETE statement.

[homer4all]

Yes that makes sense, will remove trunc(date) and replace with date. 'date' contains datetime data so thought without trunc(date) it won't work.

Basically trying to accept parameter days at runtime, use it in DELETE

Delete all data from table which are older than -days parameter.

[chacham]

Excellent. Got to think about the where clauses sometimes. They're not always obvious from the get go.

As for using dynamic SQL, that is a security risk. You should really uses a parametrized query. Variables on the end of a statement are particularly bad.

[Anonymous Monk]

perlintro#Writing subroutines, perlvar#@ARGV, Data::Dump::dd ( Basic debugging checklist ), GetOpt::Long, perlrun# s

$ perl -se " use Data:::Dump qw/ dd /; dd( \@ARGV, $pan ) " -- -pan=ca
+kes ro sham bo
(["ro", "sham", "bo"], "cakes")

$ perl -e " use Data::Dump qw/ dd /; use Getopt::Long; my %opt; GetOpt
+ions( \%opt, qw[ pan=s bo! ]);  dd( \%opt , \@ARGV ) " -- -pan=cakes 
+bi bicky by -bo bo
({ bo => 1, pan => "cakes" }, ["bi", "bicky", "by", "bo"])

$ perl -e " use Data::Dump qw/ dd /; Main( @ARGV ); sub Main { dd( \@_
+ ); } " ba bay bi bi bo bicky bi bo bo
["ba", "bay", "bi", "bi", "bo", "bicky", "bi", "bo", "bo"]

Modern Perl a loose description of how experienced and effective Perl 5 programmers work....You can learn this too.

[ww]

WARNING! OP has changed the content of the post (  s/$ARGV[1]/$ARGV[0]/)since replies were provided. OP advised against that practice by way of message, consideration and this node.

Original content (sic): I tried using $days = $ARGV1, not sure but it doesn't work.

[homer4all]

Oops sorry, actually forgot to mention that in my earlier comment. I updated code to show what exact syntax I'm using rather than providing that in comment and flooding this page. Will keep this in mind.

[homer4all]

Error message I get is

use of unitialized value in print at line