RE: Who Says Oracle does not listen

Igor Neyman Mon, 21 Jul 2003 05:27:02 -0700

Title: RE: Who Says Oracle does not listen

That’s not good enough.

I don’t want to discard ANY exception.

When dropping table, I don’t want to see error messages only if there is nothing to drop. While if let’s say there is a problem with privileges, it will go unnoticed.

Or, when adding a table, it’s fine not be getting an error, if table already exists. But, if there is no room to create a table, or to add a partition to the table, I want to see this error message.

So, I still prefer my way of doing it (see scripts in my original message) comparing to Oracle’s script, you refer to.

Igor Neyman, OCP DBA

[EMAIL PROTECTED]

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Friday, July 18, 2003 6:10 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Who Says Oracle does not listen

From ORACLE own script:

Rem
Rem Drop tables without raising errors if they do not exist
Rem
declare
   PROCEDURE drop_force(tab varchar2) IS
   BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE ' || tab;
   EXCEPTION WHEN OTHERS THEN
      NULL;
   END;
begin
   drop_force('utl_recomp_invalid');
   drop_force('utl_recomp_sorted');
   drop_force('utl_recomp_compiled');
   drop_force('utl_recomp_backup_jobs');
   drop_force('utl_recomp_log');
end;
/

Alex.

-----Original Message-----
From: Igor Neyman [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 18, 2003 2:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Who Says Oracle does not listen

Sure, I would.
But I can't wait till Oracle "turns around".
My scripts are executed by our "field" engineers, who know next to nothing about Oracle, and the only thing they can do is to check log files for error messages (and even this is done automatically).

Igor Neyman, OCP DBA
[EMAIL PROTECTED]

-----Original Message-----
Goulet, Dick
Sent: Friday, July 18, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L

Igor,

True enough, but wouldn't you like it as part and parcel of the command?

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Friday, July 18, 2003 4:35 PM
To: Multiple recipients of list ORACLE-L

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>

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]

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

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

RE: Who Says Oracle does not listen

Reply via email to