Oracle gravel of the day: no placeholders in ALTER SESSION

jdavidb on 2006-07-12T19:18:18

For some reason I cannot use placeholders in an ALTER SESSION statement. This works:

sub set_date_format
{
  my($dbh, $format) = @_;
  my $sql = <<"EOF";
    ALTER SESSION SET NLS_DATE_FORMAT = '$format'
EOF
  my $sth = $dbh->prepare($sql);
  $sth->execute;
}

But this does not:

sub set_date_format
{
  my($dbh, $format) = @_;
  my $sql = <<"EOF";
    ALTER SESSION SET NLS_DATE_FORMAT = ?
EOF
  my $sth = $dbh->prepare($sql);
  $sth->execute($format);
}

---

Very odd

Mr. Muskrat on 2006-07-12T20:45:15

I tried using bind_parms with a numbered parameter and with a named parameter both with and without a data type.

sub set_date_format_bind
{
  my($dbh, $format) = @_;
  my $sql = <<"EOF";
    ALTER SESSION SET NLS_DATE_FORMAT = ?
EOF
  my $sth = $dbh->prepare($sql) or die $DBI::errstr;
  $sth->bind_param(1, $format, SQL_LONGVARCHAR);
  $sth->execute() or die $DBI::errstr;
}

sub set_date_format_named_bind
{
  my($dbh, $format) = @_;
  my $sql = <<"EOF";
    ALTER SESSION SET NLS_DATE_FORMAT = :NDF
EOF
  my $sth = $dbh->prepare($sql) or die $DBI::errstr;
  $sth->bind_param(':NDF', $format, SQL_LONGVARCHAR);
  $sth->execute() or die $DBI::errstr;
}

The outcome is always the same. bind_param fails with ORA-01036: illegal variable name/number and the execute fails with ORA-02248: invalid option for ALTER SESSION.

So I tried doing it in SQL*PLUS.

SQL> declare
  2  ndf varchar2(20) := 'yyyymmdd';
  3  begin
  4  execute immediate 'alter session set nls_date_format = :ndf';
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
ORA-06512: at line 4

SQL> declare
  2  ndf varchar2(20) := 'yyyymmdd';
  3  begin
  4   execute immediate 'alter session set nls_date_format = ''' || ndf || '''';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
--------
20060712

It appears that it's an Oracle limitation but why? As the subject says, this is very odd.