Re: PL/SQL ALTER SESSION SET Date format?

From: <no.spam_at_columbus.rr.com>
Date: Thu, 03 Feb 2000 20:45:52 GMT
Message-ID: <4Olm4.5$0d4.612_at_typhoon.columbus.rr.com>

Your best bet is just use the DBMS_SESSION package that is supplied with Oracle.

Do this instead:

declare

   v_date varchar2(30);
BEGIN
   DBMS_SESSION.SET_NLS('nls_date_format','''YYYY MM DD HH24:MI:SS''');    select to_char(SYSDATE,'fmMon DD, YYYY HHfm:MI:SS PM')

     into v_date 
     from dual;

END; The first argument would be what you would normally type at the SQL*Plus prompt when you issue ALTER SESSION SET param = value.

The second parameter is 'value' in the ALTER SESSION.

This is especially good if you're storing a package and need to do this per each running instance of the package but don't want to set it in the init.ora file.

newopt_at_my-deja.com wrote:
: I want to change the default date format in a PL/SQL script to include
: both the date and time using the ALTER SESSION function. It's a real
: hassle to change the NLS_DATE_FORMAT in the init.ora file (you have to
: add the statement & then spin down the database to activate the change).

: Here's a PL/SQL script that should reset the default date format but
: fails.

: Any help would be appreciated!

: SQL> get setdateformat.sql
: 1 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
: 2 declare
: 3 v_date varchar2(30);
: 4 BEGIN
: 5 select to_char(SYSDATE,'fmMon DD, YYYY HHfm:MI:SS PM') into v_date
: from dual;
: 6* END;
: SQL> /
: declare
: *
: ERROR at line 2:
: ORA-00922: missing or invalid option

: If you create a PL/SQL script with just the:
: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
: statement it works fine, but introduce anything else after this
: statement & it errors out??

: Sent via Deja.com http://www.deja.com/
: Before you buy.

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Ivan Samuelson                 * 
Staff Support Coordinator and  * isamuels_at_columbus.rr.com
Information Systems Consultant *
Metro Information Services     * http://home.columbus.rr.com/isamuels
http://www.MetroIS.com         *
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Received on Thu Feb 03 2000 - 21:45:52 CET