Forum Stats

  • 3,757,164 Users
  • 2,251,204 Discussions
  • 7,869,747 Comments

Discussions

CTAS command fails but CREATE TABLE AS SELECT succeeds

jjwmartin
jjwmartin Member Posts: 8
edited Dec 13, 2018 2:46PM in SQLcl

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

Answers