Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: dbms_stats via dbms_job - syntax question SOLVED

RE: dbms_stats via dbms_job - syntax question SOLVED

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Wed, 11 Jun 2003 06:43:17 -0700
Message-ID: <F001.005AF013.20030611060953@fatcity.com> 





> But one thing I proved was that you can mix and match positional and
naming parameters



Hmm, I'd still rather use one OR another: you never know if mixing them
would work in the next version...


Why trying your luck?-:)



Igor Neyman, OCP DBA


[EMAIL PROTECTED]




-----Original Message-----


Hallas, John, Tech Dev


Sent: Wednesday, June 11, 2003 4:35 AM


To: Multiple recipients of list ORACLE-L



Thanks for all the help. The problem was not with my coding but the
syntax. In my worked example I had CASCADE>=TRUE whereas it should have
been  


CASCADE=>TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.



So all the answers that I said were incorrect were not, they had all
replicated my original typo. 



But one thing I proved was that you can mix and match positional and
naming parameters



The final working version for anyone who is interested is 



declare


l_job number;


begin


dbms_job.submit


(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD

E=>TRUE);',



trunc(sysdate+1)+01/24,'sysdate+7');


end;


/



Thanks to all



John
 




-----Original Message-----


Sent: 11 June 2003 09:24


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: 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 Wed Jun 11 2003 - 08:43:17 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US