9 Replies Latest reply: Feb 14, 2013 7:24 PM by maggie RSS

    Datapump exp and imp using API method

    maggie
      Good Day All,

      I want to know what is the best way of error handling of datapump export and Import using API. I need to implement in my current project as there lot of limitations and the only way to see the process worked is writing the code with error handling method using exceptions. I have seen some examples on the web but if there are practicle examples or good links with examples that will work sure way, I would like to know and explore. I have never used API method so I am not sure of it.

      Thanks a lot for your time.

      Maggie.
        • 1. Re: Datapump exp and imp using API method
          sb92075
          http://www.lmgtfy.com/?q=oracle+data+pump+api+examples
          • 2. Re: Datapump exp and imp using API method
            Osama_Mustafa
            Oracle documentation
            http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_api.htm
            • 3. Re: Datapump exp and imp using API method
              maggie
              I wrote the procedure with error handling but it does not out put any information of the statuses while kicking off the expdp process. I have put dbms_output.put_line as per oracle docs example but it doesnt display any messages, just kicks off and created dumpfiles. As a happy path its ok but I need to track if something goes wrong. I even stated set serveroutput on sqlplus. It doesnt even display if job started. Please help me where I made a mistake to display the status . Do I need to modify or add anything. Help!!


              CREATE OR REPLACE PROCEDURE SCHEMAS_EXPORT_TEST AS
              ------------------------------------------------------------------------------------------------
              --Using Exception Handling During a Simple Schema Export
              --This Proceedure shows a simple schema export using the Data Pump API.
              --It extends to show how to use exception handling to catch the SUCCESS_WITH_INFO case,
              --and how to use the GET_STATUS procedure to retrieve additional information about errors.
              --If you want to get status up to the current point, but a handle has not yet been obtained,
              --you can use NULL for DBMS_DATAPUMP.GET_STATUS.http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_api.htm
              ------------------------------------------------------------------------------------------------
              h1 number; -- Data Pump job handle
              l_handle number;
              ind NUMBER; -- Loop index
              spos NUMBER; -- String starting position
              slen NUMBER; -- String length for output
              percent_done NUMBER; -- Percentage of job complete
              job_state VARCHAR2(30); -- To keep track of job state
              sts ku$_Status; -- The status object returned by get_status
              le ku$_LogEntry; -- For WIP and error messages
              js ku$_JobStatus; -- The job status from get_status
              jd ku$_JobDesc; -- The job description from get_status

              BEGIN

              h1 := dbms_datapump.open (operation => 'EXPORT',job_mode => 'SCHEMA');

              dbms_datapump.add_file (handle => h1,filename => 'SCHEMA_BKP_%U.DMP',directory => 'BKP_SCHEMA_EXPIMP',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

              dbms_datapump.add_file (handle => h1,directory => 'BKP_SCHEMA_EXPIMP',filename => 'SCHEMA_BKP_EX.log',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

              ---- A metadata filter is used to specify the schema that will be exported.

              dbms_datapump.metadata_filter (handle => h1, name => 'SCHEMA_LIST',value => q'|'XXXXXXXXXX'|');

              dbms_datapump.set_parallel( handle => h1, degree => 4);

              -- Start the job. An exception will be returned if something is not set up
              -- properly.One possible exception that will be handled differently is the
              -- success_with_info exception. success_with_info means the job started
              -- successfully, but more information is available through get_status about
              -- conditions around the start_job that the user might want to be aware of.

              begin
              dbms_datapump.start_job (handle => h1);
              dbms_output.put_line('Data Pump job started successfully');
              exception
              when others then
              if sqlcode = dbms_datapump.success_with_info_num
              then
              dbms_output.put_line('Data Pump job started with info available:');
              dbms_datapump.get_status(h1,
              dbms_datapump.ku$_status_job_error,0,
              job_state,sts);
              if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
              then
              le := sts.error;
              if le is not null
              then
              ind := le.FIRST;
              while ind is not null loop
              dbms_output.put_line(le(ind).LogText);
              ind := le.NEXT(ind);
              end loop;
              end if;
              end if;
              else
              raise;
              end if;
              end;
              -- The export job should now be running. In the following loop, we will monitor the job until it completes.
              -- In the meantime, progress information is displayed.

              percent_done := 0;
              job_state := 'UNDEFINED';
              while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
              dbms_datapump.get_status(h1,
              dbms_datapump.ku$_status_job_error +
              dbms_datapump.ku$_status_job_status +
              dbms_datapump.ku$_status_wip,-1,job_state,sts);
              js := sts.job_status;
              -- If the percentage done changed, display the new value.

              if js.percent_done != percent_done
              then
              dbms_output.put_line('*** Job percent done = ' ||to_char(js.percent_done));
              percent_done := js.percent_done;
              end if;
              -- Display any work-in-progress (WIP) or error messages that were received for
              -- the job.

              if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
              then
              le := sts.wip;
              else
              if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
              then
              le := sts.error;
              else
              le := null;
              end if;
              end if;
              if le is not null
              then
              ind := le.FIRST;
              while ind is not null loop
              dbms_output.put_line(le(ind).LogText);
              ind := le.NEXT(ind);
              end loop;
              end if;
              end loop;

              -- Indicate that the job finished and detach from it.
              dbms_output.put_line('Job has completed');
              dbms_output.put_line('Final job state = ' || job_state);

              dbms_datapump.detach (handle => h1);
              -- Any exceptions that propagated to this point will be captured. The
              -- details will be retrieved from get_status and displayed.
              Exception

              when others then
              dbms_output.put_line('Exception in Data Pump job');
              dbms_datapump.get_status(h1,dbms_datapump.ku$_status_job_error,0, job_state,sts);
              if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
              then
              le := sts.error;
              if le is not null
              then
              ind := le.FIRST;
              while ind is not null loop
              spos := 1;
              slen := length(le(ind).LogText);
              if slen > 255
              then
              slen := 255;
              end if;
              while slen > 0 loop
              dbms_output.put_line(substr(le(ind).LogText,spos,slen));
              spos := spos + 255;
              slen := length(le(ind).LogText) + 1 - spos;
              end loop;
              ind := le.NEXT(ind);
              end loop;
              end if;
              end if;

              END SCHEMAS_EXPORT_TEST;
              • 4. Re: Datapump exp and imp using API method
                maggie
                Hello all,
                How do I spool the information of dbms_output.putline that i capture while running is in progress? I cannot spool in pl/sql procedure and utl_file command cannot be used due to security, what is the best way to capture the process? Please guide me.

                Thanks
                Maggie
                • 5. Re: Datapump exp and imp using API method
                  Osama_Mustafa
                  maggie wrote:
                  Hello all,
                  How do I spool the information of dbms_output.putline that i capture while running is in progress? I cannot spool in pl/sql procedure and utl_file command cannot be used due to security, what is the best way to capture the process? Please guide me.

                  Thanks
                  Maggie
                  Check this thread
                  Stored procedure : spool to output file
                  • 6. Re: Datapump exp and imp using API method
                    maggie
                    Thanks for reply, is there a way to capture all the information of the above procedure todbms_output.putline into a error_handling table, if so how do we do it, any such examples to be shared.

                    thanks a lot

                    Maggie

                    Edited by: maggie on Feb 13, 2013 10:57 AM
                    • 7. Re: Datapump exp and imp using API method
                      Richard Harrison .
                      Hi maggie,
                      Create a small procedure that writes to an error table with the pragma autonomous transaction statement at the start. This will write logging independent of the calling program, similar to what you achieve with utl file. Just google for that and you'll find lots of examples.

                      Cheers,
                      Harry
                      • 8. Re: Datapump exp and imp using API method
                        maggie
                        Hi Sir,

                        Thanks for lead. Its helpful. I have noticed in some examples that Pragma Autonomous Transactions are only used in exceptions. But I would like to capture every dbms_output.putline info in the error_log table of the datapump process. This is the only way we can track the progress of percent done. How can i achieve this. I pasted my code in the above thread. Can you pinpoint where i can use to insert in my error logs. Thanks again for great information

                        Sincerely
                        Maggie
                        • 9. Re: Datapump exp and imp using API method
                          maggie
                          Thank You. I have used pragma autonomous transation method to my procedure

                          Edited by: maggie on Feb 14, 2013 5:24 PM