Re: PL/SQL ALTER SESSION SET Date format?

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_at_nnrp1.deja.com>,   newopt_at_my-deja.com wrote:
> 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.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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 14 2000 - 18:34:00 CET