RE: dbms_stats via dbms_job

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Tue, 10 Jun 2003 08:11:01 -0700
Message-ID: <F001.005AE47A.20030610075440@fatcity.com>

This should work:

declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname =>' || CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent => 10, block_sample => FALSE, CASCADE >= TRUE); end;', trunc(sysdate+1)+01/24,'sysdate+7');
end;
/

When passing parameters to stored procedure, you can not mix and match "positional" method with "naming": either you use formal parameters, or not.
It seems like you need "naming".
I used CHR(39) to get quotes around schema name.

Igor Neyman, OCP DBA
[EMAIL PROTECTED] -----Original Message-----
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L

Listers,

Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs

The following syntax works fine to run the procedure interactively execute
dbms_stats.gather_schema_stats(ownname=>'RPT_3G_MASTER',estimate_percent
=>10,cascade=>true);

but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script

declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC ADE>=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/

The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as Job What

2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE>=TRUE') ;

but that fails to run

Has anybody got any ideas.

I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more

John

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hallas, John, Tech Dev
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 10 2003 - 10:11:01 CDT

RE: dbms_stats via dbms_job - syntax question