Re: ORA-00600 [19004]

From: Edgar Chupit <chupit_at_gmail.com>
Date: Fri, 26 Nov 2004 16:53:51 +0200
Message-ID: <a8f0771c0411260653142f8794@mail.gmail.com>

Actually job inherits NLS session environment from pl/sql block where this job is created/changed, so before calling dbms_job.change you can alter your current session parameters and than simply change/add your job.

Something like:

tt_at_OTIS> declare=20
  2 i integer;
  3 v varchar(1024);
  4 begin
  5 execute immediate 'alter session set nls_sort=3Dlatvian';   6 dbms_job.submit(i,'null;', sysdate+1 );   7 select nls_env into v from user_jobs where job =3D i;   8 dbms_output.put_line( 'before' );   9 dbms_output.put_line( v );
 10 execute immediate 'alter session set nls_sort=3Dbinary';  11 dbms_job.change(i, what=3D>'null;', next_date=3D>sysdate+2, interval= =3D>null );

 12    select nls_env into v from user_jobs where job =3D i;               =
      =20
 13    dbms_output.put_line('after');

 14 dbms_output.put_line( v );
 15 dbms_job.remove(i);
 16 end;
 17 /
before

NLS_LANGUAGE=3D'ENGLISH' NLS_TERRITORY=3D'AMERICA' NLS_CURRENCY=3D'$'
NLS_ISO_CURRENCY=3D'AMERICA' NLS_NUMERIC_CHARACTERS=3D'.,'
NLS_DATE_FORMAT=3D'DD-MON-RR' NLS_DATE_LANGUAGE=3D'ENGLISH' NLS_SORT=3D'LAT=

VIAN'
after

NLS_LANGUAGE=3D'ENGLISH' NLS_TERRITORY=3D'AMERICA' NLS_CURRENCY=3D'$'
NLS_ISO_CURRENCY=3D'AMERICA' NLS_NUMERIC_CHARACTERS=3D'.,'
NLS_DATE_FORMAT=3D'DD-MON-RR' NLS_DATE_LANGUAGE=3D'ENGLISH' NLS_SORT=3D'BIN=

ARY' As you can see this works, but if you will try this approach, it will not w= ork:

tt_at_OTIS> var i number;
tt_at_OTIS> alter session set nls_sort=3Dlatvian;

Session altered.

tt_at_OTIS> exec dbms_job.submit(:i,'null;', sysdate+1 );

PL/SQL procedure successfully completed.

tt_at_OTIS> select nls_env from user_jobs where job =3D :i;

NLS_ENV

---------------------------------------------------------------------------=

---

NLS_LANGUAGE=3D'ENGLISH' NLS_TERRITORY=3D'AMERICA' NLS_CURRENCY=3D'$' NLS_I= SO_CURRENCY
=3D'AMERICA' NLS_NUMERIC_CHARACTERS=3D'.,' NLS_DATE_FORMAT=3D'DD-MON-RR' NL= S_DATE_LANG
UAGE=3D'ENGLISH' NLS_SORT=3D'LATVIAN' tt_at_OTIS> exec dbms_job.change(:i, what=3D>'null;', next_date=3D>sysdate+2, interval=3D>null );

PL/SQL procedure successfully completed.

tt_at_OTIS> select nls_env from user_jobs where job =3D :i;                   =

  =20

NLS_ENV

---------------------------------------------------------------------------=

---

NLS_LANGUAGE=3D'ENGLISH' NLS_TERRITORY=3D'AMERICA' NLS_CURRENCY=3D'$' NLS_I= SO_CURRENCY
=3D'AMERICA' NLS_NUMERIC_CHARACTERS=3D'.,' NLS_DATE_FORMAT=3D'DD-MON-RR' NL= S_DATE_LANG
UAGE=3D'ENGLISH' NLS_SORT=3D'LATVIAN' As You can see in the second case job didn't inherited NLS environment from current session.

On Fri, 26 Nov 2004 12:49:12 +0100, Sonja =C5=A0ehovi=C4=87 <sonja.sehovic@= pbz.hr> wrote:
> Hi all!

>=20

> I's Oracle 9.2.0.4 on AIX 5.2
> The problem is with gathering statistics.
> As workaround Oracle suggested to set NLS_SORT=3D3Dbinary in the session =
=3D
> before collecting statistics.

>=20

> I wanted to put it in my daily job and tried this:

>=20

> BEGIN DBMS_JOB.CHANGE
> (job =3D3D> 21,
> next_date =3D3D> to_date('27-stu-2004 01:00:00 AM','dd-Mon-yyyy HH:MI:SS=
 =3D
> AM'),
> interval =3D3D> 'TRUNC(SYSDATE+1)+1/24',
> what =3D3D> 'alter session set NLS_SORT=3D3Dbinary;
> DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL =3D
> COLUMNS SIZE AUTO'',4, ''DEFAULT'',TRUE,NULL,NULL,''GATHER'');'
> );
> END;
> /

>=20

> BEGIN DBMS_JOB.CHANGE
> *
> ERROR at line 1:
> ORA-06550: line 1, column 93:
> PLS-00103: Encountered the symbol "ALTER" when expecting one of the =3D
> following:
> begin case declare exit for goto if loop mod null pragma
> raise return select update while with <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge
> <a single-quoted SQL string> pipe
> The symbol "update was inserted before "ALTER" to continue.
> ORA-06512: at "SYS.DBMS_JOB", line 79
> ORA-06512: at "SYS.DBMS_JOB", line 205
> ORA-06512: at "SYS.DBMS_JOB", line 185
> ORA-06512: at line 1

>=20

> I also tried with exec immediate but with no luck.
> Can someone tell me how to do this?

>=20

> Regards,
> Sonja

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
  Edgar

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 26 2004 - 08:50:04 CST