Xref: alice comp.databases.oracle.misc:52392
From: "Jane Schrock" <jschrock@us.ibm.com>
Newsgroups: comp.databases.oracle.misc
References: <87a5ko$v1$1@nnrp1.deja.com>
Subject: Re: PL/SQL ALTER SESSION SET Date format
Date: Wed, 2 Feb 2000 16:15:34 -0600
Lines: 41
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
Message-ID: <38989f85_4@news3.prserv.net>
X-Trace: 2 Feb 2000 21:20:05 GMT, 198.133.22.71
Organization: Global Network Services - Remote Access Mail & News Services
X-Complaints-To: abuse@prserv.net
Path: alice!news-feed.fnsi.net!netnews.com!newsfeed.enteract.com!hermes.visi.com!news-out.visi.com!uunet!ffx.uu.net!newsfeed2.us.ibm.net!ibm.net!news3.prserv.net!198.133.22.71

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@my-deja.com> wrote in message news: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.