5 Replies Latest reply: Oct 11, 2013 6:43 AM by Srini Chavali-Oracle RSS

    11.2.0.3, DBMS_DATAPUMP import ower db link, DATA_REMAP does not work

    user12011608

      This is related to impdp: should network_link and remap_data work together? that I accidentally marked as answered.

       

      Documentation says, I can supply NULL as value of schema for DATA_REMAP: See   DBMS_DATAPUMP

       

      That does not work, it gives an error:

      ERROR at line 1:

      ORA-39001: invalid argument value

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

      ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507

      ORA-06512: at "SYS.DBMS_DATAPUMP", line 4309

      ORA-06512: at line 11

       

      If I give SOURCE_SCHEMA as schema, DATA_REMAP does nothing.

      TARGET_SCHEMA is of course not accepted as TABLE_TO_LOAD does not yet exist.

       

      The code I am using is something like this:

      set serveroutput on;

      declare

        handlename number;

      begin

          handlename := DBMS_DATAPUMP.open('IMPORT','TABLE', 'SOURCE_DB_LINK','NAME_OF_JOB');

          DBMS_DATAPUMP.METADATA_FILTER(handle => handlename, name => 'SCHEMA_LIST', value => '''SOURCE_SCHEMA''');

          DBMS_DATAPUMP.METADATA_FILTER(handle => handlename, name => 'NAME_EXPR', value => 'IN (''TABLE_TO_LOAD'')');

         

          DBMS_DATAPUMP.METADATA_REMAP(handlename,'REMAP_SCHEMA','SOURCE_SCHEMA','TARGET_SCHEMA');

          DBMS_DATAPUMP.ADD_FILE(handlename, 'name_of_log','DUMPDIR',NULL,DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,1);

          DBMS_DATAPUMP.DATA_REMAP(handlename, 'COLUMN_NAME', 'TABLE_TO_LOAD', 'COLUMN_TO_REMAP',  'TARGET_SCHEMA.REMAPKG.REMAPFN',  NULL);

          DBMS_DATAPUMP.START_JOB(handlename);

      end;

      /

       

      Any ideas?