3 Replies Latest reply: Apr 3, 2013 4:31 AM by Richard Harrison . RSS

    Export and import using datapump api

    JP88
      Hi ,

      Could any one pls provide a unix shell script to call expdp/impdp api package ?

      Thanks in advance.

      Regards
      JP
        • 1. Re: Export and import using datapump api
          Richard Harrison .
          Hi,
          Not sure why you would want to do this?

          I would have thought you either:

          a) call impdp/expdp directly from a shell script
          b) call dbms_datapump from within the database

          Why would you want to call the api directly from a shell script?

          If you did want to do it you'd just call it in the same way as any other plsql routine from unix - do you just want an example of that?

          Regards,
          Harry
          • 2. Re: Export and import using datapump api
            JP88
            Hi Harry,

            Yes i need an example

            Regards
            JP
            • 3. Re: Export and import using datapump api
              Richard Harrison .
              Hi,
              A very simple example would be:
              #!/bin/ksh
              export ORACLE_SID=YOUR_SID_HERE
              export ORAENV_ASK=NO
              . oraenv
              
              sqlplus -s '/as sysdba' <<EOF
              @yoursql.sql
              EOF
              the file yoursql.sql would contain the dbms_datapump commands - something like this:
              DECLARE
                l_dp_handle       NUMBER;
                l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
                l_job_state       VARCHAR2(30) := 'UNDEFINED';
                l_sts             KU$_STATUS;
                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  => 'TpBackup1.dmp',
                  directory => 'TEST2',
                  reusefile => 1 );
              
              
              DBMS_DATAPUMP.add_file(
                  handle    => l_dp_handle,
                  filename  => 'TpBackupLog.log',
                  directory => 'TEST2',
                  filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                  reusefile => 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;
              /
              Cheers,
              Harry