Re: Datapump question

From: Ahmed <gherrami_at_gmail.com>
Date: Tue, 22 Jan 2019 20:43:47 +0100
Message-ID: <CANkb5P3+mr4qZqOc9pcUmeqeXc42QVO4FLCNeiK3CP4aKCnabA_at_mail.gmail.com>

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_at_bobak.net>:

> Hi Ahmed,
>
> This is not my area of expertise, but did you try DBMS_DATAPUMP.GET_STATUS.
> as suggested here:
> [oracle_at_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_at_gmail.com> 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
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 22 2019 - 20:43:47 CET