Oracle alter session doesn't make it through DBD

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

Trying to set NLS_TIMESTAMP_FORMAT in Oracle using DBI.

$dbh2->do('alter session set nls_timestamp_format = "YYYY-MM-DD HH24:MI:SS"'); doesn't.

connection is fine as I get before and after selects of sample dates around execution of above. Same alter session command works fine directly to database with sqlplus and under SQL Developer client.

Where might I start to find out what's up?

pat

Comment on Oracle alter session doesn't make it through DBD Download Code

Replies are listed 'Best First'.
Re: Oracle alter session doesn't make it through DBD by tilly (Archbishop) on Nov 10, 2007 at 02:05 UTC
First, do you get an error message? You can generate them in 2 ways: `# Ask for errors at database handle creation my $dbh2 = DBI->connect( $dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 } ); # Or check your method call $dbh2->do($sql) or die "Cannot execute SQL '$sql': $DBI::errstr;` [download] Those error messages are often helpful. Were I to guess, your problem is that you've used the wrong kind of quotes. Try `$dbh2->do( qq{alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS +'} ) or die "Can't set timestamp format: $DBI::errstr";` [download]	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Oracle alter session doesn't make it through DBD
by tilly (Archbishop) on Nov 10, 2007 at 02:05 UTC

# Ask for errors at database handle creation
my $dbh2 = DBI->connect(
  $dsn, $user, $password,
  { RaiseError => 1, AutoCommit => 0 }
);

# Or check your method call
$dbh2->do($sql) or die "Cannot execute SQL '$sql': $DBI::errstr;
[download]

  $dbh2->do(
    qq{alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS
+'}
  ) or die "Can't set timestamp format: $DBI::errstr";
[download]

[reply]
[d/l]
[select]