Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to put date range from a perl & sql script
Top Forums Shell Programming and Scripting How to put date range from a perl & sql script Post 302543159 by pinpe on Friday 29th of July 2011 07:42:00 PM
Old 07-29-2011
How to put date range from a perl & sql script
Hi Guys,

Can someone please help me on adding/inserting a variable date to an sql scipt? Basically I want to assign a 7 days date range. As shown below..


Code:
#!/usr/bin/perl

use strict;
use Env qw(ORACLE_HOME);

my $SQLPLUS='/opt/oracle/product/10.1.0/db_1/bin/sqlplus -S system/password@PINPE';
my $outputdir="/var/opt/output/";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time-(86400));
my $date=sprintf("%d%02d%02d",$year+1900,$mon+1,$mday);

SET SERVEROUTPUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF

select ','||b.number, c.studentid, a.classgroup, a.section
from pmowner.classdatat a, pmowner.classt b, pmowner.usedt c
where a.CLASSDATADBID = b.CLASSDATADBID and b.number = c.number
and c.at between to_date('${date}000000','yyyymmddhh24miss') and to_date('${date}235959','yyyymmddhh24miss');


I want to use this variable $date...

It should show like this...

Code:
SET SERVEROUTPUT OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 132
SET FEEDBACK OFF

select ','||b.number, c.studentid, a.classgroup, a.section
from pmowner.classdatat a, pmowner.classt b, pmowner.usedt c
where a.CLASSDATADBID = b.CLASSDATADBID and b.number = c.number
and c.at between to_date('20110722000000','yyyymmddhh24miss') and to_date('20110731235959','yyyymmddhh24miss');
quit;

Thanks in advance.


Br,
Pinpe
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

perl script dies altogether when I put an & after it

ignore (2 Replies)
Discussion started by: hairytorus
2 Replies

2. Shell Programming and Scripting

Perl code to extract data from the range of date

Hi All, I'm still a newbie in perl programming. I have a data below say in test.tmp The output in test.tmp will be the same data as above sample in test.tmp . So after i get all the 4th column data within the range of month and year i need, then i will use the foreach () code to execute... (1 Reply)
Discussion started by: miskin
1 Replies

3. Shell Programming and Scripting

grep - date & time range

Hi, I need to search email files by date & time range in email files. The timezone is not important. Can someone plz advise how i can do this ? For e.g A user can specify only A single date A date range date & time range Below is part of the email file. (4 Replies)
Discussion started by: coolatt
4 Replies

4. Programming

How to put variable date from SQL Script

Hi Guys, Can someone please help me on adding/inserting a variable to an sql scipt? Basically I want to assign today's date. As shown below.. set head off; set linesize 300; set pagesize 200; spool /opt/oracle/temp/output.txt select value,count(*) as totalcount from pmowner.pinpebasev... (11 Replies)
Discussion started by: pinpe
11 Replies

5. Shell Programming and Scripting

Script on Date Range

Hi All, Can anybody help me out a Shell script which pulls the files based on date range Example ./test.sh start_date End_date (20110901 20110930) or ./test.sh ( if we don't provide any input) it should take sysdate-1 ( yesterdays date) it should have both conditions Plzz help me... (1 Reply)
Discussion started by: krux_rap
1 Replies

6. UNIX Desktop Questions & Answers

grep a range of time & date

how can i grep a range? i have a text file with the following text: result.log.00:2012/01/02 12:00:07.422 LOG STARTED HERE N6Kashya29MemoryShieldScheduler_AO_IMPLE, pid=8662/8658, config=(alertThreshold=10,alertLevel=0,killThreshold=7200,coreThreshold=0,full=1), deltaTime=0,... (1 Reply)
Discussion started by: boaz733
1 Replies

7. Shell Programming and Scripting

run script in time and date range

i need to run one script inside of other, and there is some terms - main script in scheduled in cron for everyday runing every 5min - i need to run /tmp/script2.sh after first 3 days in month - i need to run /tmp/script2.sh from 7-9AM, main script is runining all day all recommendations are... (1 Reply)
Discussion started by: waso
1 Replies

8. Shell Programming and Scripting

PERL, Date & Time issues

Hello All, This is my first script in PERL. Hence require your help in moving further. I have a script which should populate the values for Today, Yesterday output. For which I use timeFrame as a variable to obtain the time in hrs:mm as 10:00. All I want is, I want my timeFrame to start... (4 Replies)
Discussion started by: sathyaonnuix
4 Replies

9. Shell Programming and Scripting

Perl script to allocate next available IP from a range

Hi I am looking to automate the process in PERL of allocating IP addresses from a set range of addresses (for example a /22 network 10.10.224.1 - 10.10.227.254) I am able to query the IP addresses that are already in use in the above range, which will produce me a list like this for example... (3 Replies)
Discussion started by: hcclnoodles
3 Replies

10. UNIX for Advanced & Expert Users

To pass one month range in sql script

Hi Guys, i am having .sql script which inserts data from one table to another table based on date condition, i need to pass range on based on how many number of months, for e.g set timing on; whenever sqlerror exit failure; spool myscript.log append accept start_date... (7 Replies)
Discussion started by: rohit_shinez
7 Replies

LEARN ABOUT DEBIAN

dspam_maintenance

dspam_maintenance(1)						   User Commands					      dspam_maintenance(1)

NAME

       dspam_maintenance - remove old signatures and unimportant tokens from the DSPAM storage backend

SYNOPSIS

       dspam_maintenance [--profile=PROFILE] [--logdays=no_of_days] [--signatures=no_of_days] [--neutral=no_of_days] [--unused=no_of_days]
       [--hapaxes=no_of_days] [--hits1s=no_of_days] [--hits1i=no_of_days] [--without-sql-purge] [--with-sql-automatization] [--with-sql-autoup-
       date] [--purgescriptdir=directory] [--with-all-drivers] [--verbose]

DESCRIPTION

       dspam_maintenance  is  used to remove old signatures/tokens and unimportant tokens from the DSPAM storage backend and purge old log entries
       in user and system logs.

       This script can be called from the command line or run from within cron. Either add a line in your crontab  or  add  this  script  in  your
       cron.{hourly,daily,weekly,monthly}.  Running this script every hour might not be the best idea but it's your decision if you want to do so.

OPTIONS

       --profile=PROFILE

	      Specify a storage profile from dspam.conf. The storage profile selected will be used for all database connectivity.

	      NOTE: See dspam.conf for more information.

       --logdays=no_of_days

	      All log entries older than no_of_days days will be removed.

	      NOTE: Default is 31 days. (for more info: man dspam_logrotate)

       --signatures=no_of_days

	      All signatures older than no_of_daysdays will be removed.

	      NOTE:  Default  value  is  set by the PurgeSignatures option in dspam.conf, if this option is not set, the default value is 14 days.
	      This option is only used for the Hash driver.

       --neutral=no_of_days

	      Deletes all tokens from the target users database whose probability is between 0.35 and 0.65 (fairly neutral, useless data).

	      NOTE: Default value is set by the PurgeNeutral option in dspam.conf, if this option is not set, the default value is 90 days.

       --unused=no_of_days

	      Remove stale tokens which have not been used for a long period of time.

	      NOTE: Default value is set by the PurgeUnused option in dspam.conf, if this option is not set, the default value is 90 days.

       --hapaxes=no_of_days

	      Remove tokens with a total hit count below 5 (which will be assigned a hapaxial value by DSPAM).

	      NOTE: Default value is set by the PurgeHapaxes option in dspam.conf, if this option is not set, the default value is 30 days.

       --hits1s=no_of_days

	      Remove tokens with a single SPAM hit.

	      NOTE: Default value is set by the PurgeHits1S option in dspam.conf, if this option is not set, the default value is 15 days.

       --hits1i=no_of_days

	      Remove tokens with a single INNOCENT hit.

	      NOTE: Default value is set by the PurgeHits1I option in dspam.conf, if this option is not set, the default value is 15 days.

       --without-sql-purge

	      Do not use SQL based purging. Only run dspam_clean.

	      NOTE: Default is off (aka: use SQL based purging).

       --with-sql-optimization

	      Run VACUUM (for PostgreSQL/SQLite) and/or OPTIMIZE (for MySQL).

	      NOTE: Default is off (aka: do not use optimizations).

       --with-sql-autoupdate

	      Run SQL based purging with purge day values passed to dspam_maintenance.

	      NOTE: Default is off (aka: do not attempt to modify SQL instructions).

       --purgescriptdir=directory

	      Space separated list of directories where to search for SQL files used for the SQL based purging.

       --with-all-drivers

	      Process all installed storage drivers (not just the active driver).

	      NOTE: Default is true (aka: process all installed drivers).

       --verbose

	      Verbose output while running maintenance script.

EXIT VALUE

       0      Operation was successful.
       other  Operation resulted in an error. Run the script with --verbose to get more information about error.

COPYRIGHT

       Copyright (C) 2002-2010 DSPAM Project
       All rights reserved.

       For more information, see http://dspam.sourceforge.net.

SEE ALSO

       dspam(1), dspam_admin(1), dspam_clean(1), dspam_crc(1), dspam_dump(1), dspam_logrotate(1), dspam_merge(1), dspam_stats(1), dspam_train(1)

dspam_maintenance						   Apr 26, 2010 					      dspam_maintenance(1)
All times are GMT -4. The time now is 11:27 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy