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 (See ORA-06550.ora-code.com): 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 (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
>
>I also tried with exec immediate but with no luck.
>Can someone tell me how to do this?
>
>Regards,
>Sonja
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
