ORA-00600 [19004] 2004-11-26 - By Edgar Chupit
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@(protected) > 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@(protected) > var i number;
tt@(protected) > alter session set nls_sort=3Dlatvian;
Session altered.
tt@(protected) > exec dbms_job.submit(:i, 'null; ', sysdate+1 );
PL/SQL procedure successfully completed.
tt@(protected) > 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@(protected) > exec dbms_job.change(:i, what=3D > 'null; ', next_date=3D >sysdate+2,
interval=3D >null );
PL/SQL procedure successfully completed.
tt@(protected) > 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 (See ORA-06550.ora-code.com): 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 (See ORA-06512.ora-code.com): at "SYS.DBMS_JOB ", line 79
> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_JOB ", line 205
> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_JOB ", line 185
> ORA-06512 (See ORA-06512.ora-code.com): 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
|
|
