Use command line argument in script

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

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;
}
[download]

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()
}
[download]

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...

Comment on Use command line argument in script Select or Download Code

Replies are listed 'Best First'.
Re: Use command line argument in script by ww (Archbishop) on Dec 18, 2014 at 23:49 UTC
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` [download] 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` [download] As to the your second question, why not 'try it to see?'	[reply] [d/l] [select]
Re^2: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 16:30 UTC
Thank you for the syntax and explanation but somehow I'm not even able to display value for $days... could somebody please help.	[reply]
Re^3: Use command line argument in script by ww (Archbishop) on Dec 19, 2014 at 16:41 UTC
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. Spirit of the Monastery	[reply] [d/l] [select]
Re^4: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 16:49 UTC
Re: Use command line argument in script by soonix (Chancellor) on Dec 19, 2014 at 10:08 UTC
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 :)	[reply] [d/l] [select]
Re: Use command line argument in script by Anonymous Monk on Dec 18, 2014 at 23:27 UTC
`use Getopt::Long; my $days = 30; GetOptions( 'days=i', \$days ); print $days, "\n";` [download] 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` [download]	[reply] [d/l] [select]
Re: Use command line argument in script by chacham (Prior) on Dec 19, 2014 at 17:13 UTC
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.	[reply]
Re^2: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 17:24 UTC
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.	[reply]
Re^3: Use command line argument in script by chacham (Prior) on Dec 19, 2014 at 17:37 UTC
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.	[reply]
Re: Use command line argument in script by Anonymous Monk on Dec 18, 2014 at 23:27 UTC
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"] [download] Modern Perl a loose description of how experienced and effective Perl 5 programmers work....You can learn this too.	[reply] [d/l]
Re: Use command line argument in script by ww (Archbishop) on Dec 19, 2014 at 16:50 UTC
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. *Questions containing the words "doesn't work" (or their moral equivalent) will usually get a downvote from me unless accompanied by:* code verbatim error and/or warning messages *a coherent explanation of what "doesn't work* actually means.**	[reply] [d/l] [select]
Re^2: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 16:52 UTC
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.	[reply]
Re^3: Use command line argument in script by homer4all (Acolyte) on Dec 19, 2014 at 17:05 UTC
Error message I get is `use of unitialized value in print at line` [download]	[reply] [d/l]