Re: Help: Using DBMS_SESSION to set NLS_DATE

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 07 Jul 1998 15:52:43 GMT
Message-ID: <35a24240.10573483@192.86.155.100>

A copy of this was sent to gaverill_at_chsra.wisc.edu (Gerard M. Averill) (if that email address didn't require changing) On Mon, 06 Jul 98 21:14:36 GMT, you wrote:

>Am I missing something obvious here!? I'm looking to programatically set the
>NLS_DATE_FORMAT parameter using the SET_NLS procedure in the system package
>DBMS_SESSION. I can set this (and other formats):
>
>begin
> dbms_session.set_nls('NLS_DATE_FORMAT', '''MM/DD/YYYYHH:MI:SS''');
>end;
>
>but not this (which is, of course, what I want):
>
>begin
> dbms_session.set_nls('NLS_DATE_FORMAT', '''MM/DD/YYYY HH24:MI:SS''');
>end;
>
>The following exception is raised:
>
>ORA-06510: PL/SQL: unhandled user-defined exception
>ORA-06512: at "SYS.DBMS_SESSION", line 46
>ORA-06512: at line 2
>
>Any ideas would be greatly appreciated. TIA,
>
>Gerard
>
>----
>Gerard M. Averill, Associate Researcher
>CHSRA, University of Wisconsin - Madison
>GAverill_at_chsra.wisc.edu

In 7.3 and lower, the lowest NLS_DATE_FORMAT string dbms_session will take is 18 characters (fixed in 8.0.3)...

In the meanwhile, you can create your own stored procedure that does the same without that limit. It would look like:

SQL> create or replace procedure set_date_fmt( fmt in varchar2 ) 2 as

  3      exec_cursor     integer default dbms_sql.open_cursor;
  4      rows_processed  number  default 0;
  5  begin
  6      dbms_sql.parse(exec_cursor,
  7                    'alter session set nls_date_format = "'||fmt||'"',
  8                     dbms_sql.native );
  9      rows_processed := dbms_sql.execute(exec_cursor);
 10      dbms_sql.close_cursor( exec_cursor );
 11  exception
 12      when others then
 13        if dbms_sql.is_open(exec_cursor) then
 14          dbms_sql.close_cursor(exec_cursor);
 15        end if;
 16        raise;

17 end;
18 /

Procedure created.

SQL> select to_char( sysdate ) from dual;

TO_CHAR(SYSDATE)

07-Jul-98

SQL> exec set_date_fmt( '''MM/DD/YYYY HH24:MI:SS''' );

PL/SQL procedure successfully completed.

SQL> select to_char( sysdate ) from dual;

TO_CHAR(SYSDATE)

'07/07/1998 11:32:28'

Just make sure the owner of the procedure has been granted the ALTER SESSION privelege directly (not via a role)....

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 07 1998 - 10:52:43 CDT

Re: Help: Using DBMS_SESSION to set NLS_DATE_FORMAT