Re: PL/SQL ALTER SESSION SET Date format?

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.

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.
Received on Thu Feb 03 2000 - 20:36:01 CET