Oracle datapump export error "invalid argument value"

Question

I have a datapump export script which I'd like to get running. The DB is Oracle 11g. To try it out, I've been executing it in SQLDeveloper. The script looks like this:

DECLARE  
     JOBHANDLE   NUMBER;
     STATUS      VARCHAR2(20);
     LOGFILE     UTL_FILE.FILE_TYPE; 
     LINE        VARCHAR2(200); 
     ROW         NUMBER := 1; 
 
     TYPE OUTPUT_TYPE IS TABLE OF VARCHAR2(128) INDEX BY PLS_INTEGER;
     OUTPUT      OUTPUT_TYPE;
 BEGIN
     EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY "TESTDIR11" AS ''/home/achim/temp/0003759/T0987654321/'' ';
 
     JOBHANDLE := DBMS_DATAPUMP.OPEN(
         operation => 'EXPORT',
         job_mode  => 'TABLE',
         job_name  => 'TST TMF.ACHIMSTEST11');
  
     DBMS_DATAPUMP.ADD_FILE(
         HANDLE    => JOBHANDLE,
         filename  => 'Q01DED3D.dmp',
         directory => 'TESTDIR11',
         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
         ,reusefile => 1
         );
  
     DBMS_DATAPUMP.ADD_FILE(
         HANDLE    => JOBHANDLE,
         filename  => 'Q01DED3D.log',
         directory => 'TESTDIR11',
         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
         ,reusefile => 1
         );
 
     BEGIN
         DBMS_DATAPUMP.SET_PARAMETER(
                 handle => JOBHANDLE,
                 name   => 'INCLUDE_METADATA',
                 value  => 0);
 
         DBMS_DATAPUMP.METADATA_FILTER(
                 HANDLE      => JOBHANDLE, 
                 name        => 'NAME_LIST', 
                 value       => '''ACHIMSTEST11''',
                 object_path => 'TABLE');
 
         DBMS_DATAPUMP.METADATA_FILTER(
                 HANDLE  => JOBHANDLE, 
                 name    => 'SCHEMA_LIST', 
                 value   => '''TMF''');
 
         DBMS_DATAPUMP.START_JOB(JOBHANDLE);
         DBMS_DATAPUMP.WAIT_FOR_JOB(JOBHANDLE, STATUS);
     EXCEPTION
         WHEN OTHERS 
         THEN
             status := 'ERROR'; 
     END;
 
     DBMS_DATAPUMP.DETACH(JOBHANDLE); 
 
     LOGFILE := UTL_FILE.FOPEN('TESTDIR', 'Q01DED3D.log', 'R'); 
     Loop BEGIN
         UTL_FILE.GET_LINE(LOGFILE, LINE);
         OUTPUT(ROW) := LINE; 
         ROW := ROW + 1;
     EXCEPTION 
         WHEN No_Data_Found 
         THEN 
             EXIT; 
         END;
     END Loop; 
 
     EXECUTE IMMEDIATE 'DROP DIRECTORY "TESTDIR"';
 
 END;

The trouble is that I get an error which I cannot explain and I have no idea how to track it down. The error message is:

Error report:

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

ORA-06512: at line 18

00000 - "invalid argument value"

*Cause: The user specified API parameters were of the wrong type or

value range. Subsequent messages supplied by

DBMS_DATAPUMP.GET_STATUS will further describe the error.

*Action: Correct the bad argument and retry the API.

The important part here is the "ORA-06512: at line 18". This points to the first ADD_FILE statement. After scouring the Internet Oracle documentation regarding the datapump, I am at a loss as to what is supposed to be wrong. I also can't figure out how to use DBMS_DATAPUMP.GET_STATUS when the job doesn't even get defined, let alone run.

I've tried not using the reusefile parameter and I've tried replacing DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE with the actual integer value it represents, which is 1.

Running this in SQLDeveloper leaves an entry floating around in dba_datapump_jobs, which isn't all that simple to get rid of. The state of the job is "DEFINING". This prevents me running the script a second time. After searching for around an hour I found out that it could be removed with DROP TABLE "TMF"."TST TMF.ACHIMSTEST11" PURGE, though why that works is a mystery.

I guess I should point out that I did not write this script, it was generated from a FreeMarker template in a Java program. In the Java program the generated script is run as an OracleCallableStatement and for 11g databases that works. The only reason I'm fiddling with it in SQLDeveloper is to try to get it to work for a 12c database. Needless to say the script doesn't work for 12c in SQLDeveoper either. I'd kind of hoped it would work for 11g.

Incidentally, the error I get when running the Java with a 12c database is the same one as shown above.

If anyone can see anything that is actually wrong or even just looks suspect, I'd be very grateful for the advice.

Oracle datapump export error "invalid argument value"

1 Answers