Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Running Datapump inside SQL worksheet....

gojMar 6 2015 — edited Mar 9 2015

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
This post has been answered by unknown-7404 on Mar 8 2015
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 6 2015
Added on Mar 6 2015
#sql-developer
6 comments
1,117 views