Oracle impdp和remap_data疑义

亲爱的AskTom追随者，

让我们考虑以下DWH场景。我想使用expdp/impdp作为在DWH中填充阶段区域的方法。
阶段区域中的表与业务表具有相同的结构，除了列int_date date，我们在其中捕获日期
数据是有效的。
所以，简单的例子，让我们使用模式hr和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)
复制

不清楚哪里出了问题。

谢谢，

杜桑

您要更改的列隐式是remap函数的参数。

如果要将所有内容设置为昨天，请将其用作返回值，而不是尝试在命令行上传递它:

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