10 Replies Latest reply: Sep 13, 2013 7:59 AM by L-MachineGun RSS

    Error when using dbms_pumpdata to export data

    kaitokidscs

      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

          >, 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

            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 .

              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

                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

                  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

                    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

                      >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

                        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

                          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

                            Try this:

                             

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