Forum Stats

  • 3,733,133 Users
  • 2,246,702 Discussions
  • 7,856,530 Comments

Discussions

CTAS command fails but CREATE TABLE AS SELECT succeeds

jjwmartin
jjwmartin Member Posts: 8
edited December 2018 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

Sign In or Register to comment.