I'm pretty sure the table variable name isn't the problem :) The column 'START' is of type DATE.
Surely if one sets the date format with NLS_DATE_FORMAT, you SHOULDN'T need to use other functions like TRUNC? Stacy... -----Original Message----- From: Reidy, Ron [mailto:[EMAIL PROTECTED] Sent: Friday, 3 December 2004 12:59 PM To: Mader, Stacy (ATNF, Parkes); [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: DBI and ALTER SESSION bug? Show us your Purl code. I am suspicious of you use of a variable for a table name. Also, if the column 'START' is a DATE-is type of column and your bind variable does not contain a time component, use the TRUNC() function on 'START'. -- Ron Ready Lead DBA Array BioPharma, Inc. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 02, 2004 6:15 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: DBI and ALTER SESSION bug? 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(); > > > This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
