CREATE TABLE AS SELECT * ….. with additional or NULL columns?

So, in Oracle, how can you add additional columns or “NULL-out” existing columns when creating a copy of a table? Here’s something you might try:

SQL> DESC orig_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 COL1                                               VARCHAR2(10)
 COL2                                               NUMBER


SQL> CREATE TABLE bad_copy AS
  2  SELECT col1, col2, NULL col3
  3  FROM orig_tab;
SELECT col1, col2, NULL col3
                   *
ERROR at line 2:
ORA-01723: zero-length columns are not allowed

This actually came up when creating an updatable materialized view for use with Oracle Advanced Replication. In this particular case, an existing column needed to be “NULLed-out” so it would be present for inserts, but refreshed data would not be visible in the materialized view. It turned out the CAST() function did the trick. Not only is this function useful for materialized views, but it can be used any time you want to “NULL-out” a column when creating a copy table, or adding a new column not defined in the original table. Here’s an example:

SQL> SELECT * from orig_tab;

COL1             COL2
---------- ----------
val1                1
val2                2

SQL> CREATE TABLE copy_tab AS
  2  SELECT col1, col2, CAST( NULL AS NUMBER ) col3
  3  FROM orig_tab;

Table created.

SQL> DESC copy_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 COL1                                               VARCHAR2(10)
 COL2                                               NUMBER
 COL3                                               NUMBER

SQL> SELECT * FROM copy_tab;

COL1             COL2       COL3
---------- ---------- ----------
val1                1
val2                2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s