Hi Ravi; Thanks for your reply...
Double quotes didn't solve the problem. :( I notice however that LIKE produces what I want: ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY' SELECT * FROM $table WHERE START LIKE '08-09-1999' A bit more testing with the statement: SELECT START FROM $table with NLS_DATE_FORMAT set to 'DD-MM-YYYY' and 'DD-MON-YYYY HH24:MI' returns the correct format. Somehow introducing a "WHERE" causes a problem? Regards, Stacy. -----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Friday, 3 December 2004 12:02 PM To: Mader, Stacy (ATNF, Parkes) Cc: [EMAIL PROTECTED] Subject: Re: DBI and ALTER SESSION bug? Stacy, Alter session should work, i use it in my script. Try with double quotes instead of single quotes in your code. $dbh->do(qq{ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'}); regs, Ravi [EMAIL PROTECTED] wrote: >Greetings all; > >A question regarding setting NLS_DATE_FORMAT and getting DBI >to obey it... With the code below, the statement fails to >retrieve data although I know it is there. However, if >I modify the SQL statement to the following: > >SELECT * FROM POSITIONS WHERE TO_CHAR(START,'DD-MM-YYYY') = ? > >The script works! Having the TO_CHAR negates the need for >using the ALTER SESSION... > >Is this a bug? > >I'm using the following: > >Oracle8i (8.1.7) on Solaris 8/9 >DBI 1.30 >DBD::Oracle 1.12 > >Regards, > > Stacy Mader. > > > >use DBI; >use Math::Trig; > >my($dbh) = DBI->connect($orac_sid,$table,$passwd,'Oracle', > { RaiseError => 1, > AutoCommit => 1, > ChopBlanks => 1}); > >$dbh->do(q{ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'}); > >$sth = $dbh->prepare(qq{SELECT * FROM $table WHERE START = ?}); > >$sth->execute('08-09-1999'); > >while( @row = $sth->fetchrow ) { > > print "@row\n"; > >} > >$dbh->disconnect(); > > >
