Oracle: If Table Exists

Handling different types of objects

Not only tables, you might need to check the existence of other DB objects as well. Here's how to do this dynamically:

DECLARE
    v_exists NUMBER;
BEGIN
    -- Swap DBA_OBJECTS, YOUR_OBJECT, OBJECT_TYPE as appropriate
    SELECT COUNT(1) INTO v_exists FROM dba_objects WHERE object_name = 'YOUR_OBJECT' AND object_type = 'OBJECT_TYPE';
    IF v_exists = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Object exists.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Object does not exist.');  -- Oh well, an object's absence makes the heart grow fonder!
    END IF;
END;


Remember to capitalize the object name and type for consistency and apply UPPER when dealing with user input!

Dealing with conditional drops

Prior to Oracle 23c, an error handling routine was crucial to conditionally drop tables. Now, IF EXISTS simplifies this process. But, if you're on older versions or when IF EXISTS is unavailable, use this:

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE your_table';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -942 THEN
            DBMS_OUTPUT.PUT_LINE('Table did not exist.');  -- Tables, they grow up so fast and leave home!
        ELSE
            RAISE;
        END IF;
END;


This routine checks for SQLCODE -942, which flags non-existent tables. Modify to suit other objects and SQLCODEs.

Watch out for the CASCADE!

When dropping objects, be extra cautious with CASCADE. It might wipe more than you asked for!

For scheduled jobs, always use DBMS_SCHEDULER.drop_job, managing exceptions like the table drop example. This avoidance action ensures no jobs linger in the shadows.

Dabble with dynamic SQL

Sometimes you need to create or alter tables dynamically.

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE your_table AS SELECT * FROM existing_table WHERE 1=0'; -- Weekend cloning experiment!
END;


Add EXECUTE IMMEDIATE with a SELECT ... INTO for dynamic table creation:

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_count FROM user_tables WHERE table_name = UPPER('your_table');
    IF v_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE your_table (...)'; -- I dream of tables!
    END IF;
END;


Remember, error handling is a must in dynamic SQL!

Halt scripts on error

Need your script to stop at the first sign of trouble? Here's a command for that:

WHENEVER SQLERROR EXIT SQL.SQLCODE; -- Stop, Hammer Time!


This command will stop your script like a traffic light at any SQL error, leaving the error to the calling environment.