Re: PL/SQL ALTER SESSION SET Date format

From: <newopt_at_my-deja.com>
Date: Wed, 09 Feb 2000 17:03:33 GMT
Message-ID: <87s6l2$joq$1@nnrp1.deja.com>

Jane I tried it but still get the error below? SQL> get testdate.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> /
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

                                                           *

ERROR at line 1:
ORA-00911: invalid character

In article <38989f85_4_at_news3.prserv.net>,   "Jane Schrock" <jschrock_at_us.ibm.com> wrote:
> Try a semi-colon after your alter session statement. You are issuing
two
> statements in your sqlplus script. The alter session and the
anonymous
> pl/sql block.
>
> Jane
>
> <newopt_at_my-deja.com> wrote in message news:87a5ko$v1
$1_at_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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 09 2000 - 11:03:33 CST