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).
