This discussion is archived
10 Replies Latest reply: Sep 13, 2013 5:59 AM by L-MachineGun RSS

Error when using dbms_pumpdata to export data

kaitokidscs Newbie
Currently Being Moderated

DECLARE 

       l_dp_handle   NUMBER; 

    BEGIN 

       l_dp_handle := 

          DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE'); 

       dbms_datapump. 

        add_file (handle      => l_dp_handle 

                , filename    => 'scott_tb_emp.dmp' 

                , directory   => 'DB_DUMP_DIR' 

                , filetype    => DBMS_DATAPUMP.KU$_file_type_dump_file); 

       dbms_datapump. 

        add_file (handle      => l_dp_handle 

                , filename    => 'scott_tb_emp.log' 

                , directory   => 'DB_DUMP_DIR' 

                , filetype    => DBMS_DATAPUMP.KU$_file_type_log_file); 

       DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle 

                                    , name          => 'NAME_EXPR' 

                                    , VALUE         => ' =''MYTABLE'' ' 

                                    , object_type   => 'TABLE'); 

     DBMS_DATAPUMP.data_filter( handle       => l_dp_handle 

                                , name         => 'SUBQUERY' 

                                , VALUE        => 'WHERE DT < ''11/01/2010''' 

                                , table_name   => 'MYTABLE' );                                 

       DBMS_DATAPUMP.start_job (l_dp_handle); 

       DBMS_DATAPUMP.detach (l_dp_handle); 

    END;

 

I want to export a subset of data in MYTABLE, it gave error

11:17:54  [DECLARE - 0 row(s), 0.000 secs]  [Error Code: 39001, SQL State: 99999]  ORA-39001: invalid argument value

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693

ORA-06512: at line 6

... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

  • 1. Re: Error when using dbms_pumpdata to export data
    sb92075 Guru
    Currently Being Moderated

    >, VALUE        => 'WHERE DT < ''11/01/2010'''

     

    what datatype is "DT"

     

    what characters, single quotes or double quotes, surround rightmost string in line above?

     

    How do I ask a question on the forums?

    https://forums.oracle.com/message/9362002#9362002

  • 2. Re: Error when using dbms_pumpdata to export data
    kaitokidscs Newbie
    Currently Being Moderated

    It doesn't matter here, if I  change it to "WHERE 1=1" it gives the same error, BTW I run this script in DbVisualizer 8.0.1

  • 3. Re: Error when using dbms_pumpdata to export data
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    I think the problem might be the wrong type of quote in one of the lines - try copying most of the code from an old example i did and replace the relevant bits with you specifics and see if that works:

     

    http://dbaharrison.blogspot.de/2013/04/dbmsdatapump-example-of-extracting-data.html

     

    Cheers,

    Harry

  • 4. Re: Error when using dbms_pumpdata to export data
    L-MachineGun Pro
    Currently Being Moderated

    On your other post you asked:

     

    how to read log file right after using DBMS_DATAPUMP export some data , , ,

    . . . E t c . . .

    My problem is that the log file was created but seems not flushed. I can't fine anything . . .

     

    The reason is that the following statement submits the job in the background and you may be trying to read an empty log file (job still running)?

     

    DBMS_DATAPUMP.detach (l_dp_handle);
  • 5. Re: Error when using dbms_pumpdata to export data
    kaitokidscs Newbie
    Currently Being Moderated

    true! I added DBMS_DATAPUMP.wait_for_job (dp_handle, status) and solved that problem!

  • 6. Re: Error when using dbms_pumpdata to export data
    kaitokidscs Newbie
    Currently Being Moderated

    This script works now but I have new problem, data_filter doesn't work when WHERE condition include a Date like "VALUE        => 'WHERE DT < ''11/01/2010''' ", it will get nothing then. Does any one know why?...

  • 7. Re: Error when using dbms_pumpdata to export data
    sb92075 Guru
    Currently Being Moderated

    >data_filter doesn't work when WHERE condition include a Date like "VALUE        => 'WHERE DT < ''11/01/2010''' ", it will get nothing then.


    "11/01/2010" is a STRING; not a DATE datatype & due to likely incorrect  implicit conversion.

    Is above 11 January or November 01?

  • 8. Re: Error when using dbms_pumpdata to export data
    kaitokidscs Newbie
    Currently Being Moderated

    I just don't know the right format for a datetype data should be written in this where clause. Month doesn't matter because if I change the year still get nothing...

  • 9. Re: Error when using dbms_pumpdata to export data
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    Check this test, i can get the successful dump file

     

    SQL> DECLARE

           l_dp_handle   NUMBER;

           job_status VARCHAR2(30);

     

     

        BEGIN

           l_dp_handle :=

              DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'TABLE');

           dbms_datapump.

            add_file (handle      => l_dp_handle

                    , filename    => 'scott_tb_emp.dmp'

                    , directory   => 'DATA_DD_DIR'

                    , filetype    => DBMS_DATAPUMP.KU$_file_type_dump_file);

           dbms_datapump.

            add_file (handle      => l_dp_handle

                    , filename    => 'scott_tb_emp.log'

                    , directory   => 'DATA_DD_DIR'

                    , filetype    => DBMS_DATAPUMP.KU$_file_type_log_file);

           DBMS_DATAPUMP.metadata_filter (handle        => l_dp_handle

                                        , name          => 'NAME_EXPR'

                                        , VALUE         => ' =''EMP'' '

                                        , object_type   => 'TABLE');

         DBMS_DATAPUMP.data_filter( handle       => l_dp_handle

                                    , name         => 'SUBQUERY'

                                    , VALUE        => 'WHERE SAL > 1000'

                                    , table_name   => 'EMP' );                                

           DBMS_DATAPUMP.start_job (l_dp_handle);

           dbms_output.put_line ('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')||' Status '||job_status);

     

     

           DBMS_DATAPUMP.detach (l_dp_handle);

        END;

       2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31 

    32  /

     

     

    PL/SQL procedure successfully completed.

     

     

    SQL> !ls -lrt|tail

    total 144

    -rw-rw-r-- 1 oracle oracle    858 Sep 13 11:08 scott_tb_emp.log

    -rw-rw---- 1 oracle oracle 110592 Sep 13 11:08 scott_tb_emp.dmp

     

     

    SQL> !cat scott_tb_emp.log

    Starting "ME"."SYS_EXPORT_TABLE_01": 

    Estimate in progress using BLOCKS method...

    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 64 KB

    Processing object type TABLE_EXPORT/TABLE/TABLE

    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

    Processing object type TABLE_EXPORT/TABLE/AUDIT_OBJ

    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

    . . exported "ME"."EMP"                                  8.515 KB      13 rows

    Master table "ME"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

    ******************************************************************************

    Dump file set for ME.SYS_EXPORT_TABLE_01 is:

      /home/oracle/MYSHELL/scott_tb_emp.dmp

    Job "ME"."SYS_EXPORT_TABLE_01" successfully completed at 11:08:47

     

     

    SQL> select count(*) from emp where sal>1000;

     

     

      COUNT(*)

    ----------

            13

     

    Hope this help

  • 10. Re: Error when using dbms_pumpdata to export data
    L-MachineGun Pro
    Currently Being Moderated

    Try this:

     

       -- - - -
          , VALUE        => 'WHERE DT < TO_DATE(''11/01/2010'',''MM/DD/YYYY'')' 

     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points