Skip to Main Content

CTAS command fails but CREATE TABLE AS SELECT succeeds

jjwmartinDec 13 2018 — edited Dec 13 2018

Good day,

There is no doubt a good reason for this (most likely to do with permissions) but it still looks humorous.

SQL> CTAS sh.customers jake.customers

Create Table As Select command failed to get DDL for table "SH"

SQL> DDL sh.customers

DDL Exception: sh.customers generation for TABLE failed - ORA-31608: specified o

bject of type COMMENT not found

ORA-06512: at "SYS.DBMS_METADATA", line 6480

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: at "SYS.DBMS_METADATA", line 6471

ORA-06512: at "SYS.DBMS_METADATA", line 9311

ORA-06512: at line 1

SQL> DESCRIBE sh.customers

Name                   Null?    Type

---------------------- -------- ------------

CUST_ID                NOT NULL NUMBER

CUST_FIRST_NAME        NOT NULL VARCHAR2(20)

CUST_LAST_NAME         NOT NULL VARCHAR2(40)

CUST_GENDER            NOT NULL CHAR(1)

CUST_YEAR_OF_BIRTH     NOT NULL NUMBER(4)

CUST_MARITAL_STATUS             VARCHAR2(20)

CUST_STREET_ADDRESS    NOT NULL VARCHAR2(40)

CUST_POSTAL_CODE       NOT NULL VARCHAR2(10)

CUST_CITY              NOT NULL VARCHAR2(30)

CUST_CITY_ID           NOT NULL NUMBER

CUST_STATE_PROVINCE    NOT NULL VARCHAR2(40)

CUST_STATE_PROVINCE_ID NOT NULL NUMBER

COUNTRY_ID             NOT NULL NUMBER

CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)

CUST_INCOME_LEVEL               VARCHAR2(30)

CUST_CREDIT_LIMIT               NUMBER

CUST_EMAIL                      VARCHAR2(50)

CUST_TOTAL             NOT NULL VARCHAR2(14)

CUST_TOTAL_ID          NOT NULL NUMBER

CUST_SRC_ID                     NUMBER

CUST_EFF_FROM                   DATE

CUST_EFF_TO                     DATE

CUST_VALID                      VARCHAR2(1)

SQL>

SQL> CREATE TABLE jake.customers

  2  AS SELECT * FROM sh.customers;

Table JAKE.CUSTOMERS created.

Can anyone confirm if this is the intended behaviour?  As well as what would be needed for the CTAS statement to succeed?

Regards,

jake

Comments
Post Details
Added on Dec 13 2018
1 comment
101 views