When you run installations and patches you often have log files full of error messages that can be ignored, such as dropping objects that don’t exist prior to creating them, or in some cases creating objects that already exist. As a result, trying to identify “real” errors is like looking for a needle in a haystack.
It would be nice if these meaningless errors could be supressed. I guess it would be a little like the IGNORE=Y option in the import utility.
Of course, there is a judgement call about what constitutes a real error. In some cases, an object not being present when you try to drop it signifies something is very wrong, but for many scripts it’s no big deal.
If Oracle included a “DROP object IF EXISTS” syntax like mySQL, and maybe even a “CREATE object IF MISSING” syntax, it would be a real bonus.
I feel an enhancement request coming on π
Cheers
Tim…
Update: The enhancement request has now been logged on Metalink (ER# 5151826) π
I agree, based on the number of times I’ve felt slightly silly explaining this to a trainee DBA ….
I’ve logged the enhancement request π
Cheers
Tim…
How about an extension of “CREATE OR REPLACE” to all objects?
I can see the appeal. Perhaps the dependency checking might be a pain for the replace…
They’ve already rejected my, “DUDE, MAKE IT GO FASTER” clause. π
Cheers
Tim…
Hi,
for migration of databse objects i created an UTIL_MIG package where i can more dynamically controll DDL without Errors and even rerun the script :
SET SERVEROUTPUT ON
EXEC dbms_output.enable(2000000);
EXEC eventlogmgr.Begintask;
DECLARE
l_Backup_Tag VARCHAR2(8) := ‘#10’;
BEGIN
Dbms_Output.Put_Line(‘.’);
Util_Mig.Msg(‘MAIN’, ‘Executing Script mig_TEXT_KEY_VARCHAR2_TO_INTEGER.sql’);
Dbms_Output.Put_Line(‘.’);
FOR l_Tab IN (SELECT *
FROM User_Tables Ut
WHERE Ut.Table_Name IN
(‘COMPANYSTATETEXT’, ‘COMPANYROLETEXT’,
‘ACTIVITYCONFIDENTIALTEXT’, ‘ACTIVITYSTATETEXT’,
‘ACTIVITYPRIORITYTEXT’, ‘ACTIVITYFREQUENCYTEXT’,
‘TRADINGSYSTEMTEXT’, ‘CONTACTSTATETEXT’,
‘RESEARCHPUBSTATUSTEXT’, ‘RESEARCHDISTRIBUTIONTEXT’,
‘RESEARCHDOCTYPETEXT’, ‘RESEARCHLANGPREFTEXT’,
‘EMPLOYEESTATETEXT’, ‘APPROVALTEXT’, ‘REFERENCEDBYTYPE’)) LOOP
BEGIN
Util_Mig.Msg(‘MAIN’,
‘Migrating Table ‘ || l_Tab.Table_Name);
IF (NOT
Util_Mig.Table_Exists(p_Table_Name => l_Tab.Table_Name || l_Backup_Tag)) THEN
Util_Mig.Backup_Table(p_Source_Table_Name => l_Tab.Table_Name,
p_Backup_Tag => l_Backup_Tag);
END IF;
Util_Mig.Drop_Unique_Constraints(p_Table_Name => l_Tab.Table_Name);
Util_Mig.Truncate_Table(p_Table_Name => l_Tab.Table_Name);
Util_Mig.Modify_Column(p_Table_Name => l_Tab.Table_Name,
p_Column_Name => ‘KEY’,
p_Column_Spec => ‘NUMBER(4) DEFAULT -1’);
IF (Util_Mig.Column_Is_Nullable(p_Table_Name => l_Tab.Table_Name,
p_Column_Name => ‘KEY’)) THEN
Util_Mig.Modify_Column(p_Table_Name => l_Tab.Table_Name,
p_Column_Name => ‘KEY’,
p_Column_Spec => ‘NOT NULL’);
END IF;
…
Greetings
Karl
I hear what you’re saying, but that doesn’t help when running catalog.sql, catproc.sql or patches π
Cheers
Tim…
Yes yoo are right
for the catalog stuff is’s a different battlefield
Greetings
Karl
set term off
drop table ..
drop table ..
drop table ..
set term on
… works fine for me … π
… but, of course, it prevents only on-screen error … I don’t like what these messages do to my blood pressure when I run a script but I’m more resilient when reviewing log files … π
in oracle user dropped but still exists what to do?