This discussion is archived
11 Replies Latest reply: Sep 6, 2013 12:37 PM by kaitokidscs RSS

How to use dbms_datapump to import a table?

kaitokidscs Newbie
Currently Being Moderated

--/

declare

  h1   NUMBER;

  begin

     h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', job_name => 'ddIMPORT1', version => 'COMPATIBLE');

     dbms_datapump.add_file(handle => h1, filename => 'test.log', directory => 'DUMPFILE_DIR', filetype => 3);

     dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);

     dbms_datapump.add_file(handle => h1, filename => 'test.dmp', directory => 'DUMPFILE_DIR', filetype => 1);

     dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

     dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 0);

     dbms_datapump.start_job(handle => h1);

  end;

  /

 

I have error

12:49:49  [DECLARE - 0 row(s), 0.000 secs]  [Error Code: 31627, SQL State: 99999]  ORA-31627: API call succeeded but more information is available

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

ORA-06512: at line 10

  • 1. Re: How to use dbms_datapump to import a table?
    sb92075 Guru
    Currently Being Moderated

    is there any particular reason you are trying to do this via PL/SQL; instead of just the command line interface?

     

     

     

    How do I ask a question on the forums?

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

  • 2. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    because I'm doing it in a Java program. Can you give the command line result? at least I can test it. thank you!

  • 3. Re: How to use dbms_datapump to import a table?
    sb92075 Guru
    Currently Being Moderated

    [oracle@localhost ~]$ oerr ora 31627

    31627, 00000, "API call succeeded but more information is available"

    // *Cause:  The user specified job parameters that yielded informational

    //          messages.

    // *Action: Call DBMS_DATAPUMP.GET_STATUS to retrieve additional information.

     

    it says it worked.

    did you SELECT against the table afterwards?

    what was returned?

  • 4. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    --/

    declare

      h1   NUMBER;

      begin

         h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'TABLE', job_name => 'ddIMPORT3', version => 'COMPATIBLE');

         dbms_datapump.add_file(handle => h1, filename => 'test.log', directory => 'DUMPFILE_DIR', filetype => 3);

         dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);

         dbms_datapump.add_file(handle => h1, filename => 'test.dmp', directory => 'DUMPFILE_DIR', filetype => 1);

         dbms_datapump.set_parameter(handle => h1, name => 'DATA_ACCESS_METHOD', value => 'AUTOMATIC');

         dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);

         dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'REPLACE');

         dbms_datapump.set_parameter(handle => h1, name => 'SKIP_UNUSABLE_INDEXES', value => 0);

         dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);

         dbms_datapump.detach(handle => h1);

      end;

      /

     

    this one works but no rows were affected. Actually I just want to read my dump file to see if the data is good... any one has good suggestions..

  • 5. Re: How to use dbms_datapump to import a table?
    sb92075 Guru
    Currently Being Moderated

    Forgive me if this is a dumb question, but into which schema & which table should the data be loaded?

     

    How does it decide which data goes into which columns?

  • 6. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    I guess TABLE information is in dump file because when I run this script the table with some data became empty, at least it is able to find the table. My question is why it is empty... Is it something wrong with my dump file or with this scripts...

  • 7. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    By the way my export script is:

    DECLARE 

           l_dp_handle        NUMBER; 

        BEGIN 

           l_dp_handle := 

              DBMS_DATAPUMP.open (operation     => 'EXPORT' 

                                , job_mode      => 'TABLE' 

                                , remote_link   => NULL 

                                , job_name      => 'JOB_EXP7' 

                                , version       => 'LATEST'); 

           DBMS_DATAPUMP. 

            add_file (handle      => l_dp_handle 

                    , filename    => 'test.dmp' 

                    , directory   => 'DUMPFILE_DIR' 

                    , filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE); 

           DBMS_DATAPUMP. 

            add_file (handle      => l_dp_handle 

                    , filename    => 'test.log' 

                    , directory   => 'DUMPFILE_DIR' 

                    , filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 

                   

           DBMS_DATAPUMP. 

            metadata_filter (handle   => l_dp_handle 

                           , name     => 'SCHEMA_EXPR' 

                           , VALUE    => 'IN(''MY_SCHEMA'')'); 

           DBMS_DATAPUMP. 

            metadata_filter (handle   => l_dp_handle 

                           , name     => 'NAME_EXPR' 

                           , VALUE    => '=''MY_TABLE'''

                           , object_type   => 'TABLE');

           DBMS_DATAPUMP.

            data_filter( handle   => l_dp_handle 

                       , name   => 'SUBQUERY' 

                       , VALUE   => 'WHERE 1=1''' 

                       , table_name   => 'MY_TABLE' );

           DBMS_DATAPUMP.start_job (l_dp_handle); 

           DBMS_DATAPUMP.detach (l_dp_handle); 

        END;

        /

     

    When change where condition in data_filter the size of .dmp file changes so I guess data should be in that .dmp file...

  • 8. Re: How to use dbms_datapump to import a table?
    sb92075 Guru
    Currently Being Moderated

    >Re: How to use dbms_datapump to import a table?

    >By the way my export script is:

     

     

    one of us is sorely confused.(see contradiction in 2 lines above)

    import is exact opposite of export.

     

    in  which direction do you desire to move data between database & external OS file?

  • 9. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    I need to put data into database from external file... Do you know the easiest to do this?...

  • 10. Re: How to use dbms_datapump to import a table?
    sb92075 Guru
    Currently Being Moderated

    if the datafile resides on the DB Server, EXTERNAL TABLE is easiest way to map & moved datafile content into the DB

  • 11. Re: How to use dbms_datapump to import a table?
    kaitokidscs Newbie
    Currently Being Moderated

    I'm new in Oracle, can you give some useful links for this? Actually I can import the external files now. What is need to do is actually check the export file to validate if it is good data(maybe by counting affected rows of other ways), do you have anything in your mind to solve this problem? Thank you!

Legend

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