How often do you need to build a new table to test something out?

How often have you typed CREATE TABLE AS SELECT…

If the answer to both of those question is ‘quite a bit Jeff!,’ then continue reading.

In SQLcl, we have built a new command simply called ‘CTAS.’

So let’s try it out.

What tables do I have to work with?

'tables' is a shorthand query we ship as a ALIAS example
‘tables’ is a shorthand query we ship as a ALIAS example

I’m lazy, so instead of typing ‘select table_name from user_tables’ I just types ‘tables’ and execute that.

It’s a burned-in sample of one of our ALIASes. With the ALIAS command you can save your own statements for re-use. Anyways, I want a copy of the EMPLOYEES table.

Running CTAS

CTAS existing_table new_table

So nothing has actually happened yet...
So nothing has actually happened yet…

So nothing has been executed, but what have we done really?

Well, we generated the DDL for the EMPLOYEES table, substituted the new table name PEEPS in instead, added the ‘as select * from EMPLOYEES’ to it, and threw THAT into the command buffer.

So, when I hit edit, we’ll launch the default editor (I have mine set to the inline editor in SQLcl, or you could use Notepad or vi or whatever)

Now I can make any changes I want or need.
Now I can make any changes I want or need.
make edits, change column name, change query, whatever - ctrl+R to execute
make edits, change column name, change query, whatever – ctrl+R to execute

I now have my new table. The more complicated your base table is, the more typing this will save you. Imagine partitions…

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

3 Comments

  1. Hi Jeff,

    Yesterday I have tried to backup dba_registry using ctas option in sqlcl , it was failed with error.

    SQL> ctas dba_registry dba_registry_backp
    Create Table As Select command failed to get DDL for table “DBA_REGISTRY”
    .
    But when i was executed create table as select * from dba_registry. it was worked.

    SQL> create table dba_registry_backup as select * from dba_registry;

    Table DBA_REGISTRY_BACKUP created.

    SQL>

    do we have any reason for this. if you have it can you please post it.

    • Yes, per the HELP, the CTAS command assumes you’re working with a table.

      SQL> help ctas
      CTAS
      ctas table new_table
      Uses DBMS_METADATA to extract the DDL for the existing table
      Then modifies that into a create table as select * from

      DBA_REGISTRY is a view, not a table.

  2. Jeff,

    Is there any chance I could get you guys to enhance the SQLcl ctas command as described below:
    You’ve done a great job making the CTAS work like any manual CTAS command, but with a lot less typing.

    However, this means it also has the same ‘flaw’ the “create table as select” in sql has.
    The latter did not evolve when we were given virtual columns.
    When you execute a “create table my_copy as select * from my_table”, any virtual columns in my_table will be real columns in my_copy.
    Sometimes this is what you want.
    Sometimes it’s not. In my case, most of the time.

    What I would like is for CTAS to have an extra parameter which tells it to convert virtual to real (current behaviour) or not.
    If not, the new table should have the same virtual columns as the source table.

    e.g.
    CTAS my_table my_copy keepvirtual

    I know I’m probably asking a lot.
    Now, you are almost done after performing a dbms_metadata.get_ddl.
    If I tell ctas to not convert the virtuals it would have to:
    – build the “create table as select” statement with the virtual columns filtered out.
    – build an alter statement to add the virtual columns.

    But then still, the columns would not necessarily (probably not) be in the same order as in the source table.
    For me, however, that would be less of a concern than the conversion to real columns.

    Thanks! (If not for the requested change, then at least for what SQLcl already is).

Reply To Erik van Roon Cancel Reply