“DROP object IF EXISTS” in Oracle?

 

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) πŸ™‚

Author: Tim...

DBA, Developer, Author, Trainer.

10 thoughts on ““DROP object IF EXISTS” in Oracle?”

  1. 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…

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

  3. I hear what you’re saying, but that doesn’t help when running catalog.sql, catproc.sql or patches πŸ™

    Cheers

    Tim…

  4. … 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 … πŸ™‚

Comments are closed.