Create a table in Oracle SQL If table does not exist using select and join

Question

The question is very common, but I am facing one single issue in there for which I am not able to find the answer. What I have to do is, create a table if the table does not exist. But while creating the table, I need to create it from the select query(which is result of JOIN of two tables). This is for "ORACLE SQL DEVELOPER" / "PL/SQL".

The query I am using is:

DECLARE
  count_matching_tbl BINARY_INTEGER := 0;  
BEGIN  
  SELECT COUNT(*)
  INTO count_matching_tbl
  FROM dba_tables 
  WHERE LOWER(table_name) = 'testtable';

  IF(count_matching_tbl = 0)
  THEN
    EXECUTE IMMEDIATE ( ' CREATE TABLE testtable AS (SELECT A.*, B.* from tab1 A JOIN tab2 B ON A.id = B.RID WHERE 1=2)');
  END IF;
END;

What is the problem with that query? Do you get an error? What error? Does it not do what you want? What is it doing or not doing correctly? Although creating a table at runtime generally indicates a poor architecture, my guess is that the table name in the data dictionary is upper case and you are looking for a lower case table name in your `select`. There may be other errors or that may be a typo. — Justin Cave, Jul 06 '16 at 12:29
@JustinCave: The error I am getting is ' not properly terminated. And also due to the parallel execution of the code, the rest of the statements after the if clause are executed before, hence getting the error: table or view does not exist. — Mitali, Jul 07 '16 at 07:05
You are missing a `/` after the PL/SQL block: http://stackoverflow.com/a/10207695/330315 — , Jul 07 '16 at 12:20
Possible duplicate of [How to access Oracle system tables from inside of a PL/SQL function or procedure?](http://stackoverflow.com/questions/2602536/how-to-access-oracle-system-tables-from-inside-of-a-pl-sql-function-or-procedure) — Prashant Mishra, Jul 07 '16 at 12:22
That code works fine for me, running in SQL*Developer. How are you executing your anonymous block? Also, I don't understand what you mean by parallel execution of the code. This is PL/SQL. It's procedural - your commands are run in series not in parallel. Your IF statement is being processed at exactly the right time. It might help all of us if you explained what has led you to believe otherwise — Christian Palmer, Jul 07 '16 at 12:29
@Mitali - There is no "parallel execution". A PL/SQL block is compiled before it is executed. If there are static references to tables that don't exist, the compilation will fail. If you want to dynamically create the table, all subsequent references to the table in the same block (and any block that might be executed before the table is created) would need to be dynamic. My guess is that you're actually getting a compilation error rather than a runtime execution error. What is the full error stack (ORA-xxxxx error code, the exact text of the message, call stack, etc.). — Justin Cave, Jul 07 '16 at 15:25
@JustinCave Yes, this was the issue, I have created two anonymous block, where the rest of the statements are executing after the creation of the table and it is working fine now. — Mitali, Jul 08 '16 at 11:15

score 0 · Answer 1 · answered Jul 06 '16 at 12:31

0

If tab1 and tab2 table has same name column then ambiguity occure while creating table and inserting record so instead of * replace table column which is add in testtable like

CREATE TABLE testtable AS (SELECT A.cola1, B.colb1 from tab1 A JOIN tab2 B ON A.id = B.RID WHERE 1=2

answered Jul 06 '16 at 12:31

Sanjay Radadiya

1,254
15
22

Hi Sanjay, Thanks. But still I am facing the issue. There is one more problem of parallel execution in oracle SQl, even before the create table is executed, the statements after the IF Clause are getting executed, hence getting the error: the table or view does not exist – Mitali Jul 07 '16 at 07:03

score 0 · Answer 2 · answered Jul 07 '16 at 12:18

0

ask your dba to give "select on dba_tables " to your schema .

Since you are using a pl/sql procedure , you need to have permissions granted directly to you rather than through a role.

answered Jul 07 '16 at 12:18

Prashant Mishra

619
9
25

score 0 · Accepted Answer · answered Jul 08 '16 at 11:24

The issue was because of the static SQL data. The table was created dynamically, but the rest of the statements were trying to access the static data. To solve this, I have created two anonymous blocks, the first one having the create table statement, commit it and end the block. The next anonymous block will have the rest of the statements to be executed after the IF clause.

This solved my problem.

DECLARE

  count_matching_tbl BINARY_INTEGER := 0;  

BEGIN  

  SELECT COUNT(*)
  INTO count_matching_tbl
  FROM dba_tables 
  WHERE LOWER(table_name) = 'testtable';

  IF(count_matching_tbl = 0)

  THEN

    EXECUTE IMMEDIATE ( ' CREATE TABLE testtable AS (SELECT A.*, B.* from tab1 A JOIN tab2 B ON A.id = B.RID WHERE 1=2)');

  COMMIT;

  END IF;

END;

/

BEGIN

-- Rest of the database execution statements
COMMIT;
END;
/

Create a table in Oracle SQL If table does not exist using select and join

3 Answers3