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