Xref: alice comp.databases.oracle.misc:17233
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.bbnplanet.com!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Help: Using DBMS_SESSION to set NLS_DATE_FORMAT
Date: Tue, 07 Jul 1998 15:52:43 GMT
Organization: Oracle Government
Lines: 96
Message-ID: <35a24240.10573483@192.86.155.100>
References: <6nrep2$g7g$1@news.doit.wisc.edu>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to gaverill@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@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@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.