PL/SQL ALTER SESSION SET Date format?

[new...@my-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.

[TurkBear]

The NLS_DATE_FORMAT change is not needed to get the result you want...

The DATE datatype format internally always includes the date/time info, so a
select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM)
should get the results you want into v_date - on my system, with a
NLS_DATE_FORMAT of DD-MON-YYYY, I get the following:

SQL> select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM') from dual;

TO_CHAR(SYSDATE,'FMMONDD,YYYYHHFM:MI:SSPM')
---------------------------------------------------------------------------
FEB 2,2000 4:01:58 PM

SQL>

hth,

>
new...@my-deja.com wrote:

-----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
http://www.newsfeeds.com The Largest Usenet Servers in the World!
------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----

[Scott Kronheim]

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

[new...@my-deja.com]

In article <3899D8A1...@synertechsystems.com>,

[new...@my-deja.com]

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

[new...@my-deja.com]

Thanks for the reply, but the problem is that the script I am actually
using (not the one i posted) retrieves a column from an Oracle table
that includes both date & time. If the session date format is not reset
to include both date & time my script variable only pulls in the date?

In article <3898a82e....@204.181.81.99>,

See Message body for real address wrote:
>
> The NLS_DATE_FORMAT change is not needed to get the result you want...
>
> The DATE datatype format internally always includes the date/time
info, so a
> select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM)
> should get the results you want into v_date - on my system, with a
> NLS_DATE_FORMAT of DD-MON-YYYY, I get the following:
>
> SQL> select to_char(sysdate,'fmMON DD,YYYY HHfm:MI:SS PM') from dual;
>
> TO_CHAR(SYSDATE,'FMMONDD,YYYYHHFM:MI:SSPM')
>
------------------------------------------------------------------------
---
> FEB 2,2000 4:01:58 PM
>
> SQL>
>
> hth,
>
> >

> -----------== Posted via Newsfeeds.Com, Uncensored Usenet News
==----------
> http://www.newsfeeds.com The Largest Usenet Servers in the
World!
> ------== Over 73,000 Newsgroups - Including Dedicated Binaries
Servers ==-----
>

[Yosi Greenfield]

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

You can use @testdate.sql to actually run the file. That would run line 1
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
both date and time. The application may or may not set the time properly,
and the application may set the time in a differrent date field. (There is
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
be able to pull it out using to_char. Setting NLS_DATE_FORMAT has some uses
at the session level, but shouldn't be needed to use PL/SQL.

HTH,

Yosi

new...@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...@synertechsystems.com>,
> Scott Kronheim <skro...@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
> >

[new...@my-deja.com]

yosi,

Why does this script prompt me for the sequence name?

1 Declare
2 cid INTEGER;
3 -- This procedure is used to create a new sequence used to
4 -- populate the seq_number column in each NCR table
5 -- with a unique ascending sequence number starting
6 -- with 1.
7 BEGIN
8 -- Open new cursor and return cursor ID.
9 cid := DBMS_SQL.OPEN_CURSOR;
10 -- Parse and immediately execute dynamic SQL statement built by
11 -- concatenating the sequence name to DROP & CREATE SEQUENCE
commands.
12 DBMS_SQL.PARSE(cid, 'DROP SEQUENCE ' || 'seqnumbernext',
dbms_sql.v7);
13 -- DBMS_SQL.PARSE(cid, 'CREATE SEQUENCE ' || 'seqnumbernext' ||
'START WITH 1 MINVALUE 1', dbms_sql.v7);
14 -- Close cursor.
15 DBMS_SQL.CLOSE_CURSOR(cid);
16 EXCEPTION
17 -- If an exception is raised, close cursor before exiting.
18 WHEN OTHERS THEN
19 DBMS_SQL.CLOSE_CURSOR(cid);
20 RAISE; -- reraise the exception
21* END;
22 /
Enter value for create: 'seqnumbernext'
old 11: -- concatenating the sequence name to DROP & CREATE SEQUENCE
commands.
new 11: -- concatenating the sequence name to DROP 'seqnumbernext'
SEQUENCE commands.

PL/SQL procedure successfully completed.

In article <38A1E4EE...@compuserve.com>,

[new...@my-deja.com]

Thanks, I wasn't aware that the parser would process any &, even in a
comment a s a request for a paramter value.

In article <881ma0$lkd$1...@nnrp1.deja.com>,