3 Replies Latest reply on Aug 4, 2014 5:13 PM by Gary Graham-Oracle

-- Unable to render TABLE DDL for object with DBMS_METADATA attempting internal generator. ver 4.0.2

2725114 Aug 3, 2014 10:05 AM

I'm facing problem during extraction of DDL of specific tables (objects ??) using version 4.0.2 (and 4.0.1 as well), did not checked on older versions.

Database ORACLE 11.2.03 on Linux.

SQL Developer on 64 bit Windows.

The table is own by user DR as well as other tables.

Trying to extract DDL as table owner I'receiving

-- Unable to render TABLE DDL for object DR.T_STAFF_ABSENCE with DBMS_METADATA attempting internal generator.

ALTER TABLE T_STAFF_ABSENCE

ADD CONSTRAINT T_STAFF_ABSENCE_STAFF_FK FOREIGN KEY

(

STAFF_ID

)

REFERENCES T_STAFF

(

STAFF_ID

)

ENABLECREATE TABLE T_STAFF_ABSENCE

(.......................

When I tried to do using differ user (with more privileges) I'm receiving correct DDL

CREATE TABLE "DR"."T_STAFF_ABSENCE"

( "REQUEST_ID" NUMBER(9,0) NOT NULL ENABLE,

"STAFF_ABSENCE_ID" NUMBER(9,0),

"STAFF_EXTID" VARCHAR2(50 CHAR) NOT NULL ENABLE,

"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,

"ABSENCE_TYPE_CODE" VARCHAR2(80 CHAR) NOT NULL ENABLE,

"ABSENCE_FROM" DATE NOT NULL ENABLE,

"ABSENCE_END" DATE,

"OPERATION_TYPE" VARCHAR2(3 CHAR),

"OPERATION_USER_LOGIN" VARCHAR2(30 CHAR),

"OPERATION_TIME" DATE,

CONSTRAINT "T_STAFF_ABSENCE_PK" PRIMARY KEY ("STAFF_ABSENCE_ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

........................................

In schema DR I have as well other tables.

select * from all_tables where owner = 'DR' AND TABLE_NAME LIKE 'T_STAFF%'

DR	T_STAFF	TSDR_DEFAULT	VALID	10
DR	T_STAFF_ABSENCE	TSDR_DEFAULT	VALID	10
DR	T_STAFF_CASH_DEFFIC	TSDR_DEFAULT	VALID	10
DR	T_STAFF_GROUP	TSDR_DEFAULT	VALID	10
DR	T_STAFF_INCOME	TSDR_DEFAULT	VALID	10
DR	T_STAFF_RELATION	TSDR_DEFAULT	VALID	10
DR	T_STAFF_WEB_ACTIVITY	TSDR_DEFAULT	VALID	10

With some of them I have no problem to extract their DDL, for some i face the same issue.

When using more powerful user I have no problem.

Any idea what can be source of issue ??

1. Re: -- Unable to render TABLE DDL for object with DBMS_METADATA attempting internal generator. ver 4.0.2

2725114 Aug 3, 2014 10:45 AM (in response to 2725114)

Hi In addition

If I execute as user DR (owner)

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO'
     AND (u.iot_type is null or u.iot_type='IOT')
     and u.table_name like 'T_STAFF_ABSENCE%';

I'm receiving correct DDL

CREATE TABLE "DR"."T_STAFF_ABSENCE"
   ( "REQUEST_ID" NUMBER(9,0) NOT NULL ENABLE,
"STAFF_ABSENCE_ID" NUMBER(9,0),
"STAFF_EXTID" VARCHAR2(50 CHAR) NOT NULL ENABLE,
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"ABSENCE_TYPE_CODE" VARCHAR2(80 CHAR) NOT NULL ENABLE,
"ABSENCE_FROM" DATE NOT NULL ENABLE,
"ABSENCE_END" DATE,
"OPERATION_TYPE" VARCHAR2(3 CHAR),
"OPERATION_USER_LOGIN" VARCHAR2(30 CHAR),
"OPERATION_TIME" DATE,
CONSTRAINT "T_STAFF_ABSENCE_PK" PRIMARY KEY ("STAFF_ABSENCE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
......................

?????????????
Like Show 0 Likes(0)

Actions
2. Re: -- Unable to render TABLE DDL for object with DBMS_METADATA attempting internal generator. ver 4.0.2

rp0428 Aug 3, 2014 3:37 PM (in response to 2725114)

It isn't clear what your actual table DDL is that you say has a problem.

For example you post an ALTER statement:
ALTER TABLE T_STAFF_ABSENCE
But then you post DDL that you say is CORRECT but that DDL does NOT show that constraint.

Post the COMPLETE set of DDL for the tables involved.

The following works fine for me on Win 32 vanilla 11.2.0.1.0 and sql dev 4.0.2.15.21

drop table t_staff cascade constraints

create table t_staff (staff_id number (9,0) not null primary key)

CREATE TABLE "T_STAFF_ABSENCE"
   ( "REQUEST_ID" NUMBER(9,0) NOT NULL ENABLE,
"STAFF_ABSENCE_ID" NUMBER(9,0),
"STAFF_EXTID" VARCHAR2(50 CHAR) NOT NULL ENABLE,
"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,
"ABSENCE_TYPE_CODE" VARCHAR2(80 CHAR) NOT NULL ENABLE,
"ABSENCE_FROM" DATE NOT NULL ENABLE,
"ABSENCE_END" DATE,
"OPERATION_TYPE" VARCHAR2(3 CHAR),
"OPERATION_USER_LOGIN" VARCHAR2(30 CHAR),
"OPERATION_TIME" DATE,
CONSTRAINT "T_STAFF_ABSENCE_PK" PRIMARY KEY ("STAFF_ABSENCE_ID")
USING INDEX
)

ALTER TABLE T_STAFF_ABSENCE
ADD CONSTRAINT T_STAFF_ABSENCE_STAFF_FK FOREIGN KEY
(STAFF_ID) REFERENCES T_STAFF (STAFF_ID)
ENABLE

Then selecting the table in the nav tree and selecting the 'sql' tab it display the correct DDL

CREATE TABLE "BM"."T_STAFF_ABSENCE"

   ( "REQUEST_ID" NUMBER(9,0) NOT NULL ENABLE,

"STAFF_ABSENCE_ID" NUMBER(9,0),

"STAFF_EXTID" VARCHAR2(50 CHAR) NOT NULL ENABLE,

"STAFF_ID" NUMBER(9,0) NOT NULL ENABLE,

"ABSENCE_TYPE_CODE" VARCHAR2(80 CHAR) NOT NULL ENABLE,

"ABSENCE_FROM" DATE NOT NULL ENABLE,

"ABSENCE_END" DATE,

"OPERATION_TYPE" VARCHAR2(3 CHAR),

"OPERATION_USER_LOGIN" VARCHAR2(30 CHAR),

"OPERATION_TIME" DATE,

CONSTRAINT "T_STAFF_ABSENCE_PK" PRIMARY KEY ("STAFF_ABSENCE_ID")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "USERS" ENABLE,

CONSTRAINT "T_STAFF_ABSENCE_STAFF_FK" FOREIGN KEY ("STAFF_ID")

   REFERENCES "BM"."T_STAFF" ("STAFF_ID") ENABLE

   ) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "USERS" ;

Note that the DDL above DOES show both constraints including the one added with the ALTER statement. Your DDL does NOT show the second constraint so why do you say that DDL is correct?

Does that table have that second constraint or not? Start over and explain EXACTLY what table the error occurs on and WHAT constraints that table has on it.
Like Show 0 Likes(0)

Actions
3. Re: -- Unable to render TABLE DDL for object with DBMS_METADATA attempting internal generator. ver 4.0.2

Gary Graham-Oracle Aug 4, 2014 5:13 PM (in response to 2725114)

Probably an issue with privileges (since it occurs when not running as schema owner). Use of internal generator was supposed to be deprecated, but apparently still lives in 4.0.

See Joyce's comments in this earlier discussion: Re: Problems Exporting Tables to DDL
Like Show 0 Likes(0)

Actions

Go to original post