6 Replies Latest reply on Mar 9, 2015 12:20 PM by Turloch O'Tierney-Oracle

    Running Datapump inside SQL worksheet....

    goj

      Hi All,

      running the following code inside worksheet and it just spits out ' Anonymous block completed".

       

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

       

      set scan off

      set serveroutput on

      set escape off

      whenever sqlerror exit

      DECLARE

          h1 number;

          errorvarchar varchar2(100):= 'ERROR';

          tryGetStatus number := 0;

      begin

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

          tryGetStatus := 1;

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

          dbms_datapump.add_file(handle => h1, filename => 'IMPORTtest2.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);

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

          dbms_datapump.add_file(handle => h1, filename => 'ORAEU_COPIED.DMP', directory => 'DATA_PUMP_DIR', filetype => 1);

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\APT_SYS_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/APT_SYS_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\APT_SYS_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/APT_SYS_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATA_MART_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_MART_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATA_MART_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_MART_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL/DATA_STORE_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_STORE_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATA_STORE_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_STORE_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\RNET2_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/RNET2_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\RNET2_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/RNET2_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATA_AMA_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_AMA_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATA_AMA_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATA_AMA_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATASERVICES_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATASERVICES_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\DATASERVICES_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/DATASERVICES_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\ARC_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/ARC_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\MCSS_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/MCSS_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\MCSS_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/MCSS_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\IPAS_DATA01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/IPAS_DATA01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\IPAS_IDX01.DBF') , value => UPPER('/rdsdbdata/db/ORAEU_A/datafile/IPAS_IDX01.DBF') );

          dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\PFIZERCMS_DATA01.DBF') , value =>

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

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

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

          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);

          errorvarchar := 'NO_ERROR';

      EXCEPTION

          WHEN OTHERS THEN

          BEGIN

              IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

                  DBMS_DATAPUMP.DETACH(h1);

              END IF;

          EXCEPTION

          WHEN OTHERS THEN

              NULL;

          END;

          RAISE;

      END;

      /

       

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

       

      any inputs are appreciated

      thanks

       

      please let me know if you see a code issue

        • 1. Re: Running Datapump inside SQL worksheet....
          thatJeffSmith-Oracle

          what would you expect it to show?

           

          we can't test your code for you

          • 2. Re: Running Datapump inside SQL worksheet....
            goj

            Hi Jeff

            I'm expecting it to run the import at the very least.

            Getting this error now on line 66:

             

            EXCEPTION

                WHEN OTHERS THEN

                BEGIN

                    IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

                        DBMS_DATAPUMP.DETACH(h1);

                    END IF;

                EXCEPTION --------> line 66

                WHEN OTHERS THEN

                    NULL;

                END;

            • 3. Re: Running Datapump inside SQL worksheet....
              thatJeffSmith-Oracle

              Won't comment on the 'when others then null' bit, but have you confirmed this works in SQL*Plus?

               

              Also it's not clear exactly what your error is or looks like. Can you post a screenshot? Just hit the camera button on the post toolbar.

              • 4. Re: Running Datapump inside SQL worksheet....

                What does your question have to do with Sql Developer?

                 

                Unless you can connect it you should mark the thread ANSWERED and repost it in the Sql and Pl/Sql forum

                 

                goj wrote:

                 

                Hi Jeff

                I'm expecting it to run the import at the very least.

                Getting this error now on line 66:

                 

                EXCEPTION

                    WHEN OTHERS THEN

                    BEGIN

                        IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

                            DBMS_DATAPUMP.DETACH(h1);

                        END IF;

                    EXCEPTION --------> line 66

                    WHEN OTHERS THEN

                        NULL;

                    END;

                When you repost you should post the EXACT code you are using. The code you posted is NOT valid: You should also REMOVE the WHEN OTHERS or you can expect everyone to pound on you for INTENTIONALLY HIDING any exceptions that occur. Why do you NOT want to know if Oracle finds problems in your code? That makes NO sense.

                IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) THEN

                There are NO SPACES before/after the 'AND' so that is just garbage as far as Oracle is concerned.

                 

                Either you posted the wrong code or your code has syntax errors and won't compile or run anyway.

                 

                I suggest you fix your syntax errors before reposting in the Sql forum. If you have trouble finding them remove ALL unnecessary code to reduce the problem to the SIMPLEST possible example until you find the code causing the errors.

                1 person found this helpful
                • 5. Re: Running Datapump inside SQL worksheet....
                  goj

                  Thanks everyone. My apologies. NO intention to hide any code,...

                  Im marking this as closed.

                   

                  I think I may have found the issue, thanks to everyone's help here. I will test and post it when its completed.

                   

                  Again, My apologies and pls no offense intended to anyone.

                  • 6. Re: Running Datapump inside SQL worksheet....
                    Turloch O'Tierney-Oracle

                    Hi Goj,

                     

                    This looks like PL/SQL output from the Data Pump wizard in SQLDeveloper.

                    If the Data Pump Job starts you will receive no output from the script at the prompt,

                    (except anonymous block completed).

                    There may be output in:

                     

                    dbms_datapump.add_file(handle => h1, filename => 'IMPORTtest2.LOG', directory => 'DATA_PUMP_DIR', filetype => 3);

                     

                    Directory: DATA_PUMP_DIR File:IMPORTtest2.LOG

                     

                    The when others then null is "do not report secondary exception" (you are already reporting initial exception with the 'raise').

                     

                    In SQLDeveloper 4.1 Early adopter the Master table is by default kept which allows the Log file to be found and visible

                    from the UI whenever the Data Pump job is in 'Not Running' state..

                    SQLDeveloper Senior Member of Tech Staff blog: Data Pump Wizard enhancement

                     

                    I do see two issues:

                    1/ incomplete text there should be something after value =>

                        dbms_datapump.metadata_remap(handle => h1, name => 'REMAP_DATAFILE', old_value => UPPER('C:\ORACLE\ORADATA\ORCL\PFIZERCMS_DATA01.DBF') , value =>

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

                     

                    2/ Remap data file - non windows is case sensitive - so the UPPER on the new value is in REMAP_DATAFILE is probably a bug.

                     

                    Turloch

                    -SQLDeveloper Team

                     

                    Note:

                    There is a way of getting better feedback from an initial Data Pump exception

                    which is used in the Data Pump wizard.

                    (This does not apply in your testcase where there is no exception).

                    An additional error checking example is given in:

                    http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_api.htm#i1008408

                     

                    NOTE2 Whitespace between AND and bracket is nice but optional:

                    In SQLPlus:

                    SQL> set serveroutput on

                    SQL> begin

                      2  if ((1=1)AND(1=1)) THEN

                      3  dbms_output.put_line('if worked');

                      4  end if;

                      5  end;

                      6  /

                    if worked

                     

                    PL/SQL procedure successfully completed.