Tuesday, April 23, 2013

Sample code: Using Datapump API for metadata and data filtering

I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn't find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.

Structure for reload_dev_tables:

 Name                                      Null?    Type                        
 ----------------------------------------- -------- -------------
 OWNER                                     NOT NULL VARCHAR2(40)                
 TABLE_NAME                                NOT NULL VARCHAR2(40)                
 IS_FULL                                   NOT NULL NUMBER(1)                   
 FILTER_PREDICATE                                   VARCHAR2(250)   

Here is the datapump code itself, tested in 11.2.0.3. This is just a demonstration how to use the datapump API, specifically the metadata and data filters.

  PROCEDURE export_data(p_directory IN VARCHAR2) IS
    CURSOR c_norows IS
      select owner, table_name from dba_tables WHERE owner in (
        select distinct owner from reload_dev_tables) and status='VALID' and temporary = 'N' 
        and secondary = 'N' and nested = 'NO' and dropped = 'NO' and iot_name is null
      minus
      select owner, table_name from reload_dev_tables;
    l_dp_handle       NUMBER;
    l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
    l_job_state       VARCHAR2(30) := 'UNDEFINED';
    l_sts             KU$_STATUS;
    s varchar2(3000);
  BEGIN
    l_dp_handle := DBMS_DATAPUMP.open(
      operation   => 'EXPORT',
      job_mode    => 'SCHEMA',
      );

    DBMS_DATAPUMP.add_file(
      handle    => l_dp_handle,
      filename  => 'dev_dw.dmp',
      directory => p_directory);

    DBMS_DATAPUMP.add_file(
      handle    => l_dp_handle,
      filename  => 'dev_dw.log',
      directory => p_directory,
      filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

    --
    SELECT listagg(''''||owner||'''', ', ') WITHIN GROUP (ORDER BY owner) INTO s
    FROM (SELECT DISTINCT owner FROM dba_tables WHERE owner IN (SELECT distinct owner from reload_dev_tables));
    DBMS_DATAPUMP.metadata_filter(l_dp_handle, 'SCHEMA_LIST', s);
     
    -- Add query filters
    FOR rec IN (SELECT owner, table_name, filter_predicate FROM reload_dev_tables r 
        WHERE filter_predicate IS NOT NULL AND 
        EXISTS (SELECT 1 FROM dba_tables t WHERE t.owner = r.owner AND t.table_name = r.table_name 
          AND t.dropped = 'NO') ) LOOP
      DBMS_DATAPUMP.DATA_FILTER (
        handle => l_dp_handle,
        name  => 'SUBQUERY',
        value => 'WHERE '||rec.filter_predicate,
        table_name => rec.table_name,
        schema_name => rec.owner);
    END LOOP;
    -- Add tables without rows
    FOR rec IN c_norows LOOP
      DBMS_DATAPUMP.DATA_FILTER (
        handle => l_dp_handle,
        name  => 'INCLUDE_ROWS',
        value => 0,
        table_name => rec.table_name,
        schema_name => rec.owner);
    END LOOP;
   
    DBMS_DATAPUMP.start_job(l_dp_handle);

    DBMS_DATAPUMP.detach(l_dp_handle);
  END;

No comments:

Post a Comment