Xref: alice comp.databases.oracle.server:83126 Path: alice!news-feed.fnsi.net!news.idt.net!newsfeed.mathworks.com!newsfeed2.news.nl.uu.net!sun4nl!bullseye.news.demon.net!demon!news.demon.nl!demon!sybrandb.demon.nl!not-for-mail From: "Sybrand Bakker" Newsgroups: comp.databases.oracle.server Subject: Re: PL/SQL setting date format Date: Wed, 2 Feb 2000 22:39:29 +0100 Message-ID: <949527698.8372.0.pluto.d4ee154e@news.demon.nl> References: <87a5im$t9$1@nnrp1.deja.com> Reply-To: "Sybrand Bakker" X-Trace: news.demon.nl 949527698 pluto:8372 NO-IDENT sybrandb.demon.nl:212.238.21.78 X-Complaints-To: abuse@nl.demon.net X-Newsreader: Microsoft Outlook Express 5.00.2014.211 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211 Lines: 45 Yes, because there's no semi-colon or / following your SQL statement. You could as well place this statement in your login.sql (this file is automatically executed the first time you connect to the database in sqlplus) Hth, -- Sybrand Bakker, Oracle DBA wrote in message news:87a5im$t9$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.