Re: ORA-00600 [19004]

 
Sonja, 

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@xxxxxx> 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: 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: 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

--
http://www.freelists.org/webpage/oracle-l



--- Links ---
   1 
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
http://www.freelists.org/webpage/oracle-l

Other related posts: