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?
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 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)
I now have my new table. The more complicated your base table is, the more typing this will save you. Imagine partitions…
3 Comments
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.
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).