CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
PhoenixDev
PhoenixDev

Reputation: 802

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

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

Upvotes: 3

Views: 16510

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

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

Upvotes: 1

user3278460
user3278460

Reputation:

Another approach is to use exception logic. I changed field names and types according to Oracle rules

declare
  eAlreadyExists exception;
  pragma exception_init(eAlreadyExists, -00955);
begin
  execute immediate 'CREATE TABLE my_table(id number, dateof date)';
  execute immediate 'INSERT INTO my_table VALUES (NULL, sysdate)';
  exception when eAlreadyExists then 
      null;  
end;

but may be it is not a good idea to create tables dynamically

Upvotes: 2

Moudiz
Moudiz

Reputation: 7377

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

Upvotes: 4

Hawk
Hawk

Reputation: 5170

You can use NOT EXISTS with select statement:

IF NOT EXISTS(SELECT 1 FROM my_table) THEN
CREATE TABLE my_table(id NUMBER, date date);
COMMIT;
INSERT INTO my_table(id, date) values (NULL, O);
COMMIT;
END IF;

UPDATE

According to the comment, I cannot use Exist directly in PL/SQL. So this is another way to do it:

begin
  select case
           when exists(select 1
                         from my_table)
           then 1
           else 0
        end into l_exists
   from dual;

  if (l_exists = 1)
  then
    -- anything
  else
     EXECUTE IMMEDIATE 'CREATE TABLE my_table(id NUMBER, date date)';
     EXECUTE IMMEDIATE 'INSERT INTO my_table(id, date) values (NULL, O)';
  end if;
 end;

Upvotes: 0

Related Questions