Hi group,
I am new to Toad. I want to create a new table in my database by using SELECT command to extract data from a existing table. The query is structured as below:
Create TABLE test AS (
SELECT *
FROM mydb.tablename)
When I tried to execute this query Toad prompt an error message " Failed ParseException cannot recognize input near SELECT * FROM in column specification"
Not sure why this query won't work in Toad.
Hmmm, this syntax should work, assuming that you are specifying your source table with the correct schema name, etc.
What happens if you remove the parentheses altogether? ... e.g.
Create TABLE test AS
SELECT * FROM mydb.tablename
Hi Gary,
Thanks for the help. I figured out what the problem was. Now that I have created a new table using the CREATE TABLE AS .. SELECT method. How do I change the datatype? Right now each column is set to string, I will need to change some cols to INT and VARCHAR. Is there any way I could edit them in Toad?
You cannot change the type of a column. There are two routes you can go, both rely on your character columns being able to convert without error
Base_table ( id varchar2(10));
Create another table with id having the correct type and insert the old records:
NEW_TABLE(ID NUMBER)
Insert into new_table(id) select id from base_table;
Another method is to add a column and update it:
alter table base_table rename column id to id_orig;
alter table base_table add (id number);
update base_table set id = id_orig;
Appreciate Brian chiming in here... if you're wondering, Joe, why you can't simply change the datatype for the existing column with Toad's table editor (or otherwise), it's because Oracle won't let you change the datatype on the fly for a column/table that already has data in it. Brian outlined a couple of good solutions.
Good Morning all,
Hi Joe.
Your source table must have had its columns defined as VARCHAR2 if the new table has the same.
When running this sort of query it's always best to describe/F4 the source table first. Just to be sure you will be getting what you want.
Look for numbers stored as strings, or worse, dates/times stored as strings (or numbers!) -- if you find some that are obviously wrong (by obviously, I mean using the wrong data type for the stored data values) then they need to be fixed on the new table.
I tend, mostly, to:
Create table new_t as select
<<list of good columns>>
from old_t
where 0=1;
Alter table new_t
add (
Old_broken_number_col number(..),
Old_broken_date_col date,
...
);
Insert into new_t
Select
<<list of good columns>>,
To_number(old_broken_number_col),
To_date(old_broken_date_col, 'format string')
from old_t;
-- run a few checks here to be sure we converted the broken columns ok.
Commit;
Then add indexes, constraints etc as required.
If you get errors converting from string to the correct data type, it means that your data in the old table is "corrupt" -- most likely down to storing the data in completely thevwrong data type. (One of many bug bears of mine!)
The format string hinted at above tells the to_date function what format the string date column data should be. Hopefully yyyy/mm/dd but mileage varies, esp in the USA where mm/dd/yyyy is deemed correct. 
HTH
Cheers,
Norm. [TeamT][Team new puppy 
]
Hi Joe,
You can use Toad's Rebuild Table feature to alter the datatype beyond what Oracle normally allows....within reason (for example, NUMBER to VARCHAR2). Find the table in the Schema Browser. Right-Click -> Rebuild -> Table. Double-click a column to change datatype.
This technique renames the original table, creates the new table as modified, copies data across (with transformations as needed), then finally adds the constraints, indexes, etc on the new table. Please note that in version 14.1, there is a bug where triggers are not automatically recreated on the new table, so you'd have to re-add them yourself. That will be fixed in 14.2)
-John
Thanks,
All of the recommendations are spot on and just require work on your part to get the end result.
There is a way to utilize create table as and get the datatypes you want right off the bat but there is different work involved using dynamics. see example(paste into toad or other editor to color code comments and such)
--create table statement in oracle is quite powerful now (later versions of Oracle)
-- it analyzes the table as it is created and does so in many cases faster than if I populated an existsing table and analyzed it afterwards.
-- this saves time
-- this saves code
-- deficiency is that create table decides the datatypes based on the data in the source table, NOT the column data type in that table
-- I find using the create gives me advantages. Using something similar to below I can also control the outcome tables datatypes and other attributes at the same time it is created
declare
i_query3n clob;
-- these come from an extensive library used for dynamics
-- the query can be simplified to remove all of these for simplicity's sake
tr_id_max integer;
p_source varchar2(30);
t_name varchar2(30);
work_owner varchar2(30);
mon_owner varchar2(30);
aud_type varchar2(20 char);
aud_source varchar2(100 char);
aud_date timestamp(6);
aud_day_id number;
tr_type varchar2(20);
begin
t_name := 'test';
mon_owner := 'mydb';
work_owner := 'mydb';
-- remaining variables get assigned values here... etc.
-- CHR(10) - used to move to a new line so the output is formatted in a way you can read it
-- CHR(39) - used to put a single quote around a variable value - serves to make dynamic coding easier
i_query3n := 'Create Table '||mon_owner||'.'|| t_name || ''
|| CHR (10)
|| 'as'
|| CHR (10)
|| '(select /*+ PARALLEL(TB1, 64) */ '
|| CHR (10)
|| ', cast('||tr_id_max||' as number) tr_id,
cast('||chr(39)||sysdate||chr(39)||' as date) tr_date,
cast('||chr(39)||to_char(sysdate, 'yyyymmdd')||chr(39)||' as varchar2(8 char)) tr_batch_id,
cast('||chr(39)||tr_type||chr(39)||' as varchar2(20 char)) tr_type,
cast( 0 as number) tr_l0_id,
cast('||chr(39)||p_source||chr(39)|| ' as varchar2(100 char)) tr_source '
|| CHR(10)
|| ', cast('||chr(39)||tr_type||chr(39)||' as varchar2(20 char)) aud_type,
cast('||chr(39)||aud_source||chr(39)||' as varchar2(100 char)) aud_source,
cast('||chr(39)||aud_date||chr(39)|| ' as timestamp) aud_date,
cast('||aud_day_id|| ' as number) aud_day_id '
|| CHR(10)
|| 'from '||work_owner||'.'|| t_name ||'' ||' TB1 '
|| CHR (10)
|| ') '
;
-- display the output of my query as it will be submitted to oracle
DBMS_OUTPUT.put_line (' ' || i_query3n);
-- execute my query
execute immediate i_query3n;
-- error trapping email routine and alternate processing is omitted but advised
end;
-- end of example
-- your query
Create TABLE test AS
SELECT * FROM mydb.tablename
-- looks something like this *paranthesis around the select are not required
i_query3n := 'Create Table '||mon_owner||'.'|| t_name || ''
|| CHR (10)
|| 'as'
|| CHR (10)
|| '(select /*+ PARALLEL(TB1, 64) */ '
|| CHR (10)
|| 'from '||work_owner||'.'|| t_name ||'' ||' TB1 '
|| CHR (10)
|| ') '
;
-- add cast statements to get the desired outcome datatypes you desire in the middleI haven't played with Toad's Rebuild Table feature, thanks, will put it on my list of things to explore!
Dog gone it John. Yet another time I am made aware of a feature of TOAD that would have saved me a ton of time. If I had ever used it, I had forgotten about it. I have been using TOAD to create a script of the table, then editing all of the object names appropriately.
Thanks for pointing this out.
It's a nice feature as long as the tables don't have too much data and the copying of data from old table to new table isn't a big deal. We just added the code that translates the data from one type to another a few versions ago...maybe 13.3 or 14.0.