PL/SQL ALTER SESSION SET Date format

From: <newopt_at_my-deja.com>
Date: Wed, 02 Feb 2000 20:55:55 GMT
Message-ID: <87a5ko$v1$1@nnrp1.deja.com>

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. Received on Wed Feb 02 2000 - 14:55:55 CST