Oracle Datapump Issues

My Experience with Oracle Bug 14202396 in Oracle 11.2.0.4: Datapump remap_data fails if used for more than 10 cols in a table.

I was assigned to write a tool to anonymize an entire database. This should be exported and imported anonymously in another database. The first idea that came to mind was to use „dbms_datapump“. I have good experiences with it and I know that this utility is very flexible and fast. It also offers the possibility to obscure columns on the fly using dbms_datapump.data_remap function.

Before I opted for this solution, I made a proof of concept, then the whole thing was implemented and it was revealed during testing that in some cases, that it does not work at all, and without any reasonable error message! I get the error in some cases at defining the job and before starting it.

ORA-39001: invalid argument value
ORA-06512: in „SYS.DBMS_SYS_ERROR“, line 79
ORA-06512: in „SYS.DBMS_DATAPUMP“, line 3507
ORA-06512: in „SYS.DBMS_DATAPUMP“, line 4309
ORA-06512: in line 66
00000 – „invalid argument value“
*Cause: The user specified API parameters were of the wrong type or
value range. Subsequent messages supplied by
DBMS_DATAPUMP.GET_STATUS will further describe the error.
*Action: Correct the bad argument and retry the API.

There was no error or hint in the Oracle Alert file. To narrow down the problem, I have written the following test block:

DECLARE
  STMT VARCHAR2(32767);
  tbl_name VARCHAR2(30) := UPPER('TEST_TABLE$$$$');
  fu_name varchar2(100) := 'PKG_OBFUS_DATA.FU_1';
  v_job_state user_datapump_jobs.state%TYPE;
  v_exp_job_name varchar2(30) := 'exp_dmp_i_m_just_tst';
  h1 NUMBER;
  g_dbdir VARCHAR2(30) := 'DATA_PUMP_DIR';
  NUMBER_OF_COL_TO_TEST BINARY_INTEGER := 11; --  <= 10 works , > 10 error at job definition (11.2.0.4). But in 12.2 it works fine
  v_col_name VARCHAR2(30) := 'COL_1';
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'drop table '||tbl_name||' purge';
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('table does''t exist....');
  END;
  --generate table code with 20 colums
  FOR REC IN (SELECT A
                FROM (SELECT 'create table '||tbl_name||'   (' A
                        FROM DUAL
                      UNION ALL
                      SELECT DECODE(ROWNUM, 1, NULL, ',') || 'col_' || ROWNUM ||
                             ' varchar2(100)'
                        FROM DUAL
                      CONNECT BY LEVEL < 21
                      UNION ALL
                      SELECT ') nologging compress'
                        FROM DUAL)) LOOP
    STMT := STMT || REC.A || CHR(10);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(STMT);
  -- create the table
  EXECUTE IMMEDIATE STMT;
  --generate insert stmt
  select 'insert into '||tbl_name||' values ('||listagg(''''||column_name||'''' , ',') within group(order by column_id) || ')' 
  INTO stmt 
  FROM user_tab_cols where table_name = tbl_name ;
  --insert just one row ;
  EXECUTE IMMEDIATE stmt;
  dbms_output.put_line(SQL%ROWCOUNT || ' row inserted in '||tbl_name||'' ) ;
  COMMIT;
  -- create obfus function (it works only for functions in pkg)
  --head
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PACKAGE PKG_OBFUS_DATA IS FUNCTION FU_1(p_in VARCHAR2) RETURN VARCHAR2; END;';
  --body
  EXECUTE IMMEDIATE 'CREATE OR REPLACE PACKAGE BODY PKG_OBFUS_DATA IS FUNCTION FU_1(p_in VARCHAR2) RETURN VARCHAR2 IS
                      BEGIN RETURN ''test''; END;  END;';
  
  -- test datapump
  h1 := dbms_datapump.open(operation => 'EXPORT',job_mode => 'TABLE',job_name => v_exp_job_name);
  dbms_datapump.add_file(handle => h1, filename => 'delete_me_if_u_see_me.dmp' ,directory => g_dbdir, reusefile  => 1);
  dbms_datapump.metadata_filter(h1,'NAME_EXPR', ' IN ('''||tbl_name||''')');
  DBMS_DATAPUMP.metadata_filter(h1,'SCHEMA_EXPR',  'IN ('''||USER||''')');
  dbms_output.put_line('ok');
  --dbms_datapump.data_remap(handle => h1, name => 'COLUMN_FUNCTION', 
  --  table_name => tbl_name, column => v_col_name, function => fu_name, schema => 'SMARAGD_DBA');
  FOR i IN 1..NUMBER_OF_COL_TO_TEST LOOP
    dbms_output.put_line(i);
    dbms_datapump.data_remap(handle => h1, name => 'COLUMN_FUNCTION', 
    table_name => tbl_name, column => 'COL_'||i, function => fu_name, schema => 'SMARAGD_DBA');

  END LOOP;
  --definition is ok
  dbms_datapump.start_job(h1);
  dbms_datapump.wait_for_job(h1, v_job_state);
  --cleanup
  EXECUTE IMMEDIATE 'drop table '||tbl_name||' purge';
  EXECUTE IMMEDIATE 'drop package PKG_OBFUS_DATA';
END;

The test block simply creates a table with 20 columns, a test function for anonymization. And I made runs. I was surprised: the program works as long as the number of anonymised columns is less than 10! I could not believe it, Oracle developers forgot something here or is that just a restriction?!
In the doc I found no  things about this limitation and the error message does not say much. Then I posted the question in a forum, one responded quickly and told me that he had the same problem. He said: We opened a SR for it at the time, and Oracle’s response was that you need to purchase Oracle Data Redaction if the built-in Datapump limit is insufficient. I was pretty disappointed. I did not want to lose much time on this topic then
I have a workaround built in, which cost me two days.

Later I found out that Oracle does not have any restrictions here, but it was just a bug. After installing the OPatch everything worked fine as expected.