Problem with Job Queue & DBMS_STATS

From: Roy Speaker <rspeaker_at_my-deja.com>
Date: 21 Jun 2001 12:07:33 -0700
Message-ID: <bbe82a99.0106211107.19ca6bc8@posting.google.com>

Hi,

I am trying to automate stats gathering with dbms_stats via the job queue.
I'm using a little script I wrote to simplify the submission process:

SQL> get c:\sql\submit_job
  1 REM
  2 REM script to automate submission of a job to job queue   3 REM

  4  REM	if passing variables to a stored procedure/package 
  5  REM	in the WHAT section, use 2 sets of quote marks:
  6  REM	   dbms_stats.gather_schema_stats(''HR'')
  7  REM
  8  REM	use to_date masking in the start section
  9  REM	do not use to_date masking in the interval section

 10 REM
 11 variable jobno number;
 12 variable instno number;
 13 begin
 14 select instance_number into :instno from v$instance;  15 dbms_job.submit(:jobno, '&what', &start, '&interval', TRUE, :instno);
 16 commit;
 17* end;

I ran it and entered the following (15 minute interval for testing):

SQL>
SQL> @c:\sql\submit_job
Enter value for what: dbms_stats.gather_schema_stats('HR') Enter value for start: to_date('06/21/2001 15:00','MM/DD/YYYY HH24:MI')
Enter value for interval: sysdate+1/96
old 3: dbms_job.submit(:jobno, '&what', &start, '&interval', TRUE, :instno);
new 3: dbms_job.submit(:jobno,
'dbms_stats.gather_schema_stats('HR')', to_date('06/21/2001 15:00','MM/DD/YYYY HH24:MI'), 'sysdate+1/96', TRUE, :instno);   dbms_job.submit(:jobno, 'dbms_stats.gather_schema_stats('HR')', to_date('06/21/2001 15:00','MM/DD/YYYY HH24:MI'), 'sysdate+1/96', TRUE, :instno);

                                                           *

ERROR at line 3:

ORA-06550: line 3, column 60: 
PLS-00103: Encountered the symbol "HR" when expecting one of the
following:

. ( ) , * @ % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like between is null is not || is dangling
The symbol ". was inserted before "HR" to continue.

For some reason it didn't like the single quotes around HR (the schema name).
I reran it and used 2 sets of single quotes, and it accepted it:

SQL> @c:\sql\submit_job
Enter value for what: dbms_stats.gather_schema_stats(''HR'') Enter value for start: to_date('06/21/2001 15:00','MM/DD/YYYY HH24:MI')
Enter value for interval: sysdate+1/96
old 3: dbms_job.submit(:jobno, '&what', &start, '&interval', TRUE, :instno);
new 3: dbms_job.submit(:jobno,
'dbms_stats.gather_schema_stats(''HR'')', to_date('06/21/2001 15:00','MM/DD/YYYY HH24:MI'), 'sysdate+1/96', TRUE, :instno);

PL/SQL procedure successfully completed.

SQL> @c:\sql\seejobs

Job Queue Listing for HR.WORLD

       JOB WHAT                                          NEXT_RUN     
       INTERVAL         B
---------- ---------------------------------------------
-------------------- ---------------- -
        12 dbms_stats.gather_schema_stats('HR')          06/21/2001
15:00     sysdate+1/96     N

SQL> spool off

However, it doesn't run, it fails.

Job Queue Listing for HR.WORLD

       JOB WHAT                                          NEXT_RUN     
       INTERVAL         B
---------- ---------------------------------------------
-------------------- ---------------- -
        12 dbms_stats.gather_schema_stats('HR')          06/21/2001
15:14     sysdate+1/96     N

SQL> select job, what, last_date, failures from dba_jobs;

       JOB WHAT                                          LAST_DATE  

FAILURES

---------- --------------------------------------------- ---------
----------
        12 dbms_stats.gather_schema_stats('HR')                       
     3

and from the alert log:

Thu Jun 21 15:00:33 2001
Errors in file n:\Oracle\admin\pcpw\bdump\pcpwSNP1.TRC:

ORA-12012: error on auto execute of job 12
ORA-06550: line 1, column 133:
PLS-00103: Encountered the symbol "" when expecting one of the

following:

   := . ( % ;
The symbol ";" was substituted for "" to continue.

Based on this information, can anybody point out what I'm doing wrong?

Thanks
Roy Received on Thu Jun 21 2001 - 14:07:33 CDT