Re: Problem with Job Queue & DBMS_STATS

Single quotes are never a good idea unless you use bind variables. You have to do

dbms_job.submit(:jobno, replace ('&what', '''', ''''''), &start, replace ('&interval', '''', ''''''), TRUE, :instno);

to make sure you enter a valid string.

Martin

Roy Speaker wrote:
>
> 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 Sat Jun 23 2001 - 07:27:16 CDT