Hi!
I'm working with oracle, and wanting to manipulate the NLS_DATE_FORMAT. I
know I can change it in the init.ora file for my database, but I don't want
to affect all objects in the db, just my schema. So I wrote a trigger to
help me out:
CREATE OR REPLACE TRIGGER init_date
AFTER LOGON ON user.schema
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYY/MM/DD
HH:MI:SS"';
END;
It compiles, cool. So now I fire up SQLPLUS (connected as the user
specified in the trigger) and I do this:
SQL> select sysdate from dual;
SYSDATE
-------------------
2002/03/13 01:51:07
Voila! Everything works! So, now I write a snippet of perl, using DBI
(unecessary declarations removed for brevity):
my $dbh = DBI->connect("dbi:Oracle:SID", "user", "password");
my $sth = $dbh->prepare("SELECT sysdate FROM dual");
$sth->execute();
while(my $ref = $sth->fetchrow_arrayref()) {
print $ref->[0]."\n";
}
Fully expecting this to work, I execute it, and I get:
13-MAR-02
So I check that I'm using the right user, yup. I check using SQLPLUS again,
yup. So I add this to the program:
$dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS'");
And I execute it:
2002-03-13 01:54:35
Well, at least it works.
So can someone help me understand why it behaves this way? The trigger
fires perfectly when I use SQLPLUS, when I connect via TOAD (an Oracle
'IDE'), and when I run use Java & JDBC for the same query. It only shows up
when I connect via DBI.
Thanks!
Cory 'G' Watson
* What is the sound of Perl? Is it not the sound of a wall that people have
stopped banging their heads against?
