1 Reply Latest reply: Aug 23, 2013 10:40 AM by 283a4243-0791-426b-82a1-0534614755f1 RSS

    To increase the XMLtype column data length

    283a4243-0791-426b-82a1-0534614755f1

      Hi ,

       

      I have created a Table with an XMLtype column , when I am checking the data length it is showing only 2000.

       

      How can I increase the length.

       

      DBMS_METADATA.GET_DDL('TABLE','CC_EVENT_MESSAGES','UNS_STAGING_DEV5')

      --------------------------------------------------------------------------------

        CREATE TABLE "UNS_STAGING_DEV5"."CC_EVENT_MESSAGES"

         (    "SRC_SYSTEM_CD" VARCHAR2(10) NOT NULL ENABLE,

              "SRC_SEQUENCE_NUM" NUMBER NOT NULL ENABLE,

              "SRC_EVENT_NAME" VARCHAR2(255) NOT NULL ENABLE,

              "SRC_EVENT_XSD_NAME" VARCHAR2(255) NOT NULL ENABLE,

              "SRC_EVENT_DATE" DATE NOT NULL ENABLE,

              "INSERT_TS" TIMESTAMP (6) NOT NULL ENABLE,

              "PROCESSED_TS" TIMESTAMP (6),

              "STAGING_NUM" NUMBER NOT NULL ENABLE,

              "MESSAGE_STATE" VARCHAR2(10) NOT NULL ENABLE,

              "MESSAGE_FRAME" "SYS"."XMLTYPE"  NOT NULL ENABLE

         ) SEGMENT CREATION IMMEDIATE

        PCTFREE 0 PCTUSED 0 INITRANS 1 MAXTRANS 255

      NOCOMPRESS LOGGING

        STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "UNS_STAGING_DEV5"

      XMLTYPE COLUMN "MESSAGE_FRAME" STORE AS BASICFILE CLOB (

        TABLESPACE "UNS_STAGING_DEV5" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION

        CACHE

        STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

         CACHE

       

       

      SQL> select OWNER,TABLE_NAME,column_name,data_type,data_length from dba_tab_columns where DATA_TYPE like '%XMLTYPE' and owner not in ('SYS','SYSTEM','XDB');

       

       

      OWNER                TABLE_NAME           COLUMN_NAME     DATA_TYPE  DATA_LENGTH

      --------------------         --------------------            ---------------                ----------               -----------

      UNS_STAGING_DEV5     CC_EVENT_MESSAGES    MESSAGE_FRAME   XMLTYPE           2000