Skip to Main Content
  • Questions
  • DBMS_METADATA.GET_DDL - Output is getting Truncated

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 04, 2017 - 1:09 pm UTC

Answered by: Chris Saxon - Last updated: September 14, 2020 - 1:58 am UTC

Category: SQL Developer - Version: 11.2

Viewed 1000+ times

You Asked

Hi Team,

I am trying to get table & its dependent ddl, but it its not giving completed output -

SQL> set linesize 1000
SQL> set pages 1000
SQL> set pages 1000
SQL> set lines 1000
SQL> select DBMS_METADATA.GET_DDL('TABLE','TB_TEST') from dual;

DBMS_METADATA.GET_DDL('TABLE','TB_TEST')
--------------------------------------------------------------------------------

  CREATE TABLE "FOO"."TB_TEST"
   (    "CARD_NO" VARCH


SQL> 



I tried spooling the output but same truncated output I am getting in log file as well.
Can you please help.

and we said...

You need to increase the value of the long system variable. This controls the maximum width displayed for CLOBs, BLOBs and other "large" data types

SQL> create table t ( x int, y date, z varchar2(100) );

Table created.

SQL> sho long
long 80
SQL> select DBMS_METADATA.GET_DDL('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "CHRIS"."T"
   (    "X" NUMBER(*,0),
        "Y" DATE,
        "Z" VARCHAR2(10


SQL> set long 100000
SQL> select DBMS_METADATA.GET_DDL('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "CHRIS"."T"
   (    "X" NUMBER(*,0),
        "Y" DATE,
        "Z" VARCHAR2(100)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


http://docs.oracle.com/database/122/SQPUG/SET-system-variable-summary.htm#SQPUG089

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Parameter of GET_DDL

September 11, 2020 - 10:34 am UTC

Reviewer: A reader

How can i see value of "object type" parameter of "GET_DDL" ?
for example object type may be table for table , type for type but what for the database link?

I getting ora-31600 error.
Connor McDonald

Followup  

September 12, 2020 - 3:56 am UTC

Have you checked the docs ?

Hint.... there's a table listing all the valid object types in the docs for DBMS_METADATA

September 12, 2020 - 8:09 am UTC

Reviewer: A reader

I check the document following link.
https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS026

but not find any more parameter accepted by get_ddl argument
Connor McDonald

Followup  

September 14, 2020 - 1:58 am UTC

Its right there in the 19c docs

DBMS_METADATA_OBJTYPE

and I checked - same in 18c docs, same in 12.2 docs, same in 11.2 docs.

I mean...you gotta meet us half way here.

List object_types

September 12, 2020 - 10:32 am UTC

Reviewer: David from France

Hello,

This is an OLD list from oracle 9.2 but, I think, still valid.
https://docs.oracle.com/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031458


Thanks

September 14, 2020 - 11:36 am UTC

Reviewer: A reader

thank you finally got answers of my question.
before post question, i find "object type" of dbms_metadata.get_ddl
instead of "dbms_metadata"

More to Explore

DBMS_METADATA

More on PL/SQL routine DBMS_METADATA here