CODEWITHSUNDEEP
oracle-databasecreate-table
jaydeep
jaydeep

Reputation: 11

Oracle DB Create Table Only When Data Exists

When I try to create table using below statement CREATE TABLE <TABLE_NAME_1> AS SELECT * FROM <TABLE_NAME_2> WHERE <CONDITION_1> ;

I would like to understand is there any way, where oracle ignores creation of table when underlined select statement does not returns any rows.

i.e. if end users intent is to avoid unnecessary blank tables. Is there any way/feature provided by Oracle.

Upvotes: 0

Views: 135

Answers (1)

Popeye
Popeye

Reputation: 35900

CREATE TABLE <TABLE_NAME_1> 
AS 
SELECT * FROM <TABLE_NAME_2> WHERE <CONDITION_1> ;

Will create the table with structure even if there is no data in the table.

CTAS once executed will create the table anyway.

If you want to create the table only when data is present in the selected table then you have to use the PL/SQL block as follows:

DECLARE
    CNT NUMBER := 0;
BEGIN
    SELECT COUNT(1)
      INTO CNT
      FROM <TABLE_NAME_2>;

    IF CNT > 0 THEN
        EXECUTE IMMEDIATE 'CREATE TABLE <TABLE_NAME_1> AS 
                           SELECT * FROM <TABLE_NAME_2> WHERE <CONDITION_1>';
    END IF;
END;
/

Upvotes: 1

Related Questions