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

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

    2725114

      Hi

      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%'

       

       

      DRT_STAFFTSDR_DEFAULTVALID10
      DRT_STAFF_ABSENCETSDR_DEFAULTVALID10
      DRT_STAFF_CASH_DEFFICTSDR_DEFAULTVALID10
      DRT_STAFF_GROUPTSDR_DEFAULTVALID10
      DRT_STAFF_INCOMETSDR_DEFAULTVALID10
      DRT_STAFF_RELATIONTSDR_DEFAULTVALID10
      DRT_STAFF_WEB_ACTIVITYTSDR_DEFAULTVALID10

       

      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

          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

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

           

          ?????????????

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

            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.

            • 3. Re: -- Unable to render TABLE DDL for object with DBMS_METADATA attempting internal generator. ver 4.0.2
              Gary Graham-Oracle

              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