Create table if it does not exist, and enter one row after creating

Question

I need to create a table if it does not exist, and when it is created add a single row to it.

I'm new to oracle and PL/SQL so I basically need an equivalent of the following T-SQL:

IF OBJECT_ID('my_table', 'U') IS NULL
BEGIN
  CREATE TABLE my_table(id numeric(38,0), date datetime)    
  INSERT INTO my_table
  VALUES (NULL, 0)
END

Solution 1

if you want to check table creation

DECLARE count NUMBER;
BEGIN
count := 0;
SELECT COUNT(1) INTO count from user_tables WHERE table_name= 'MY_TABLE';
IF COL_COUNT = 0 THEN
EXECUTE IMMEDIATE 'create table ....';
END IF;
END;
/

A checking for DML .please note you have to sepcify your pk columns and values.

DECLARE count NUMBER;
BEGIN
count := 0;
SELECT COUNT(1) INTO count from MY_TABLE WHERE id= 0 and name='Something';
IF COL_COUNT = 0 THEN
EXECUTE IMMEDIATE 'insert into MY_TABLE (id,name) values(0,''something'') ';
END IF;
END;
/

also note I recomand to specify columns when you insert into a table

Solution 2

In my opinion, you should not be creating objects on the fly. You should think about your design before implementing it.

Anyway, if you really want to do it this way, then you need to do it programmatically in PL/SQL (ab)using EXECUTE IMMEDIATE.

However, I would prefer the CTAS i.e. create table as select if you want to create a table ta once with a single row. For example,

SQL> CREATE TABLE t AS SELECT 1 id, SYSDATE dt FROM DUAL;

Table created.

SQL> SELECT * FROM t;

        ID DT
---------- ---------
         1 29-MAY-15

SQL>

The table is created permanently.

If you are looking for a temporary table, which you could use to store session specific data , then look at creating Global temporary table.

From documentation,

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific

Comments

[Alex Poole]

No you can't; you can't use IF in plain SQL anyway, but if you could the SELECT 1 FROM my_table would still get ORA-00942. If you did this in a PL/SQL block you'd have to do the create and insert dynamically, and you can't use exists directly in PL/SQL either, only in SQL.

[Alex Poole]

Maybe also worth showing that the insert has to be done dynamically too? (And setting col_count - which you have inconsistent names for! - to zero is redundant.)

[Moudiz]

@AlexPoole okay ill add a checking for the insert

[Hawk]

@AlexPoole Thanks. I've suggested another way to do it.

[Alex Poole]

I meant in the same block, straight after the dynamic create. Doesn't quite make sense in a separate block; if the select works (because the table now exists) then the insert doesn't need to be dynamic. But the OP seemed to want to do create/insert as one step; so you would have the two execute immediate statements together in the first block?

[Moudiz]

@AlexPoole well I meant to provide a better way ? anyway ill include it ine one block

[Alex Poole]

The better way is to have a permanent table and not have to do this at all *8-)

[Laszlo Lugosi]

Hmm. Did you try your solutions? :) As @AlexPoole said your script will not compile, because MY_TABLE does not exists. The IF section either not compile but by different reason, you forgot declaring etc.. You need to check the existance of the table via selecting from user_tables, all_objects or something like that. So the NOT EXISTS is for queries, not for testing objects existance.