John, My code doesn't work probably because "CASCADE >= TRUE" should actually be: "CASCADE => TRUE". So, 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; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -----Original Message----- Hallas, John, Tech Dev Sent: Wednesday, June 11, 2003 3:24 AM To: Multiple recipients of list ORACLE-L Thanks for the try Igor but that doesn't work either ERROR at line 1: ORA-06550: line 2, column 24: PLS-00201: identifier 'CASCADE' must be declared ORA-06550: line 1, column 99: Wolfgang's suggestion doesn't work and I have tried every permutation of quotes (including none at all) around the cacscade keyword as per Raj' suggestion with still no success. Thomas's suggestion of using a procedure was my failback position and is probably the way I will have to go. I am still frustrated that I cannot get the syntax to work correctly. Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it fails when I try a dbms_job.run Thanks to everyone anyway. John -----Original Message----- Sent: 10 June 2003 16:55 To: Multiple recipients of list ORACLE-L 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). -- 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).