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>
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;
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
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
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
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.