BEGIN DBMS_JOB.CHANGE (job => 21, next_date => to_date('27-stu-2004 01:00:00 AM','dd-Mon-yyyy HH:MI:SS = AM'), interval => 'TRUNC(SYSDATE+1)+1/24', what => 'begin execute immediate ''alter session set NLS_SORT=binary''; DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL = COLUMNS SIZE AUTO'',4, ''DEFAULT'',TRUE,NULL,NULL,''GATHER''); end;' ); END; / Steve Sonja ©ehoviæ 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=3Dbinary in the session = >before collecting statistics. > >I wanted to put it in my daily job and tried this: > >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 = >AM'), > interval =3D> 'TRUNC(SYSDATE+1)+1/24', > what =3D> 'alter session set NLS_SORT=3Dbinary; > DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE,''FOR ALL = >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 = >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 > > -- //www.freelists.org/webpage/oracle-l