Oracle – 使用dbms_utility.exec_ddl_statement未正确执行的游

发布时间：2020-12-12 13:49:46 所属栏目：百科来源：网络整理

导读：我需要同时在多个DB上运行SP,其中一部分是从每个DB中删除一些重复记录.现在,由于SP可以多次运行,我已经包含了一个备份表,以及在SP连续运行两次的情况下截断和删除它所需的内容. 现在,因为我正在通过DBLINK创建表我已经研究过我需要使用dbms_utility.exec_ddl

我需要同时在多个DB上运行SP,其中一部分是从每个DB中删除一些重复记录.现在,由于SP可以多次运行,我已经包含了一个备份表,以及在SP连续运行两次的情况下截断和删除它所需的内容.

现在,因为我正在通过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;

DBMS_UTILITY.EXEC_DDL_STATEMENT仅可靠地运行DDL.如果您尝试使用PL / SQL块运行它,它将无声地失败并且不会运行任何操作.

这可以通过运行显然会失败的PL / SQL块来证明.下面的代码应生成ORA-01476：除数等于零.但它没有做任何事情.

begin
    dbms_utility.exec_ddl_statement@myself(
        q'[declare v_test number; begin v_test := 1/0; end;]'
    );
end;
/

使用临时过程远程运行PL / SQL块.使用DBMS_UTILITY.EXEC_DDL_STATEMENT创建过程,然后使用本机动态SQL调用它.

begin
    dbms_utility.exec_ddl_statement@myself(
        q'[
            create or replace procedure test_procedure
            is
                v_test number;
            begin
                v_test := 1/0;
            end;
        ]'
    );
    execute immediate 'begin test_procedure@myself; end;';
end;
/

RESULTS:

ORA-01476: divisor is equal to zero
ORA-06512: at "JHELLER.TEST_PROCEDURE",line 5
ORA-06512: at line 1
ORA-06512: at line 12

我认为这种行为是一个错误. Oracle应该抛出错误而不是简单地做任何事情.

欢迎连接地狱.当它们嵌入4级深度时,字符串变得混乱.但是,您可以采取一些措施来简化生活：

>使用嵌套的替代引用机制.例如,q'[…]’,在q’和…之内. …>’等
>使用多行字符串.没有必要连接多行,只需使用一个字符串.
>使用额外的间距来帮助识别字符串的开头和结尾.当事情变得疯狂时,值得将一个字符串分隔符单独放在一行上,这样一切都很容易排列.
>使用REPLACE而不是连接.

我使用这些提示重新格式化了部分代码. Stackoverflow不理解备用引用机制,但在一个好的Oracle SQL编辑器中字符串应该看起来更好.

declare
    v_db_name varchar2(30) := 'myself';
    sql_update varchar2(32767);
begin
    execute immediate replace(
    q'[
        begin
            dbms_utility.exec_ddl_statement@#DB_NAME#
            (
                q'<
                    create or replace procedure cw_drop_table is
                        sql_drop varchar2(2000);
                    begin
                        sql_drop :=
                        q'{
                            BEGIN
                                EXECUTE IMMEDIATE 'DROP TABLE iSecurity2_dupes_bak';
                            EXCEPTION WHEN OTHERS THEN
                                IF SQLCODE != -942 THEN
                                    NULL;
                                END IF;
                            END;
                        }';
                        execute immediate sql_drop;
                    end;
                >'
            );
            execute immediate 'begin cw_drop_table@#DB_NAME#; end;';
        end;
    ]','#DB_NAME#',v_db_name);

    sql_update := 'create table iSecurity2_dupes_bak as select * from iSecurity2';
    execute immediate 'begin dbms_utility.exec_ddl_statement@'||v_db_name||
        '(:sql_update);  end;' using sql_update;
    commit;
end;
/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!