Re: PL/SQL ALTER SESSION SET Date format?

[Quoted] When you use get <filename>, all lines of the file are brought into SQLPlus [Quoted] as one sql statement. The slash at the bottom executes it. That's why it [Quoted] doesn't like line 1 - it's neither SQL nor PL/SQL.

You can use _at_testdate.sql to actually run the file. That would run line 1 [Quoted] first, then run the PL/SQL statement in lines 2 thru 6.

Incidentally, as someone else mentioned, you shouldn't need to set nls_date_format since you're using to_char. Every Oracle date field includes [Quoted] both date and time. The application may or may not set the time properly, [Quoted] and the application may set the time in a differrent date field. (There is [Quoted] no time type field, time values are stored in date fields.)

So even if your app stores date in one field and time in another, you should [Quoted] be able to pull it out using to_char. Setting NLS_DATE_FORMAT has some uses [Quoted] at the session level, but shouldn't be needed to use PL/SQL.

HTH, Yosi

newopt_at_my-deja.com wrote:

> 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';
> *
> ERROR at line 1:
> ORA-00911: invalid character
>
> 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 - 23:06:38 CET