CODEWITHSUNDEEP
javaoracle-databasejdbi
user3634446
user3634446

Reputation: 133

How to support CREATE TABLE IF NOT EXISTS for oracle database for jdbi?

We have a framework where we need to create a table if it doesn't exist. TABLE IF NOT EXISTS is not supported for Oracle. Any suggestion on how to implement it for Oracle database using jdbi?

Upvotes: 3

Views: 297

Answers (1)

TenG
TenG

Reputation: 4004

Here is one approach for you.

Create function that returns TRUE is the table exists, FALSE if it doesn't:

   CREATE OR REPLACE FUNCTION fn_exists ( p_table IN VARCHAR2 ) RETURN BOOLEAN IS
      l_dummy NUMBER;
      l_found BOOLEAN := FALSE:
   BEGIN
      SELECT 1 INTO l_dummy
      FROM   user_tables
      WHERE table_name = p_table;
      l_found := SQL%FOUND;
      RETURN ( l_found );
   EXCEPTION
      WHEN no_data_found THEN
         RETURN ( FALSE );
      WHEN OTHERS THEN
         RAISE:
   END fn_exists;

Then, use it like this:

   BEGIN
      IF NOT fn_exists ( 'THE_TABLE' ) THEN
         EXECUTE IMMEDIATE ' CREATE TABLE THE_TABLE ....';
      END IF;
   END;

Or a procedure to wrap this:

   CREATE OR REPLACE PROCEDURE pr_crt_if_not_exists 
   (
      p_table   IN VARCHAR2, 
      p_crt_sql IN VARCHAR2
   ) IS
   BEGIN
       IF NOT fn_exists ( p_table ) THEN
          EXECUTE IMMEDIATE p_crt_sql;
       END IF;
   END pr_crt_if_not_exists;

Then use:

   BEGIN
      pr_crt_if_not_exists ( 'THE_TABLE', 'CREATE TABLE THE_TABLE ...' );
      pr_crt_if_not_exists ( 'THE_TABLE2', 'CREATE TABLE THE_TABLE2 ...' );
      .
      .
   END;

Upvotes: 1

Related Questions