Re: PL/SQL setting date format

This one's easy: You load PL/SQL into the SQL-buffer and then try to run it, but you only can run what's in the buffer if it's a single SQL-statement or a single PL/SQL block: you've loaded both!

Instead of GETting the script and then running it, you should START the script (start scriptname or @scriptname). Underwater, the first statement will be loaded in the SQL-buffer and be run, after that the second will be loaded and run.

Jaap.

newopt_at_my-deja.com heeft geschreven in bericht <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.
Received on Thu Feb 03 2000 - 07:10:05 CST