Create table if it doesn't exist and enter one row after creation
I need to create a table if it doesn't exist and when it is created add one row to it.
I'm new to oracle and PL / SQL, so I basically want the 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
source to share
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;
/
Checking for DML
. Note that you have to clear the columns and pk 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 that I recommend specifying columns when inserting into a table
source to share
Another approach is to use exclusion logic. I changed the 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 it may not be advisable to dynamically create tables
source to share
In my opinion, you shouldn't create objects on the fly. You should think about your design before implementing it.
Anyway, if you really want to do this, you need to do it programmatically in PL / SQL (ab) with EXECUTE IMMEDIATE .
However, I would prefer CTAS ie create table as select
if you want to create the ta table once with one 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 constantly.
If you're looking for a temporary table that you can use to store session-specific data, take a look at creating a global temporary table .
From the documentation,
Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates whether the data in the table is transaction specific (default) or session specific
source to share
You can use NOT EXISTS with a 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
As per 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;
source to share