UPDATE2: { The following is better because it does a date comparison (over the past 24 hours). I found
this site useful for doing date math in Oracle.
SELECT * FROM ACS.USERS WHERE group_name = 'Full Network Access' AND u
+pdate_timestamp > sysdate-24/24
} # End Update2
UPDATE1: SOLVED!!
Queries like this worked:
SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Access'
+ AND TO_CHAR(update_timestamp) LIKE '15-JUN-2010%'
Thanks for all the help!! Either SQL Developer is massaging the code or it's a diference in the clients. Special thanks to Anonymous Monk, igelkott, tye, and bluescreen.
} # End Update1
I can't seem to get the date passed in correctly using DBIx::Simple. Re: [DBIx::Class] How to execute stored procedure? looked interesting but I tried something similar and it didn't change anything (and that doesn't explain problems passing in a hardcoded date).
In Oracle SQL Developer the following both work (note: update_timestamp is a DATE and the other two are VARCAR2s):
select * from users where group_name = 'Full Network Access' and updat
+e_timestamp like sysdate
select * from users where group_name = 'Full Network Access' and updat
+e_timestamp like '14-JUN-10'
However, in my Perl code I never get anything back.
#!/usr/local/perl510/bin/perl
use feature ":5.10"; # Use all new features in Perl 5.10
use strict; use warnings; use Data::Dumper;
use FindBin; use Getopt::Long; use IO::File;
use DBIx::Simple;
my $DEBUG = 1;
my $TEST = 0;
my $TEST_TO_DATE = 0;
my %db = (
cfg_file => '.inet_audit.db.cfg',
username => undef,
password => undef,
);
my @full_network_access; # From CsicoSecure (via Oracle)
#//////////////////////////////////////////////////////////////
sub options {
my $numopts = @ARGV;
Getopt::Long::Configure('bundling');
GetOptions(
'D|debug+' => \$DEBUG,
'T|test=i' => \$TEST,
'2|test_to_date+' => \$TEST_TO_DATE,
);
return;
}
#///////////////////////////////////////////////////////////
# Read DB Cfg file (i.e. username and password)
sub get_db_cfg {
# Sets the cfg file path and then reads in the username and passwo
+rd).
# Sets the following: $db{cfg_file}, db{username} $db{password}
return;
}
#///////////////////////////////////////////////////////////
sub get_users_from_db {
my @dt = split q{ }, uc scalar localtime;
my $timestamp =
$TEST==1 ? 'sysdate'
: $TEST==2 ? sprintf "'%02s-%03s-%04d %08s'", $dt[2], $
+dt[1], $dt[4], $dt[3]
: $TEST==3 ? sprintf "'%02s-%03s-%02d %08s'", $dt[2], $
+dt[1], substr($dt[4],2), $dt[3]
: $TEST==4 ? sprintf "'%02s-%03s-%02d %05s'", $dt[2], $
+dt[1], substr($dt[4],2), substr($dt[3],0,5)
: $TEST==5 ? sprintf "'%02s-%03s-%04d %05s'", $dt[2], $
+dt[1], $dt[4], substr($dt[3],0,5)
: $TEST==6 ? sprintf "'%02s-%03s-%04d'", $dt[2], $
+dt[1], $dt[4]
: $TEST==7 ? sprintf "'%02s-%03s-%02d'", $dt[2], $
+dt[1], substr($dt[4],2)
: $TEST==8 ? time
: $TEST==9 ? "TO_DATE('".time."')"
: 'nothing you have seen before!! :-)'
;
if( $TEST >= 1 ) {
if( $TEST_TO_DATE ) { $TEST = 2 }
else { $TEST = 1 }
}
my @sql = (
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Acce
+ss'}, # Works. The rest fail
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Acce
+ss' AND update_timestamp LIKE $timestamp},
qq{SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Acce
+ss' AND update_timestamp LIKE TO_DATE($timestamp)},
);
say "\$sql[$TEST]=$sql[$TEST]" if $DEBUG;
eval {
my $dbh = DBIx::Simple->connect( 'DBI:Oracle:nms', $db{usernam
+e}, $db{password},
{ RaiseError => 1, AutoCommit => 1} );
@full_network_access = $dbh->query( $sql[$TEST] )->flat;
};
print Data::Dumper->Dump([\@full_network_access, ],['full_network_
+access']) if $DEBUG;
if( $@ ) { die "SQL ERROR: $@"; }
return;
}
#///////////////////////////////////////////////////////////
MAIN: {
options();
get_db_cfg();
get_users_from_db();
exit 0;
}
Output looks like the following:
> ./show_db_error --test 1
$sql[1]=SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network
+ Access' AND update_timestamp LIKE sysdate
$full_network_access = [];
> ./show_db_error --test 2
$sql[1]=SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network
+ Access' AND update_timestamp LIKE '14-JUN-2010 17:50:49'
$full_network_access = [];
> ./show_db_error --test 7
$sql[1]=SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network
+ Access' AND update_timestamp LIKE '14-JUN-10'
$full_network_access = [];
Note that "SELECT user_id FROM ACS.USERS WHERE group_name = 'Full Network Access'" works, so the problem is with the "update_timestamp LIKE today's date" portion.
The --test 7 output works just fine in Oracle SQL Developer (whether I * it or go for just user_id).
Ideally I'd just like to run sysdate (--test 1) since all I need is today's date.
Okay, so I assume either I'm running into a different in SQL Developer and the Oracle client on my PC and the Oracle client installed on our Solaris 10 SPARC system I am running my Perl script on. Or a problem with DBIx::Simple. Or I'm missing something blindingly obvious. Any help, debugging tips, clue-by-fours, etc. are welcome!!
We're using Oracle 10g and it's running on Red Hat Enterprise Linux (4 or 5). As indicated, my script is running from a Solaris 10 SPARC system. And I'm running Oracle SQL Developer on my PC (Win XP Pro).
Elda Taluta; Sarks Sark; Ark Arks