Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is it possible to CREATE TABLE .. AS and declare datatypes in 1 statement?

JacobatTheNewSchoolDec 14 2012 — edited Dec 14 2012
Hi,

11gr2, using 32-bit SQL Developer 3.2.20.09 build 09.87
Windows 7

Is it possible to use a "CREATE TABLE xxx AS SELECT .. FROM .. ;" statement AND within the same single statement declare the datatype for any literal values?

I am doing this:
CREATE TABLE JUNKITZ
 AS
    SELECT 'x' "TYPE" FROM DUAL
;
ALTER TABLE JUNKITZ
MODIFY ("TYPE" VARCHAR2(3))
;
In the first statement Oracle is creating the table and inserting the value successfully, but it sets the datatype of column "TYPE" as CHAR(1). Is it possible to declare the datatype as VARCHAR2(3) within the SELECT statement itself without executing the ALTER TABLE .. MODIFY statement? I attempted
CREATE TABLE JUNKITZ
 AS
    SELECT 'x' "TYPE" VARCHAR2(3) FROM DUAL
;
But it threw ORA-00923, naturally.

In reality, I want to create a table using a SELECT statement which co-mingles database elements AND literals. So, in that case I believe the database elements will inherit the source field's datatype - but I want to know if I can control the literal values datatypes within the same single statement.
thanks//jacob
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 11 2013
Added on Dec 14 2012
3 comments
2,393 views