Re: ORA-00600 [19004]

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@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@OTIS> var i number;
tt@OTIS> alter session set nls_sort=3Dlatvian;

Session altered.

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

PL/SQL procedure successfully completed.

tt@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@OTIS> exec dbms_job.change(:i, what=3D>'null;', next_date=3D>sysdate+2,
interval=3D>null );

PL/SQL procedure successfully completed.

tt@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

Other related posts: