To avoid those errors in my scripts I'm checking data dictionary for the "existence" of the object (fortunately, dynamic sql helps here):
REM Dropping synonym DECLARE lCounter integer; begin SELECT COUNT(*) INTO lSyn FROM dba_synonyms WHERE synonym_name = 'PRCPV_REPORT_INFO' AND OWNER = 'PUBLIC'; IF (lSyn = 1) THEN EXECUTE IMMEDIATE 'drop PUBLIC SYNONYM PRCPV_Report_Info'; END IF; end; / or: REM Adding column DECLARE lCounter integer; begin SELECT count(*) INTO lCounter FROM DBA_TAB_COLUMNS WHERE table_name = 'PRCP_MENU' AND column_name = 'MENU_NAME' AND owner = 'IPN_DBA'; IF (lCounter = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE prcp_menu ADD menu_name VARCHAR2(50) NULL'; END IF; end; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -----Original Message----- Goulet, Dick Sent: Friday, July 18, 2003 3:04 PM To: Multiple recipients of list ORACLE-L <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif> Update TAR Go to End <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott om.gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot tom.gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif> TAR Number 3169102.996 Open Date 17-JUL-03 19:18:03 Support Identifier 1208611 Name Richard Goul Priority 4 Last Update 18-JUL-03 15:22:33 Product Oracle Server - Enterprise Edition Product Version 9.2.0.1.0 Platform HP-UX PA-RISC (64-bit) Detailed Status Soft Close TAR Reference n/a BUG Reference n/a Abstract <http://metalink.oracle.com/images/black.gif> DROP OBJECT NO_FAIL <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott om.gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot tom.gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_top. gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_top .gif> Resolution History <http://metalink.oracle.com/images/black.gif> 17-JUL-03 19:18:03 GMT ### Selected Industry ### Discrete Manufacturing ### Reason current product functionality is insufficient. ### Today if you issue a "drop table <my_table>;" command and the table does not exist you get an error back. ### Detailed description of the Enhancement Request. ### It would be great if we had an addition to the drop object command, something like "drop table <my_table> no_fail;" which would always return "Table dropped" whether it existed or not. ### How the product can be changed to achieve the desired result. ### Have no idea. ### Reasons to consider the Enhancement Request. ### When one creates scripts you run them many times with "whenever sqlerror exit" or else have to look in a log file for errors. Many times not being able to drop an object is an error, but many times, like when your running a script for the first time, it isn't. Run any of the CAT scripts that you provide, you have a marathon time scanning the script for real errors, like "could not extend" and have to filter these nuisance errors. ### Business impact if the Enhancement is not considered. ### Well, many a DBA will continue to waste time reviewing these errors for no purpose. ### Enhancement is affecting an implementation milestone. ### NO ### Description of the business flow that is affected by this Enhancement ### Creating a database, installing any package third party or not, ETL processes, etc.... Contact me via : E-mail -> [EMAIL PROTECTED] 17-JUL-03 19:19:19 GMT TAR has been assigned to an analyst -- Sending email. 17-JUL-03 19:46:59 GMT PROBLEM =========== Today if you issue a "drop table <my_table>;" command and the table does not exist you get an error back. It would be great if we had an addition to the drop object command, something like "drop table <my_table> no_fail;" which would always return "Table dropped" whether it existed or not. PROBLEM VERIFICATION ======================== Is this what you are requesting? This should be incorporated in our cat*.sql scripts so the output log file doesn't show all these ignorable errors/warnings and dba only has to look for 'real' errors. 17-JUL-03 19:47:26 GMT Email Update button has been pressed -- Sending email. 17-JUL-03 20:15:38 GMT New info : That's a part thereof. The Cat scripts are an example. Allow me if you please: This is from the CATREP.SQL script: drop synonym dbms_offline_snapshot * ERROR at line 1: ORA-01434: private synonym to be dropped does not exist What would be the enhancement, general in nature not just the cat* scripts, would be to have an option on the command that allows it to not fail. Namely in this particular case the command would be "drop synonym dbms_offline_snapshot nofail;" which would have return "Synonym dropped.". 17-JUL-03 21:20:03 GMT This would have to be filed as an enhancement request and code won't be changed until after 10i as it is close to release. The thing is that these scripts are generic, they were created to be run on any version, environment, configuration, component, etc database for any user. If you don't have replication instsalled, it will give the error on all replication objects. Same with spatial or intermedia or adv security option, etc. It has to cover them all whether you have these components installed or not. Generally the only errors we are concerned with in the output file are the ones that cause the entire script to hang or abort. We totally agree with you in support as we also have to read through all this output whenever a customer has an installation/upgrade problem and uploads the cat*.sql output files. We have to go through and identify any errors that may cause problems in the database. 17-JUL-03 21:20:17 GMT Email Update button has been pressed -- Sending email. 18-JUL-03 14:45:20 GMT New info : Well, I did file this as an enhancement request, so having it in a future version was the desired end result. If it can be included in a patch to 10i or part of 11G, as I understand it's going to be called, then great. Mission accomplished. <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_left_bott om.gif> <http://metalink.oracle.com/images/metalink/generic/usaeng/kur_right_bot tom.gif> Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick 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).