Re: PL/SQL ALTER SESSION SET Date format?

[Quoted] Scott, I made the change 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';

                                                           *

In article <3899D8A1.AD258384_at_synertechsystems.com>,   Scott Kronheim <skronheim_at_synertechsystems.com> wrote:
> I believe you need a semicolon after the first line of your script:
> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
> declare
> etc.
>
> ALTER SESSION, as an SQL statement, needs the semicolon to make it
> distinct from the PL/SQL block started by the DECLARE keyword.
>
> -----
> Scott Kronheim
>
> newopt_at_my-deja.com wrote:
> >
> > 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 - 17:56:39 CET