Hi Mark,
the Job status is 'DEFINING'. I get the error at defining the job and
before starting it.
I've noticed that the problem occurs when the number of columns that are
anonymized are greater than 10 (> 10). I guess that Oracle has some
limitation (Or Bug) here.
To demonstrate the problem:
-- table with 11 columns
CREATE TABLE MY_TABLE (col_1 VARCHAR2(100), col_2 VARCHAR2(100), ....,
col_20 VARCHAR2(100)); -- 20 columns
-- fct
CREATE FUNCTION fu_anonymize(p_in VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'anonyme';
END ;
-- this block work fine if I try to anonymize few than 10 column. V_COL_NR
= 10
-- by V_COL_NR > 10 i get the ora 39001
DECLARE
H1 NUMBER;
V_JOB_STATE USER_DATAPUMP_JOBS.STATE%TYPE;
V_COL_NR PLS_INTEGER := 11; --=======> v_col_nr = 10 it works fine
BEGIN
H1 := DBMS_DATAPUMP.OPEN(OPERATION => 'EXPORT',
JOB_MODE => 'TABLE',
JOB_NAME => 'job_test');
DBMS_DATAPUMP.ADD_FILE(HANDLE => H1,
FILENAME => 'file.dmp',
DIRECTORY => 'DATA_PUMP_DIR',
REUSEFILE => 1);
DBMS_DATAPUMP.METADATA_FILTER(H1, 'NAME_EXPR', ' IN (''MY_TABLE'')'); --
table to export
DBMS_DATAPUMP.METADATA_FILTER(H1, 'SCHEMA_EXPR', 'IN (''MY_SCHEMA'')');
-- schema
FOR I IN 1 .. V_COL_NR LOOP
-- if v_col_nr > 10 . I get error at i =11
DBMS_DATAPUMP.DATA_REMAP(HANDLE => H1,
NAME => 'COLUMN_FUNCTION',
TABLE_NAME => 'MY_TABLE',
COLUMN => 'COL_' || I,
FUNCTION => FU_ANONYMIZE,
SCHEMA => 'MY_SCHEMA');
END LOOP;
CLOSE REF_CUR;
DBMS_DATAPUMP.START_JOB(H1);
DBMS_DATAPUMP.WAIT_FOR_JOB(H1, V_JOB_STATE);
DBMS_OUTPUT.PUT_LINE('export anonymized data for ' || G_CDD_SCHEMA ||
' done! with Job State ''' || V_JOB_STATE || '''.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END;
Ahmed Fikri
Am Di., 22. Jan. 2019 um 19:45 Uhr schrieb Mark J. Bobak <mark@xxxxxxxxx>:
Hi Ahmed,
This is not my area of expertise, but did you try DBMS_DATAPUMP.GET_STATUS.
as suggested here:
[oracle@aftersalesdata-dvl ~]$ oerr ora 39001
39001, 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.
?
On Tue, Jan 22, 2019 at 12:47 PM Ahmed <gherrami@xxxxxxxxx> wrote:
Hello,
I try to export a table using DBMS_DATAPUMP. The table has 20 columns and
I try to anonymise 11 columns using functions from package. Like this way:
dbms_datapump.data_remap(handle => h1, name => 'COLUMN_FUNCTION',
table_name => 'TEST_TABLE', column => 'COL1', function =>
'MYPKG.FU1', schema => USER);
but it seems that work only for 10 columns.
If I try to anonymize more than 10 columns I get ORA-39001 invalid input
argument at datapump job definition.
Is this a bug or maybe a restriction? Any Idea?
Best Regards
Ahmed Fikri
Senior Developer
Stuttgart, Germany
