Perhaps you should elaborate on the failure with EXECUTE IMMEDIATE - It
*should* work (however, DDL statements are not allowed as such in PL/SQL).
I suggest you write a very simple wrapper procedure eg COLLECT_STATS, which
does an EXECUTE IMMEDIATE 'alter session blah ' then calls DBMS_STATS. Test
it, then make the job run this procedure.
Regards,
Stephane Faroult
RoughSea Ltd
http://www.roughsea.com
On Fri, 26 Nov 2004 12:49 , Sonja ?ehovi? <sonja.sehovic@(protected) > sent:
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
< <
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
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?
