Re: Datapump question

Hi Ghassan,

I quickly wrote a block to easily test the problem. In version 11.2.0.4 without patch for the bug 14202396 it does not work. In version 12.2 it works fine. (see below)

DECLARE
  STMT VARCHAR2(32767);
  tbl_name VARCHAR2(30) := UPPER('just_delete_me_if_u_see_me$$$$');   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

                INTO STMT
                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

• 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; Am Do., 24. Jan. 2019 um 15:54 Uhr schrieb Ahmed <gherrami_at_gmail.com>:

> Hi Ghassan,

>

>

>

>

>
>> Ahmed,
>> This should work if you have patch for bug 14202396 (fixed in 12.2, and
>> there are backports to older versions).
>> And your code does not work as it is: you close a cursor that does not
>> exists in the code, and you have to have the function defined in a package,
>> not as a standalone one.
>>
>> rgds
>>
>> On Thu, Jan 24, 2019 at 10:26 AM Ahmed <gherrami_at_gmail.com> wrote:
>>
>>> 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 when
>>> anonymizing
>>> more than ten columns in a table, that does not work at all, and without
>>> any reasonable error message. This is from my point of view not fair from
>>> Oracle side.
>>> Now I have a workaround built in, which cost me two days. But thanks for
>>> the hint of Adric.
>>>
>>> Am Mi., 23. Jan. 2019 um 20:10 Uhr schrieb Mark W. Farnham <mwf_at_rsiz.com
>>> >:
>>>
>>>> cool. Learning something new today. I didn’t even know you could
>>>> obscure column content with exp/imp.
>>>>
>>>>
>>>>
>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Mladen Gogala
>>>> *Sent:* Wednesday, January 23, 2019 9:43 AM
>>>> *To:* Adric Norris; Ahmed
>>>> *Cc:* oracle-l_at_freelists.org
>>>> *Subject:* Re: Datapump question
>>>>
>>>>
>>>>
>>>> With the advent of cloud and DBaaS, data pump becomes awkward. However,
>>>> even Oracle 18c maintains the good, old exp/imp executables which can be
>>>> used instead. Ttue, data pump versions are much faster, but there is no
>>>> need to copy files locally and there are no limits with the number of
>>>> columns.
>>>>
>>>>
>>>>
>>>> Mladen Gogala
>>>> Database Consultant
>>>> Tel: (347) 321-1217
>>>>
>>>> On 1/22/2019 3:03:23 PM, Adric Norris <landstander668_at_gmail.com> wrote:
>>>>
>>>> I'm not sure what version you're on, but 11.2.0.4 had a limit of
>>>> something like 8-10 columns (going from memory, so the exact number is a
>>>> bit fuzzy). 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.
>>>>
>>>>
>>>>
>>>> On Tue, Jan 22, 2019 at 11:47 AM Ahmed <gherrami_at_gmail.com> wrote:
>>>>
>>>> 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