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;
