Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_DATAPUMP.ADD_FILE : ORA-39001
DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #226182] Thu, 22 March 2007 14:26 Go to next message
tplank
Messages: 7
Registered: March 2007
Junior Member
I have a stored proc that is giving me an "ORA-39001: invalid argument value" message when I execute the ADD_FILE. I'll put the proc at the bottom. I've tried every option. I've double checked that the user Oracle can read and write to the file system directory. I've double checked the persmissions. I've tried to execute using the user SYS. I've tried it with and without the extra default parameters. I'm stuck. Does anyone have any clues about this?

It is the first ADD_FILE which is throwing the exception.

--------------------------------------------------

CREATE OR REPLACE PROCEDURE import_test_data (p_filename IN VARCHAR2) AS
   l_handle           NUMBER; 
   l_temp             NUMBER; 
   l_state            VARCHAR2(20);
   l_date             VARCHAR2(20);
   l_date_started     DATE;
   ind                NUMBER;       
   percent_done       NUMBER;       
   job_state          VARCHAR2(30);   
   le                 ku$_LogEntry;  
   js                 ku$_JobStatus;  
   jd                 ku$_JobDesc;    
   sts                ku$_Status;   

BEGIN

   l_date_started := SYSDATE;
   l_date := to_char(l_date_started,'YYYYMMDD-HH24MI');

   l_handle := DBMS_DATAPUMP.OPEN ('IMPORT','TABLE', NULL, 'TESTDATA'||l_date);   

   DBMS_DATAPUMP.ADD_FILE (l_handle, p_filename,'TESTDATA', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
   DBMS_DATAPUMP.ADD_FILE (l_handle, p_filename||'.log','TESTDATA', NULL, DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
   
   DBMS_DATAPUMP.SET_PARAMETER (l_handle, 'INCLUDE_METADATA', 0);
   DBMS_DATAPUMP.SET_PARAMETER (l_handle, 'TABLE_EXISTS_ACTION', 'APPEND');

   DBMS_DATAPUMP.METADATA_FILTER (l_handle,'NAME_LIST','''RT_MESSAGES''',NULL,'TABLE');
   
   DBMS_DATAPUMP.START_JOB (l_handle);
   DBMS_DATAPUMP.WAIT_FOR_JOB (l_handle, l_state);

END;

Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #226250 is a reply to message #226182] Fri, 23 March 2007 01:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
ORA-39001: 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.


Could be a starting point to call get_status

Also found this reference

[Edit: added link]

[Updated on: Fri, 23 March 2007 01:37]

Report message to a moderator

Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #226393 is a reply to message #226250] Fri, 23 March 2007 08:53 Go to previous messageGo to next message
tplank
Messages: 7
Registered: March 2007
Junior Member
Thanks for the response. I don't think that thread is relevant to my issue. Also, I started to post my exception handling but my get_status call is not providing any additional information.
Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #226397 is a reply to message #226393] Fri, 23 March 2007 09:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Is it the second add that fails ?
Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #226443 is a reply to message #226397] Fri, 23 March 2007 14:56 Go to previous messageGo to next message
tplank
Messages: 7
Registered: March 2007
Junior Member
No, it is the first ADD_FILE that fails. Do you see something with the second?
Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #231029 is a reply to message #226182] Fri, 13 April 2007 13:37 Go to previous messageGo to next message
Andreas68
Messages: 1
Registered: April 2007
Junior Member
Have you checked if the file, which you are trying to add, already exists?
I had the experience that I cannot add a dump file which already exists.
Re: DBMS_DATAPUMP.ADD_FILE : ORA-39001 [message #233103 is a reply to message #226182] Tue, 24 April 2007 13:34 Go to previous message
discojohnson
Messages: 1
Registered: April 2007
Junior Member
below is an example of what i use to catch the difficult exceptions thrown by the datapump.

  PROCEDURE estimate_space(in_schema IN VARCHAR2, in_dblink IN VARCHAR2)
  IS
    v_handle NUMBER;
    v_job_state VARCHAR2(4000);
    v_status ku$_Status1010;
    v_logs ku$_LogEntry1010;
    v_row PLS_INTEGER;
  BEGIN
    ...
  EXCEPTION
    WHEN OTHERS THEN
      dbms_datapump.get_status(NULL, 8, 0, v_job_state, v_status);
      v_logs := v_status.error;
      
      v_row := v_logs.FIRST;
      LOOP
        EXIT WHEN v_row IS NULL;
        debugwriter('logLineNumber='||v_logs(v_row).logLineNumber);
        debugwriter('errorNumber='||v_logs(v_row).errorNumber);
        debugwriter('LogText='||v_logs(v_row).LogText);
        v_row := v_logs.NEXT (v_row);
      END LOOP;
  END;
Previous Topic: How to use cursor with execute immediate
Next Topic: Re-Compiling package
Goto Forum:
  


Current Time: Sat Dec 03 12:07:45 CST 2016

Total time taken to generate the page: 0.09457 seconds