Re: PL/SQL ALTER SESSION SET Date format

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_at_my-deja.com> wrote in message news:87a5ko$v1$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.
Received on Wed Feb 02 2000 - 16:15:34 CST