DDL Creation issue

From: Lok P <loknath.73_at_gmail.com>
Date: Wed, 15 Sep 2021 13:40:13 +0530
Message-ID: <CAKna9VY+xyyTUKQzUd3L9AobnsuTtCA=JaW6iO1JvEC7iokn6Q_at_mail.gmail.com>

Hello Listers, saw a question in the dev forum and we had encountered similar issues in the past for one of our third party applications. Want to know what experts thought here on if any change to dbms_metadata functionality in 19C caused this appending of the unwanted characters towards the end of the DDL script? and is there a way to get rid of those ?

Below is the example block which is generating some odd characters '*nul; null; 10 10;*' towards the end of the DDL statement in 19C.

drop table test1;
create table test1(c1 number(10));
create index idx12 on test1(c1);

declare

   l_md_handle NUMBER;
   l_tr_handle NUMBER;
   l_loop_cnt NUMBER := 0;
   l_next_ddl_clob CLOB;
   l_all_ddl_clob CLOB;

   In_table_name varchar2(4000):='TEST1';  BEGIN
 dbms_stats.delete_table_stats(ownname => NULL,

                                 tabname => In_table_name,
                                 force => TRUE);

   dbms_lob.createtemporary(l_all_ddl_clob, true);

   l_md_handle := DBMS_METADATA.OPEN('TABLE_EXPORT');      dbms_output.put_line('l_md_handle '||to_char(l_md_handle));

   DBMS_METADATA.SET_FILTER(l_md_handle,'NAME', In_table_name);

   l_tr_handle := DBMS_METADATA.ADD_TRANSFORM(l_md_handle, 'DDL');    dbms_output.put_line('l_tr_handle '||to_char(l_tr_handle));

   LOOP
     l_loop_cnt := l_loop_cnt + 1;

     l_next_ddl_clob := DBMS_METADATA.FETCH_CLOB(l_md_handle);
     dbms_output.put_line('l_next_ddl_clob '||to_char(l_next_ddl_clob));
     IF (l_loop_cnt = 1)
     THEN
       l_all_ddl_clob := l_next_ddl_clob;
     ELSE
       l_all_ddl_clob := l_all_ddl_clob || ';'|| CHR(10) || l_next_ddl_clob;
     END IF;

    EXIT WHEN l_next_ddl_clob IS NULL;
   --
   END LOOP;
   dbms_output.put_line('l_all_ddl_clob final '||to_char(l_all_ddl_clob));

   DBMS_METADATA.CLOSE(l_md_handle);

 END;
/

• OUTPUT *********************

l_md_handle 200001
l_tr_handle 200000001
l_next_ddl_clob

 CREATE TABLE "user1"."TEST1"
  ( "C1" NUMBER(10,0)
) SEGMENT CREATION IMMEDIATE

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"
l_next_ddl_clob

l_next_ddl_clob
 CREATE INDEX "user1"."IDX12" ON "user1"."TEST1" ("C1")  PCTFREE 10 INITRANS 2 MAXTRANS 255
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS"
l_next_ddl_clob
 null
l_next_ddl_clob
 null
l_next_ddl_clob
 10 10
l_next_ddl_clob
l_all_ddl_clob final
 CREATE TABLE "user1"."TEST1"
  ( "C1" NUMBER(10,0)
) SEGMENT CREATION IMMEDIATE

 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS" ;  ;

 CREATE INDEX "user1"."IDX12" ON "user1"."TEST1" ("C1")  PCTFREE 10 INITRANS 2 MAXTRANS 255
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "USERS" ;  null;

 null;

 10 10;

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 15 2021 - 10:10:13 CEST