Oracle Datapump API

Question

I am rewriting an application, that imports and exports oracle database. I was using imp.exe and exp.exe executed as Processes from code. Now I'm trying to use impdp and expdp instead of them. I studied some samples at https://docs.oracle.com/, however the following code fails at DBMS_DATAPUMP.ADD_FILE.

DECLARE
  h2 NUMBER;
BEGIN    
  h2 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL, 'MYJOB', 'LATEST');
  DBMS_DATAPUMP.ADD_FILE(h2,'test1.dmp','datapump_dir');
  DBMS_DATAPUMP.METADATA_FILTER(h2,'SCHEMA_EXP','IN (''sc900'')');
  DBMS_DATAPUMP.START_JOB(h2);
  dbms_datapump.detach(h2);
END;

The error output is

 ORA-39001: invalid argument value
 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 3507
 ORA-06512: at "SYS.DBMS_DATAPUMP", line 3756

I am connected as 'sys' and 'datapump_dir' creation statement was successfully executed.

What am i doing wrong? Thanks in advance!

Apart from creating DATAPUMP_DIR, did you grant READ & WRITE on that directory to user that runs code you wrote? — Littlefoot, Jan 10 '18 at 12:35
`SCHEMA_EXP` is [not a valid metadata filter](https://docs.oracle.com/database/121/ARPLS/d_datpmp.htm#BABDDDEF), it's `SCHEMA_EXPR`. I suspect that `sc900` is also invalid and should be upper case, i.e. `q'{IN ('SC900')}'` — Ben, Jan 10 '18 at 13:10
@Ben thanks, i've updated this line, however, as i've written, the problem is in the previous one - "DBMS_DATAPUMP.ADD_FILE(h2,'test1.dmp','datapump_dir')" — Eva Leigh, Jan 10 '18 at 13:18
Have you rerun the code in addition to changing it? Did you get the exact same error? — Ben, Jan 10 '18 at 13:24

score 1 · Accepted Answer · edited Oct 09 '20 at 17:24

1

If anyone will be interested in, I found the problem cause. For some reason, it didn't work, even if I specified default directory. However, when I pass null to this parameter, it works. The following code works, the only disadvantage is that I can save to default directory only

DECLARE
  h2 NUMBER;
BEGIN
  h2 := DBMS_DATAPUMP.OPEN('EXPORT', 'SCHEMA');
  DBMS_DATAPUMP.ADD_FILE(h2, 'example0.dmp', null);
  DBMS_DATAPUMP.METADATA_FILTER(h2,'SCHEMA_EXPR', q'{IN ('SC900')}');
  DBMS_DATAPUMP.START_JOB(h2);
  DBMS_DATAPUMP.DETACH(h2);
END;
/

edited Oct 09 '20 at 17:24

EvilTeach

28,120
21
85
141

answered Jan 10 '18 at 13:57

Eva Leigh

51
1
9

Ya. ADD_FILE takes 4 argument. Typically a directory and a type. The type can specify the log or dump file. I would guess null is defaulting the directory and the missing type is defaulting to dump. – EvilTeach Oct 09 '20 at 17:24

Oracle Datapump API

1 Answers1