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
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;
Am Do., 24. Jan. 2019 um 15:54 Uhr schrieb Ahmed <gherrami@xxxxxxxxx>:
Hi Ghassan,
I just copied the example from my package on the fly and apparently left
lines in it. The example was only intended to explain the problem here. For
my customer, I have built a workaround and am satisfied with it first.
However, I will check on occasion if the problem is fixed in 12.2. Which I
hope, since I am a huge fan of "dbms_datapump" (and Oracle in general) and
have so far only very good experiences with it.
Thanks for your Hint :-)
Am Do., 24. Jan. 2019 um 15:31 Uhr schrieb Ghassan Salem <
salem.ghassan@xxxxxxxxx>:
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@xxxxxxxxx> 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@xxxxxxxx
:
cool. Learning something new today. I didn’t even know you could
obscure column content with exp/imp.
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Mladen Gogala
*Sent:* Wednesday, January 23, 2019 9:43 AM
*To:* Adric Norris; Ahmed
*Cc:* oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxx> 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@xxxxxxxxx> 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
