You Asked
Hi,
I am trying to extract the DDL code for an index. Please have a look at the below code. The same returns
BEGIN
NULL;
END;
as the output. Please can you confirm if there is something wrong with the code?
drop table a;
create table a ( a number, b timestamp, c varchar2(10));
alter table a add constraint uk_1 unique (a,b) using index (create unique index uk_1 ON a(a,b));
create table b (b clob);
select * from user_indexes where index_name = 'UK_1'
select * from user_constraints where constraint_name = 'PK_1'
select dbms_metadata.get_ddl('INDEX', 'UK_1') from dual;
DECLARE
v_handle NUMBER;
v_transform_handle NUMBER;
v_table_query CLOB;
BEGIN
v_handle := DBMS_METADATA.OPEN ('INDEX');
DBMS_METADATA.SET_FILTER (v_handle, 'NAME'
, 'UK_1');
DBMS_METADATA.SET_FILTER (v_handle, 'SCHEMA', USER);
v_transform_handle := DBMS_METADATA.ADD_TRANSFORM (v_handle, 'DDL');
DBMS_METADATA.SET_TRANSFORM_PARAM (v_transform_handle
, 'EXPORT'
, TRUE);
v_table_query := DBMS_METADATA.FETCH_DDL (v_handle);
INSERT INTO b VALUES (v_table_query);
COMMIT;
DBMS_METADATA.CLOSE (v_handle);
END;
/
select * from b
~Thanks & Regards
and Chris said...
I can see a couple of issues:
- EXPORT is an invalid parameter for DDL transformations. You can find the valid values at:
http://docs.oracle.com/database/121/ARPLS/d_metada.htm#BGBJBFGE - dbms_metadata.fetch_ddl returns a sys object. But you're assigning it to a clob. So you should use dbms_metadata.fetch_clob
declare
v_handle number;
v_transform_handle number;
v_table_query clob;
begin
v_handle := dbms_metadata.open ( 'INDEX' ) ;
dbms_metadata.set_filter ( v_handle, 'NAME' , 'UK_1' ) ;
dbms_metadata.set_filter ( v_handle, 'SCHEMA', user ) ;
v_transform_handle := dbms_metadata.add_transform ( v_handle, 'DDL' ) ;
v_table_query := dbms_metadata.fetch_clob ( v_handle ) ;
insert into b values
( v_table_query
) ;
dbms_metadata.close ( v_handle ) ;
end;
/
select * from b;
B
CREATE UNIQUE INDEX "CHRIS"."UK_1" ON "CHRIS"."A" ("A", "B")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"
Though I have to ask: why are you bothering with all this? As it stands it returns the same as calling dbms_metadata.get_ddl in a select:
select dbms_metadata.get_ddl ( 'INDEX', 'UK_1' ) ddl from dual;
DDL
CREATE UNIQUE INDEX "CHRIS"."UK_1" ON "CHRIS"."A" ("A", "B")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment