I am having trouble using date operations in Oracle via DBI.
The following code fails to return any results.
$selectStatement.="SELECT s.NETWORK_NAME as NETWORK_NAME,";
$selectStatement.=" TO_CHAR(s.OUTAGE_START,'MM/DD/YYYY HH24:MI:SS') as
OUTAGE_START,";
$selectStatement.=" TO_CHAR(s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS')
as IMPLEMENTATION_DATE";
$selectStatement.=" FROM SC.PFZOUTAGEM1 s";
$selectStatement.=" WHERE";
$selectStatement.=" (s.OUTAGE_START < TO_DATE(?,'dd-mm-yyyy hh24:mi:ss')";
$selectStatement.=" and s.OUTAGE_START >= TO_DATE(?,'dd-mm-yyyy hh24:mi:ss')";
$selectStatement.=")";
DBI->trace(2);
my $Qselect=$dbh->prepare($selectStatement);
$GMTIMEFUTURE='16-10-2005 16:48:00';
$GMTIME='16-10-2005 16:43:00';
$Qselect->execute($GMTIMEFUTURE, $GMTIME);
The following query in Toad Returns one row (I set the values so the query
would work).
SELECT s.NETWORK_NAME as NETWORK_NAME,
TO_CHAR(s.OUTAGE_START,'MM/DD/YYYY HH24:MI:SS') as OUTAGE_START,
TO_CHAR(s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS') as
IMPLEMENTATION_DATE
FROM SC.PFZOUTAGEM1 s
WHERE (s.OUTAGE_START < TO_DATE('16-10-2005 16:48:00','dd-mm-yyyy
hh24:mi:ss')
and s.OUTAGE_START = TO_DATE('16-10-2005 16:43:00','dd-mm-yyyy hh24:mi:ss'))
Here is the trace output from DBI:
DBI 1.48-nothread default trace level set to 0x0/2 (pid 21322)
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x5e2ce8)~0x626274 'SELECT
s.NETWORK_NAME as NETWORK_NAME, TO_CHAR(s.OUTAGE_START,
'MM/DD/YYYY HH24:MI:SS') as OUTAGE_START, TO_CHAR
(s.IMPLEMENTATION_DATE, 'MM/DD/YYYY HH24:MI:SS') as IMPLEMENTATION_DATE FROM
SC.PF
ZOUTAGEM1 s WHERE (s.OUTAGE_START < TO_DATE(?,'dd-mm-yyyy hh24:mi:ss') and
s.OUTAGE_START = TO_DATE(?,'dd-mm-yyyy hh24:mi:ss'))')
dbd_preparse scanned 2 distinct placeholders
fbh 1: 'NETWORK_NAME' NULLable, otype 1-> 5, dbsize 90/91, p90.s0
fbh 2: 'OUTAGE_START' NULLable, otype 1-> 5, dbsize 19/20, p19.s0
fbh 3: 'IMPLEMENTATION_DATE' NULLable, otype 1-> 5, dbsize
19/20, p19.s0
<- prepare= DBI::st=HASH(0x626d84) at InMaintenance.pl line 101
-> execute for DBD::Oracle::st (DBI::st=HASH(0x626d84)~0x626e98 '16-10-
2005 16:48:00' '16-10-2005 16:43:00')
bind :p1 <== '16-10-2005 16:48:00' (type 0)
bind :p1 <== '16-10-2005 16:48:00' (size 19/20/0, ptype 4, otype 1)
bind :p2 <== '16-10-2005 16:43:00' (type 0)
bind :p2 <== '16-10-2005 16:43:00' (size 19/20/0, ptype 4, otype 1)
dbd_st_execute SELECT (out0, lob0)...
dbd_st_execute SELECT returned (SUCCESS, rpc0, fn4, out0)
<- execute= '0E0' at InMaintenance.pl line 111
-> $DBI::err (*) FETCH from lasth=HASH
<- $DBI::err= undef
-> fetchrow_hashref for DBD::Oracle::st (DBI::st=HASH(0x626d84)~0x626e98)
1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER 'NAME')
1 <- FETCH= [ 'NETWORK_NAME' 'OUTAGE_START' 'IMPLEMENTATION_DATE' ] at
InMaintenance.pl line 131
1 -> fetch for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER)
1 <- fetch= undef at InMaintenance.pl line 131
<- fetchrow_hashref= undef at InMaintenance.pl line 131
-> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x626e98)~INNER)
<- DESTROY= undef
-- DBI::END
-> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x242b90)~0x5e2c88)
<- disconnect_all= (not implemented) at DBI.pm line 677
! -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x626274)~INNER)
! <- DESTROY= undef during global destruction
! -> DESTROY in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x5e2c88)
~INNER)
! <- DESTROY= undef during global destruction
Any advice would be helpful.
Matt Dougherty
[EMAIL PROTECTED]
