Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

DBIx::Simple and Oracle dates

by Argel (Prior)
on Jun 14, 2010 at 23:17 UTC ( [id://844757]=perlquestion: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re: DBIx::Simple and Oracle dates
by bluescreen (Friar) on Jun 15, 2010 at 01:38 UTC

    Using the tool provided by the vendor is not exactly the same as using a database driver through any language, there are always subtle differences. My experience is more related to MySQL but it happened to me that things that in the client worked in a way, they worked differently using DBD::mysql

    I agree with igelkott that you should use = operator instead of a like, and if you column is timestamp and you need to match only the date then you should cast the column like:

    WHERE group_name = 'Full Network Access' AND TO_DATE(update_timestamp) + = sysdate
Re: DBIx::Simple and Oracle dates
by igelkott (Priest) on Jun 15, 2010 at 00:01 UTC

    Could be a mismatch in Oracle's NLS_DATE_FORMAT. Could check/set this with DateTime::Format::Oracle.

    If that's OK, should rewrite Option-1 to use a comparison since "like" isn't really appropriate here.

      Will look into that tomorrow. Why isn't "like" appropriate? I'm only specifiy e.g. a date and leaving the time out, so it's not an exact match. BTW, why doesn't 'sysdate' work? Something preventing Oracle from running it through the DBI?

      Elda Taluta; Sarks Sark; Ark Arks

        Why isn't "like" appropriate?

        Because you can't just make stuff up? "LIKE" is a specific operator for matching strings where "%" can be used in one as a wildcard. It isn't some generic "roughly similar to" DWIM magical operator. Perhaps you should check your documentation?

        Now, I often regret when I have to learn more about Oracle (because it usually means I've had to try to use Oracle) and I know Oracle does some rather bizarre things. But I'd still be rather surprised to find out Oracle took the string-comparing LIKE and overloaded it for testing if datetimes were "near".

        - tye        

        Why isn't "like" appropriate?

        As has been said, a useful LIKE statement needs wildcards (otherwise it's just testing equality). Not to stray too far OT, but you'd also need to cast your date to a string (using to_char). Basically, my advice (for what it's worth) is to stick with comparisons like <= for dates.

        The part about the nls setting is more important. You should insure that your DBIx connection spits out dates in the same format as your other Oracle clients. Who knows, you could be trying to compare these to ISO 8601 format.

Re: DBIx::Simple and Oracle dates
by Anonymous Monk on Jun 14, 2010 at 23:50 UTC
    Hi

    Do you need '%' on the '14-JUN-10'
    to match the timestamp?


    J.C.
      No, not that simple. :-( But good idea!

      Elda Taluta; Sarks Sark; Ark Arks

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://844757]
Approved by igelkott
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (5)
As of 2024-03-29 13:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found