How to implement CREATE TABLE IF NOT EXISTS in Oracle

Unlike PostgreSQL and MySQL, Oracle doesn't provide a direct CREATE TABLE IF NOT EXISTS syntax. However, there are several reliable methods to achieve the same functionality. This article explores the best approaches to conditionally create tables in Oracle databases.

Understanding the Challenge

When developing database applications, you often need to ensure that a table exists before performing operations. While other databases offer straightforward solutions, Oracle requires a different approach. The good news is that Oracle provides powerful PL/SQL capabilities to handle this requirement effectively.

Solution 1: Using PL/SQL Block

The most common and reliable approach is to use a PL/SQL block with exception handling:

BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE employees (
      id NUMBER PRIMARY KEY,
      name VARCHAR2(100),
      hire_date DATE
   )';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
         NULL; -- Table already exists
      ELSE
         RAISE;
      END IF;
END;
/

How it Works:

1. The PL/SQL block attempts to create the table
2. If the table already exists, Oracle raises error ORA-955
3. The exception handler catches this specific error and continues silently
4. Any other errors are re-raised for proper error handling

Solution 2: Using Dynamic SQL with User_Tables

A more verbose but explicit approach is to check the data dictionary first:

DECLARE
   v_table_exists NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_table_exists
   FROM user_tables
   WHERE table_name = 'EMPLOYEES';

   IF v_table_exists = 0 THEN
      EXECUTE IMMEDIATE 'CREATE TABLE employees (
         id NUMBER PRIMARY KEY,
         name VARCHAR2(100),
         hire_date DATE
      )';
   END IF;
END;
/

Advantages:

• More explicit and easier to understand
• Allows for additional checks before table creation
• Avoids exception handling

Disadvantages:

• Slightly more verbose
• Small possibility of race conditions in high-concurrency environments

Solution 3: Single-Line Alternative

For simple scripts where PL/SQL might be overkill, you can use:

SELECT DECODE(COUNT(*), 0, 'CREATE TABLE employees (id NUMBER PRIMARY KEY, name VARCHAR2(100), hire_date DATE)') 
FROM user_tables 
WHERE table_name = 'EMPLOYEES';

Best Practices

1. Case Sensitivity: Remember that Oracle stores table names in uppercase by default. Always use consistent casing in your checks.

2. Permissions: Ensure the executing user has proper privileges:

   • CREATE TABLE permission
   • SELECT permission on USER_TABLES

3. Error Handling: Always implement proper error handling for production code:
   

   DECLARE
      v_sql VARCHAR2(4000);
   BEGIN
      v_sql := 'CREATE TABLE employees (
         id NUMBER PRIMARY KEY,
         name VARCHAR2(100),
         hire_date DATE
      )';

      EXECUTE IMMEDIATE v_sql;
   EXCEPTION
      WHEN OTHERS THEN
         IF SQLCODE = -955 THEN
            DBMS_OUTPUT.PUT_LINE('Table already exists');
         ELSE
            DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
            RAISE;
         END IF;
   END;
   /

Performance Considerations

When implementing conditional table creation, consider these performance factors:

1. Concurrency: The PL/SQL exception handling approach (Solution 1) is generally more concurrency-friendly
2. Execution Plan: Checking USER_TABLES first might be more efficient for single-threaded scripts
3. Memory Usage: Dynamic SQL requires less memory than storing the entire table structure in PL/SQL variables

Conclusion

While Oracle doesn't provide a direct CREATE TABLE IF NOT EXISTS syntax, the PL/SQL exception handling approach offers a robust and reliable solution. For simpler scripts, the USER_TABLES check method provides a more readable alternative. Choose the approach that best fits your specific use case, considering factors like concurrency, readability, and maintenance.

Remember to always test your implementation thoroughly, especially in environments where multiple sessions might attempt to create the same table simultaneously.

References

1. if statement - Check table exist or not before create it in Oracle - Stack Overflow
2. How to Implement CREATE TABLE IF NOT EXISTS in various RDBMSs | Learn Database Online