Databases Reference
In-Depth Information
3 dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,
'CONSTRAINTS', false );
4 dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,
'REF_CONSTRAINTS', false );
5 dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM,
'SQLTERMINATOR', TRUE );
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
Using the SET_FILTER Procedure
The
SET_FILTER
procedure helps you restrict the objects to be retrieved by the
DBMS_METADATA
package.
You can specify individual object names or restrict the objects by schema names. The
SET_FILTER
procedure comes in handy in various situations, such as when you're trying to extract the DDL for an
index you've created on an index organized table (IOT). IOTs always include a primary key constraint, so
when you invoke the
GET_DEPENDENT_DDL
procedure, it gets the index creation statements for both the
primary key as well as the index you've created. The following example shows how to invoke the
SET_FILTER
procedure to get just the DDL for the index you've created:
SQL> set serveroutput on
SQL> declare
2 l_myHandle number;
3 l_transHandle number;
4 l_ddl clob;
5 begin
6 l_myHandle := dbms_metadata.open('INDEX');
7 dbms_metadata.set_filter(l_myHandle, 'SYSTEM_GENERATED', FALSE);
8 dbms_metadata.set_filter(l_myHandle, 'BASE_OBJECT_SCHEMA',user);
9 dbms_metadata.set_filter(l_myHandle, 'BASE_OBJECT_NAME', 'IOT_TAB_TST');
10 l_transHandle := dbms_metadata.add_transform(l_myHandle, 'DDL');
11 loop
13 l_ddl := dbms_metadata.fetch_clob(l_myHandle);
14 EXIT WHEN L_DDL IS NULL;
15 dbms_output.put_line( l_ddl);
16 end loop;
17 dbms_metadata.close(l_myHandle);
18* end;
SQL> /
CREATE INDEX "SYS"."IOT_IDX1" ON "SYS"."IOT_TAB_TST" ("B")
PCTFREE 10 INITRANS
2 MAXTRANS 255 COMPUTE STATISTICS
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
