Re: PL/SQL ALTER SESSION SET Date format?

From: <newopt_at_my-deja.com>
Date: Fri, 11 Feb 2000 19:01:24 GMT
Message-ID: <881ma0$lkd$1_at_nnrp1.deja.com>

yosi,

[Quoted] 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.34F4610_at_compuserve.com>,   Yosi Greenfield <ygreenfield_at_compuserve.com> wrote:
> 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 _at_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
>
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 11 2000 - 20:01:24 CET