Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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.