It works, thanks!! Regards, Sonja -----Original Message----- From: Steve Jelfs [mailto:steve@xxxxxxxxxxxxxx] Sent: Friday, November 26, 2004 3:26 PM To: Sonja =A9ehovi=E6 Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: ORA-00600 [19004] BEGIN DBMS_JOB.CHANGE (job =3D> 21, next_date =3D> to_date('27-stu-2004 01:00:00 AM','dd-Mon-yyyy HH:MI:SS = =3D AM'), interval =3D> 'TRUNC(SYSDATE+1)+1/24', what =3D> 'begin execute immediate ''alter session set NLS_SORT=3Dbinary''; DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL = =3D COLUMNS SIZE AUTO'',4, ''DEFAULT'',TRUE,NULL,NULL,''GATHER''); end;' ); END; / Steve Sonja =A9ehovi=E6 wrote: >Hi all! > >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. > >I wanted to put it in my daily job and tried this: > >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; >/ > >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 > >I also tried with exec immediate but with no luck. >Can someone tell me how to do this? > >Regards, >Sonja > >-- >//www.freelists.org/webpage/oracle-l > =20 > -- //www.freelists.org/webpage/oracle-l