This discussion is archived
14 Replies Latest reply: Nov 14, 2012 6:30 AM by JoyceScapicchio RSS

Problems Exporting Tables to DDL

973518 Newbie
Currently Being Moderated
All,

I using the latest SQLDev 3.2.20.09. I am attempting to export DDL from a working db so I can run the script against a new db. Simple enough?

However, the generated DDL is useless without extensive editing.

1. It places the ALTER TABLE command before the CREATE TABLE command, which means trying to alter a table that does not yet exist.

2. It concatenates the ENABLE keyword from the preceding alter command with the CREATE keyword from the succeeding create command, which results in this: ENABLECREATE TABLE...

3. Despite carefully selecting the terminator box, which I thought should cause the script to be generated with terminators, no terminators are produced at all.

Until this morning, I was running an older version if SQLDev, which did not cause problems 2 and 3. I upgraded because I was hoping to get away from problem 1. Now things are worse.

What gives? Am I misunderstanding the purpose of this feature?
  • 1. Re: Problems Exporting Tables to DDL
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    How exactly are you generating the DDL?

    DB Copy under Utilities and View > Cart both other methods for accomplishing what your're asking for.
  • 2. Re: Problems Exporting Tables to DDL
    973518 Newbie
    Currently Being Moderated
    I'm using Tools/Database Export... and follow the dialogs. I also tried using the cart. Same results either way.
  • 3. Re: Problems Exporting Tables to DDL
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    Can you provide a reproducible test case? For example anything in a common schema like HR, SH, SCOTT, etc.
  • 4. Re: Problems Exporting Tables to DDL
    973518 Newbie
    Currently Being Moderated
    Thanks for responding.

    Sorry I cannot. All those schemas have been removed.

    My export file does contain the following. Is this causing the problem?

    -- Unable to render TABLE DDL for object DID.ACTOR_COD with DBMS_METADATA attempting internal generator.
  • 5. Re: Problems Exporting Tables to DDL
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    Do you have the original DDL used to create any of these tables that you can share so we can compare that with what SQL Developer is producing?

    The error message is indicating that we need to fall back on our own DDL engine vs the database's. It could definitely be the issue, but again need a test case.
  • 6. Re: Problems Exporting Tables to DDL
    rp0428 Guru
    Currently Being Moderated
    >
    I using the latest SQLDev 3.2.20.09. I am attempting to export DDL from a working db so I can run the script against a new db.
    >
    You posted this at: 'Posted: Nov 9, 2012 7:18 AM' and then you posted the below at 'Posted: Nov 9, 2012 7:41 AM'
    >
    Sorry I cannot. All those schemas have been removed.
    >
    So you had a working DB but within 23 minutes of your first post all of the schemas from your working DB were removed?

    If that is really true then you either neglected to report your problem to your team or you have a serious communication problem in your organization. Why would your team remove 'all those schemas' if your export of the DDL had failed and that DDL was critical to getting your new db in sync?

    You also said
    Until this morning, I was running an older version if SQLDev, which did not cause problems 2 and 3.
    Good - post that export - it will have the DDL in it that contains the objects you say you had a problem with. We can use that DDL to try to reproduce the problem.
  • 7. Re: Problems Exporting Tables to DDL
    973518 Newbie
    Currently Being Moderated
    Sorry about the confusion.
    The schemas hr, scott, etc have been removed. The production schemas have not.

    I am posting the DDL generated by the two different versions of SQLDev as requested. Both were generated using Tools/Database Export... then following the dialogs.

    Thanks in advance.

    The following is DDL output from the older SQLDev 3.1.07.42

    -- Unable to render TABLE DDL for object EID.ACTIVITY_CDS with DBMS_METADATA attempting internal generator.
    ALTER TABLE EID.ACTIVITY_CDS
    ADD CONSTRAINT ACC_UK UNIQUE
    (
    ACC_DESC
    )
    ENABLECOMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CD IS 'A unique code for an activity type';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_DESC IS 'A unique description of the activity code';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CREATE_BY IS 'The user who created the row';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CREATE_DT IS 'The date the row was created';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_UPDATE_BY IS 'The user who last updated the row';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_UPDATE_DT IS 'The date the row was last updated';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CLOSE_DT IS 'The date the activity code is retired and no longer useable';
    COMMENT ON TABLE EID.ACTIVITY_CDS IS 'Table Activity_Cds describes the handling of contraband used in the context of the I-44.'
    CREATE TABLE EID.ACTIVITY_CDS
    (
    ACC_CD VARCHAR2(4 BYTE) NOT NULL
    , ACC_DESC VARCHAR2(80 BYTE) NOT NULL
    , ACC_CREATE_BY VARCHAR2(30 BYTE) NOT NULL
    , ACC_CREATE_DT DATE NOT NULL
    , ACC_UPDATE_BY VARCHAR2(30 BYTE)
    , ACC_UPDATE_DT DATE
    , ACC_CLOSE_DT DATE
    , CONSTRAINT ACC_PK PRIMARY KEY
    (
    ACC_CD
    )
    ENABLE
    )
    LOGGING
    TABLESPACE "ENF_DATA1"
    PCTFREE 10
    INITRANS 1
    STORAGE
    (
    INITIAL 524288
    NEXT 131072
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    BUFFER_POOL DEFAULT
    )
    /
    GRANT SELECT ON "ACTIVITY_CDS" TO PUBLIC;


    /

    The following is DDL output from the newer SQLDev 3.2.20.09.87

    -- Unable to render TABLE DDL for object EID.ACTIVITY_CDS with DBMS_METADATA attempting internal generator.
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CD IS 'A unique code for an activity type';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_DESC IS 'A unique description of the activity code';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CREATE_BY IS 'The user who created the row';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CREATE_DT IS 'The date the row was created';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_UPDATE_BY IS 'The user who last updated the row';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_UPDATE_DT IS 'The date the row was last updated';
    COMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CLOSE_DT IS 'The date the activity code is retired and no longer useable';COMMENT ON TABLE EID.ACTIVITY_CDS IS 'Table Activity_Cds describes the handling of contraband used in the context of the I-44.'ALTER TABLE EID.ACTIVITY_CDS
    ADD CONSTRAINT ACC_UK UNIQUE
    (
    ACC_DESC
    )
    ENABLECREATE TABLE EID.ACTIVITY_CDS
    (
    ACC_CD VARCHAR2(4 BYTE) NOT NULL
    , ACC_DESC VARCHAR2(80 BYTE) NOT NULL
    , ACC_CREATE_BY VARCHAR2(30 BYTE) NOT NULL
    , ACC_CREATE_DT DATE NOT NULL
    , ACC_UPDATE_BY VARCHAR2(30 BYTE)
    , ACC_UPDATE_DT DATE
    , ACC_CLOSE_DT DATE
    , CONSTRAINT ACC_PK PRIMARY KEY
    (
    ACC_CD
    )
    ENABLE
    )
    LOGGING
    TABLESPACE "ENF_DATA1"
    PCTFREE 10
    INITRANS 1
    STORAGE
    (
    INITIAL 524288
    NEXT 131072
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    PCTINCREASE 0
    BUFFER_POOL DEFAULT
    )
    GRANT SELECT ON "EID"."ACTIVITY_CDS" TO PUBLIC;
  • 8. Re: Problems Exporting Tables to DDL
    rp0428 Guru
    Currently Being Moderated
    >
    The following is DDL output from the older SQLDev 3.1.07.42

    -- Unable to render TABLE DDL for object EID.ACTIVITY_CDS with DBMS_METADATA attempting internal generator.
    ALTER TABLE EID.ACTIVITY_CDS
    ADD CONSTRAINT ACC_UK UNIQUE
    (
    ACC_DESC
    )
    ENABLECOMMENT ON COLUMN EID.ACTIVITY_CDS.ACC_CD IS 'A unique code for an activity type';
    >
    Well that last line starts withk 'ENABLECOMMENT ...' so it looks like the older version DID have the issue even though you said it didn't
    >
    2. It concatenates the ENABLE keyword from the preceding alter command with the CREATE keyword from the succeeding create command, which results in this: ENABLECREATE TABLE...

    3. Despite carefully selecting the terminator box, which I thought should cause the script to be generated with terminators, no terminators are produced at all.

    Until this morning, I was running an older version if SQLDev, which did not cause problems 2 and 3. I upgraded because I was hoping to get away from problem 1. Now things are worse.
    >
    Why did you say the older version did not have problem #2 when it does?
  • 9. Re: Problems Exporting Tables to DDL
    973518 Newbie
    Currently Being Moderated
    I didn't notice the ENABLECOMMENT business. It got past me. Sorry.

    Still, can't it be said that SQLDev has problems?
  • 10. Re: Problems Exporting Tables to DDL
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    It appears borked to me, but we still need the actual DDL for the table, so we can create it locally and then see what happens when we export it via SQL Developer.
  • 11. Re: Problems Exporting Tables to DDL
    973518 Newbie
    Currently Being Moderated
    I take it "borked" means not working.

    When you say the "actual DDL" do you mean that which created the table initially? If so, it is unavailable to me. I do believe the DDL I sent will work if edited into a proper format.
  • 12. Re: Problems Exporting Tables to DDL
    JoyceScapicchio Journeyer
    Currently Being Moderated
    You must have select_catalog_role in order to export or diff objects owned by another schema. You also must have select on the objects in order to find the objects using the Specify Objects panel of the wizard.

    If you do not have select_catalog_role and export objects owned by another schema, the export will attempt to use the internal generator which is no longer supported. DDL generated by the internal generator may not be correct or complete.

    For export, the following comment is included in the generated file if the internal generator is used:
    -- Unable to render TABLE DDL for object xxxx with DBMS_METADATA attempting internal generator.

    Make sure you have been granted the object level priviledges and select_catalog_role.

    Joyce Scapicchio
    SQLDeveloper Team
  • 13. Re: Problems Exporting Tables to DDL
    rp0428 Guru
    Currently Being Moderated
    >
    If you do not have select_catalog_role and export objects owned by another schema, the export will attempt to use the internal generator which is no longer supported. DDL generated by the internal generator may not be correct or complete.
    >
    Since the internal generator is no longer supported is the attempted use of the internal generator considered a bug that will be fixed in an upcoming release? Presumably the fix would be to determine if the select_catalog_role has been granted and abandon the export (or raise an exception) if it hasn't. Seems unlikely you could reliably determine if the internal generator will generate correct output.
  • 14. Re: Problems Exporting Tables to DDL
    JoyceScapicchio Journeyer
    Currently Being Moderated
    Yes, generation by the internal generator is planned to be removed in the next release.

    Joyce Scapicchio
    SQLDeveloper Team

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points