Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dusan.

Asked: March 17, 2021 - 3:39 pm UTC

Last updated: March 22, 2021 - 6:15 pm UTC

Version: 19c

Viewed 100+ times

You Asked

Dear AskTom followers,

Let's consider following DWH scenario. I want to use expdp/impdp as methods for populating stage area in DWH.
Tables in stage area have the same structure as business tables, except column int_date date, where we catch the date when
the data is valid.
So, simple example, let's use schemas hr and hr_stage.

conn hr_stage

hr_stage@PDB1> create table int_employees
  2  as select * from hr.employees
  3  where 1=0;

Table created.

hr_stage@PDB1>
hr_stage@PDB1> alter table int_employees add (int_date  date   );

Table altered.

hr_stage@PDB1>
hr_stage@PDB1> create or replace
  2   package pkg_date
  3  as
  4     function f_int_date (p_int_date in date) return date;
  5  end;
  6  /

Package created.

hr_stage@PDB1>
hr_stage@PDB1>
hr_stage@PDB1> create or replace
  2   package body pkg_date as
  3    function f_int_date (p_int_date in date) return date  as
  4     begin
  5      return p_int_date;
  6     end;
  7  end;
  8  /

Package body created.

-- now, conn as hr

hr@PDB1> host expdp hr/hr@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp TABLES=employees CONTENT=DATA_ONLY

Export: Release 19.0.0.0.0 - Production on Wed Mar 17 15:58:53 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  hr/********@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp TABLES=employees CONTENT=
DATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /home/oracle/imp_cli.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Wed Mar 17 15:59:02 2021 elapsed 0 00:00:08

-- So table hr.empoyees is exported. Now, let's connect as hr_stage and try to import table employees into the table hr_stage.int_employees
-- We want to populate  as sysdate-1, using hr.pkg_date.f_int_date(sysdate-1)
-- As the documentation says, it is possible using REMAP_DATA parameter using pkg.function 

conn hr_stage

hr_stage@PDB1> host impdp hr/hr@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp remap_schema=hr:hr_stage REMAP_TABLE=hr.employees:hr_stage.int_employees CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND remap_data=hr_stage.int_employees.int_date:hr_stage.pkg_date.f_int_date(sysdate-1)

Import: Release 19.0.0.0.0 - Production on Wed Mar 17 16:01:53 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39232: invalid remap function: HR.PKG_DATE.F_INT_DATE(SYSDATE-1)


Not clear what is wrong.

Thanks,

Dusan

and we said...

The column you're changing is implicitly the parameter to the remap function.

If you want to set everything to yesterday, use this as the return value instead of trying to pass it on the command line:

  function f_int_date (p_int_date in date) return date  as
  begin
    return sysdate - 1;
  end;

Rating

  (5 ratings)

Comments

Still does not work

Dusan, March 18, 2021 - 9:07 am UTC

Hi there,

Command impdp run successfully, but the desired column was not populated.

hr_stage@PDB1> create or replace
2 package body pkg_date as
3 function f_int_date (p_int_date in date) return date as
4 begin
5 return sysdate - 1;
6 end;
7 end;
8 /

Package body created.

hr_stage@PDB1>
hr_stage@PDB1> host impdp hr/hr@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp remap_schema=hr:hr_stage REMAP_TABLE=employ
ees:int_employees CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND remap_data=hr_stage.int_employees.int_date:hr_stage.pkg_d
ate.f_int_date

Import: Release 19.0.0.0.0 - Production on Thu Mar 18 09:59:46 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@pdb1 directory=DIR_HOME dumpfile=imp_cli.dmp remap_schema=hr:hr_stage R
EMAP_TABLE=employees:int_employees CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND remap_data=hr_stage.int_employees.int_da
te:hr_stage.pkg_date.f_int_date
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR_STAGE"."INT_EMPLOYEES" 17.08 KB 107 rows
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Thu Mar 18 09:59:48 2021 elapsed 0 00:00:02


hr_stage@PDB1> select count(*) from int_employees where int_date is not null;

COUNT(*)
---------
0

hr_stage@PDB1>

Chris Saxon
March 18, 2021 - 1:39 pm UTC

Sorry, I'm not able to reproduce. Check all your permissions etc. simplify the example (fewer remaps) to see if you can isolate where the issue is.

If it's still not working take this up with support.

Simplified version

Dusan, March 18, 2021 - 3:43 pm UTC

I simplified example, expdp/impdp in the same schema, easy to reproduce. The same result.

drop table t ;

drop table int_t;


create table t
(id number primary key not null);

create table int_t
(id number not null
,int_date date);



insert into t
select level from dual connect by level<=50;
commit;



create or replace
package pkg_date
as
function f_int_date(p_date IN date) return date;
end;
/


create or replace
package body pkg_date as
function f_int_date(p_date IN date) return date as
begin
return sysdate-1;
end;
end;
/

host expdp valasekd/a@pdb1 directory=DIR_HOME dumpfile=dump1.dmp TABLES=t CONTENT=DATA_ONLY

host impdp valasekd/a@pdb1 directory=DIR_HOME dumpfile=dump1.dmp REMAP_TABLE=t:int_t REMAP_DATA=int_t.int_date:pkg_date.f_int_date

select * from int_t where int_date is not null;



Chris Saxon
March 18, 2021 - 5:31 pm UTC

You're mapping from the new table to itself! You need to map from a column in the source table.

* Add int_date to the original table
* Export it
* Import with REMAP_DATA=t.int_date:pkg_date.f_int_date

Dusan Valasek, March 19, 2021 - 8:33 am UTC

The point is that the source table t is considered untouchable. Imagine the real situation - I cannot go to a client and say hey, add some extra columns to your business tables because I need it for my DWH.
I want to perform the operation exactly on the target table. I do not see any reason why this cannot be done. Moreover, there is no error message, nor warning in the log.

regards,

Dusan
Chris Saxon
March 19, 2021 - 8:48 am UTC

Think about what you're asking to do: remap something that doesn't exist! How's that supposed to work?!

If you want to add a default value for the rows you're importing, why not:

alter table int_t 
  modify int_date 
  default sysdate 

Dusan Valasek, March 19, 2021 - 9:00 am UTC

Default is not sufficient enough, they may send me an export saying this was valid 3 days ago.
Ok, it does not make sense to investigate this anymore. Simply - wrong way!

Thanks,

Dusan
Chris Saxon
March 22, 2021 - 9:02 am UTC

Sooo... change the default just before the import!

Dusan Valasek, March 22, 2021 - 9:35 am UTC

In the meantime, I was also thinking about changing the default value before import. It seemed to me a little bit crazy solution, but - yes, why not.

thanks,
Dusan
Chris Saxon
March 22, 2021 - 6:15 pm UTC

As long as you're the only user writing to the table it looks like the best solution to me.