Oracle-使用dbms_utility.exec_ddl_statement的游标无法正确执行 [英] Oracle - cursor using dbms_utility.exec_ddl_statement not executing properly

查看：107
本文介绍了Oracle-使用dbms_utility.exec_ddl_statement的游标无法正确执行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have a requirement to run a SP across multiple DBs at the same time and one part of it is to eliminate some duplicate records from each DB. Now since the SP can be run multiple times i've included a backup table and and what's needed to truncate and drop it in case the SP is run twice in a row.

现在，由于我是通过DBLINK创建表的，所以我已经研究过需要使用dbms_utility.exec_ddl_statement-但在这种情况下，即使该过程执行了，截断和删除查询也无济于事，因为当我运行时SP再次失败，告诉我备份表的名称已被使用(即使我在CREATE之前包括了DROP执行).

Now, since i'm creating tables via DBLINK i've researched that i need to use dbms_utility.exec_ddl_statement - but in this case even though the procedure executes, the truncate and drop queries seem to do nothing, because when i run the SP the second time it fails telling me the name of the backup table is already in use (even though i've included the DROP execution before CREATE).

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.

The rest of the cursor which includes deletes, inserts, updates and creation of GLOBAL TEMP tables seems to work just fine and everything executes. If i manually delete the backup table even the CREATE that fails executes.

我很困惑:(

更新2016年8月12日

在@Jon Heller提供的帮助下，只要我为DB_LINK使用静态名称，我就能在下面转换我的代码.但是，当我尝试使用变量时，它会失败. 尝试了以下两个版本，但仍然无法运行，但是我不断对其进行修改-我在这里遗漏了什么吗?

With the help provided by @Jon Heller I was able to transform my code in the below which works as long as i use a static name for the DB_LINK. But when i try to use the variable it fails. Tried both of the below versions but stil lcan't get to run however i keep modifying them - am i missing something here?

注意:现在，我添加了alter session，因为没有它，由于ORA-04062，重新运行原始过程一直失败:过程"cw_drop_table"的时间戳已更改；

Note: Now, i added the alter session because without it, re-running the original procedure kept failing due to ORA-04062: timestamp of procedure "cw_drop_table" has been changed;

第一个版本

    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_

解决方案

经过深思熟虑和洗完澡后，我放弃了上面的方法，继续下面的方法.不知道为什么我不早考虑它:|

After much contemplation and a shower i abandoned the above methodology and went with the below. Not sure why i didn't think about it earlier :|

注意:如果有人读过这个冗长的问题并且知道我在2016年8月12日更新中做错了什么，我很想知道:)

Note: if anyone ever reads through this long-winded question and knows what i did wrong in the 08/12/2016 Update, i am curious to find out :)

    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块运行它，它将无提示地失败并且不运行任何程序.

DBMS_UTILITY.EXEC_DDL_STATEMENT only reliably runs DDL. If you try to run it with a PL/SQL block it will silently fail and not run anything.

这可以通过运行显然应该失败的PL/SQL块来证明. 下面的代码应生成ORA-01476: divisor is equal to zero.但是相反，它什么也没做.

This can be demonstrated by running a PL/SQL block that should obviously fail. The code below should generate ORA-01476: divisor is equal to zero. But instead it does nothing.

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调用它.

Use a temporary procedure to run a PL/SQL block remotely. Create the procedure with DBMS_UTILITY.EXEC_DDL_STATEMENT and then call it with native dynamic 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应该抛出错误，而不是简单地不做任何事情.

I think this behavior is a bug. Oracle should throw an error instead of simply not doing anything.

欢迎来到串联地狱.将字符串嵌入4层深度时，它们会变得凌乱.但是您可以做一些事情来使生活更轻松:

Welcome to concatenation hell. Strings get messy when they're embedded 4 levels deep. But there are a few things you can do to make life easier:

  1. 使用嵌套的替代报价机制.例如，q'[ ... ]'，q'< ... >'内的等等.
  2. 使用多行字符串.不需要连接多行，只需使用一个字符串即可.
  3. 使用额外的间距来帮助识别字符串的开头和结尾.当事情变得如此疯狂时，值得将一个字符串定界符本身全部放在一行上，以便所有内容都易于排列.
  4. 使用REPLACE代替串联.
  1. Use nested alternative-quoting mechanism. For example, q'[ ... ]', inside a q'< ... >', etc.
  2. Use multi-line strings. There's no need to concatenate multiple lines, just use a single string.
  3. Use extra spacing to help identify the start and end of strings. When things get this crazy it's worth putting a string delimiter on a line all by itself, so that everything is easy to line up.
  4. Use REPLACE instead of concatenation.

我使用这些提示重新格式化了部分代码. Stackoverflow无法理解替代的报价机制，但是在良好的Oracle SQL编辑器中，字符串应该看起来更好.

I re-formatted part of your code using those tips. Stackoverflow does not understand the alternative quoting mechanism, but the strings should look better in a good Oracle SQL editor.

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

这篇关于Oracle-使用dbms_utility.exec_ddl_statement的游标无法正确执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆