Oracle DB Create Table Only When Data Exists

Question

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.

Sounds to me like there is a larger problem to be addressed. Who's creating tables on the fly like this without knowing the conditions that would dictate whether they want/need to create the new table? — EdStevens
– EdStevens, Commented Nov 2, 2020 at 13:32

Popeye · Accepted Answer · 2020-11-02 10:55:05Z

1

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;
/

answered Nov 2, 2020 at 10:55

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle DB Create Table Only When Data Exists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related