1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Dbms_metadata.get_ddl generating truncated ddls

Discussion in 'SQL PL/SQL' started by @nk!t, Aug 31, 2015.

  1. @nk!t

    @nk!t Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United States
    Hi DBAs,

    Request you to help me out with this .While generating the DDL for a schema, DDL of big tables are getting truncated. After displaying close to 110 rows I am getting ... (ellipsis as text)
    I am using SQL developer for executing the below :

    SELECT DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER)
    FROM DBA_TABLES
    WHERE OWNER = 'XYZ'

    Please advise.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The only expected reason that output from GET_DDL would be truncated is if SQL*Plus does not return the full LONG value:

    http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm

    "The output from DBMS_METADATA.GET_DDL is a LONG datatype. When using SQL*Plus, your output may be truncated by default. Issue the following SQL*Plus command before issuing the DBMS_METADATA.GET_DDL statement to ensure that your output is not truncated:

    SQL> SET LONG 9999
    "

    Your initial post in the earlier thread seems to indicate that you are issuing a "SET LONG 90000". Your post indicates "after 110 rows I am getting...". If you mean 110 rows of DDL for a single table... depending on the line length, I can't rule out the possibility that you are hitting 90000 bytes. That seems difficult to believe, though. My best guess based on the data provided is that the LONG value was either not set or was set to a lower value than 90000. If that's not the case, I would suggest providing a test case with a script that pulls the DDL from one of your problem tables and the resulting spool file.