现在,因为我正在通过DBLINK创建表我已经研究过我需要使用dbms_utility.exec_ddl_statement – 但是在这种情况下即使执行过程,截断和删除查询似乎什么都不做,因为当我运行SP时第二次它没有告诉我备份表的名称已被使用(即使我在CREATE之前包含了DROP执行).

loop
    fetch v_data into v_database_name;
    exit when v_data%NOTFOUND;
    sql_update := 'BEGIN'
                ||'    EXECUTE IMMEDIATE ''truncate table iSecurity2_dupes_bak'';'
                ||' EXCEPTION'
                ||'    WHEN OTHERS THEN'
                ||'       IF sqlCODE != -942 THEN'
                ||'          RAISE;'
                ||'       END IF;'
                ||' END;';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
    commit;
    sql_update := 'BEGIN'
                ||'    EXECUTE IMMEDIATE ''DROP TABLE iSecurity2_dupes_bak'';'
                ||' EXCEPTION'
                ||'    WHEN OTHERS THEN'
                ||'       IF sqlCODE != -942 THEN'
                ||'          RAISE;'
                ||'       END IF;'
                ||' END;';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
    commit;
    sql_update := 'create table iSecurity2_dupes_bak as select * from iSecurity2';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
commit;
.................

ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.DBMS_UTILITY",line 478
ORA-06512: at line 1
ORA-06512: at "database.procedure_name",line 53
ORA-06512: at line 2

包括删除,插入,更新和创建GLOBAL TEMP表的游标的其余部分似乎工作得很好并且一切都在执行.如果我手动删除备份表,即使执行失败的CREATE也是如此.

我很困惑:(

更新08/12/2016

在@Jon Heller提供的帮助下,我能够转换下面的代码,只要我使用DB_LINK的静态名称就行.但是,当我尝试使用变量时,它失败了.
尝试了以下两个版本,但stil无法运行,但我一直在修改它们 – 我在这里错过了什么？

注意：现在,我添加了alter session,因为没有它,重新运行原始过程因ORA-04062而失败：过程“cw_drop_table”的时间戳已被更改;

第一版

loop
    fetch v_data into v_database_name;
    exit when v_data%NOTFOUND;
    sql_update := 'alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
    commit;
        begin 
                       dbms_utility.exec_ddl_statement@v_database_name (
                            q'[ 
                            create or replace procedure cw_drop_table is sql_drop varchar2(2000);
                                begin 
                                    sql_drop := 'BEGIN' 
                                        ||'    EXECUTE IMMEDIATE ''DROP TABLE iSecurity2_dupes_bak'';' 
                                        ||' EXCEPTION'
                                        ||'    WHEN OTHERS THEN IF sqlCODE != -942 THEN NULL; END IF; END;';
                                    execute immediate sql_drop;
                                    commit;
                                end; ]' );      
                            execute immediate 'begin cw_drop_table@'||v_database_name||'; end;'; 
        end;
    sql_update := 'create table iSecurity2_dupes_bak as select * from iSecurity2';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
    commit;

PLS-00352: Unable to access another database 'V_DATABASE_NAME'
PLS-00201: identifier 'DBMS_UTILITY@V_DATABASE_NAME' must be declared
PL/sql: Statement ignored

第二版

loop
        fetch v_data into v_database_name;
        exit when v_data%NOTFOUND;
        sql_update := 'alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE';
        execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
        commit;
        declare v_db_name varchar2(100);
            begin   select v_database_name into v_db_name from dual;                    
                execute immediate   'dbms_utility.exec_ddl_statement@'||v_db_name||' ('
                            ||' q''[ '
                            ||' create or replace procedure cw_drop_table is sql_drop varchar2(2000);'
                            ||'     begin ' 
                            ||'         sql_drop := ''BEGIN'' '
                            ||'             ||''       EXECUTE IMMEDIATE ''DROP TABLE iSecurity2_dupes_bak'';'' '
                            ||'             ||''    EXCEPTION'' '
                            ||'             ||''       WHEN OTHERS THEN IF sqlCODE != -942 THEN NULL; END IF; END;''; '
                            ||'         execute immediate sql_drop;'
                            ||'         commit;'
                            ||'     end; ]'' ); '   
                            ||' execute immediate ''begin cw_drop_table@'||v_db_name||'; end;''; ';
            end;
        sql_update := 'create table iSecurity2_dupes_bak as select * from iSecurity2';
        execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
    commit;

PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || member SUBMULTISET_

解

经过多次沉思和淋浴后,我放弃了上述方法,并采用了以下方法.不知道为什么我之前没想过：|

注意：如果有人读过这个冗长的问题并知道我在08/12/2016更新中做错了什么,我很想知道:)

loop
        fetch v_data into v_database_name;
        exit when v_data%NOTFOUND;
        sql_update := 'alter session set REMOTE_DEPENDENCIES_MODE=SIGNATURE';
        execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;
        commit;
        begin       
            sql_update:='DROP TABLE iSecurity2_dupes_bak';
            execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_database_name||'(:sql_update);  end;' using sql_update;

            EXCEPTION
                WHEN OTHERS THEN
                  IF sqlCODE = -942 THEN
                    NULL; -- suppresses ORA-00942 exception
                  ELSE
                     RAISE;
                  END IF;
        END;