Re: PL/SQL setting date format

From: <newopt_at_my-deja.com>
Date: Wed, 09 Feb 2000 17:11:58 GMT
Message-ID: <87s74q$k6e$1@nnrp1.deja.com>

If I include a semicolon at the end of the alter session statment I get this error?
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 <949527698.8372.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> 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
> <newopt_at_my-deja.com> wrote in message news:87a5im$t9
$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:11:58 CST