DBMS_DATAPUMP finding out what the actual error is when things go wrong

Normally when datapump fails you get a sensible error message but sometimes it seems to have hidden what the problem actually was. In the example below


DECLARE

  l_dp_handle NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state VARCHAR2(30) := 'UNDEFINED';
  l_sts  KU$_STATUS;
  v_logs ku$_LogEntry;
  v_row  PLS_INTEGER;
  v_job_state varchar2(4000);

BEGIN

  l_dp_handle := DBMS_DATAPUMP.open(operation   => 'EXPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => NULL,
                                    version     => 'LATEST');

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.dmp',
                         directory => 'SCRATCH',
                         reusefile => 1);

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.log',
                         directory => 'SCRATCH',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                         reusefile => 1);

  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
  DBMS_DATAPUMP.DATA_FILTER(handle => l_dp_handle,
                            name   => 'INCLUDE_ROWS',
                            value  => 0);
  dbms_datapump.set_debug(debug_flags  => to_number('1FF0300',
                                                    'XXXXXXXXXXXXX'),
                          version_flag => 1);

  DBMS_DATAPUMP.start_job(l_dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);

  DBMS_OUTPUT.PUT_LINE(v_job_state);

END;



when i create a new user and just give it create session and then run this plsql block i just get the message


Which tells me nothing about what actually went wrong


If i add the exception block in though i get the actual problem from DBMS_OUTPUT.

DECLARE

  l_dp_handle NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state VARCHAR2(30) := 'UNDEFINED';
  l_sts  KU$_STATUS;
  v_logs ku$_LogEntry;
  v_row  PLS_INTEGER;
  v_job_state varchar2(4000);

BEGIN

  l_dp_handle := DBMS_DATAPUMP.open(operation   => 'EXPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => NULL,
                                    version     => 'LATEST');

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.dmp',
                         directory => 'SCRATCH',
                         reusefile => 1);

  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.log',
                         directory => 'SCRATCH',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                         reusefile => 1);

  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''ALIGNE''');
  DBMS_DATAPUMP.DATA_FILTER(handle => l_dp_handle,
                            name   => 'INCLUDE_ROWS',
                            value  => 0);
  dbms_datapump.set_debug(debug_flags  => to_number('1FF0300',
                                                    'XXXXXXXXXXXXX'),
                          version_flag => 1);

  DBMS_DATAPUMP.start_job(l_dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);

  DBMS_OUTPUT.PUT_LINE(v_job_state);

EXCEPTION
  WHEN OTHERS THEN
    dbms_datapump.get_status(NULL, 8, 0, v_job_state, l_sts);
    v_logs := l_sts.error;
 
    v_row := v_logs.FIRST;
    LOOP
      EXIT WHEN v_row IS NULL;
      dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);
      dbms_output.put_line('errorNumber=' || v_logs(v_row).errorNumber);
      dbms_output.put_line('LogText=' || v_logs(v_row).LogText);
      v_row := v_logs.NEXT(v_row);
    END LOOP;
    RAISE;
END;









The user just needs create table rights and it will work. This is very useful for when datapump isn;t telling you what is really wrong.

0 comments:

Post a Comment