Oracle:我可以以编程方式告知过程是否包含提交吗? [英] Oracle: Can I tell programmatically if a procedure contains a commit?

问题描述

如何以编程方式告诉存储过程是否具有提交?在理想情况下，如果一个过程调用另一个执行提交的过程，则可以通过传递方式检测到这一点.

How can I tell programmatically if a stored procedure has a commit? In an ideal world this could be detected transitively if one procedure calls another that executes a commit.

我不需要知道是否将执行提交(因为这可能在某些条件逻辑中)，只需调用该提交即可.

I don't need to know if the commit will be executed (since that might be in some conditional logic), just that commit is invoked.

例如，在此代码段中，我想知道p1调用commit，p2从不调用commit，p3调用通过p1提交.

For example, in this snippet I would like to know that p1 calls commit, p2 never calls commit, and p3 calls commit via p1.

create or replace procedure p1 as
begin
    insert into foo values(99);
    commit;
end;

create or replace procedure p2 as
begin
    insert into foo values(99);
end;

create or replace procedure p3 as
begin
    p1;
end;

推荐答案

我有为此编写的打包过程.我将在下面粘贴代码.

I have a package procedure I wrote for this. I'll paste the code below.

要使用它，只需使用您提供的名称调用"start_no_commit_section".然后，稍后用相同的名称调用"end_no_commit_section".如果已发出提交(或回滚)，则对"end_no_commit_section"的调用将引发错误.

To use it, just call "start_no_commit_section" with a name you give. Then, later, call "end_no_commit_section" with the same name. If a commit (or rollback) has been issued, the call to "end_no_commit_section" will raise an error.

不幸的是，这并不能告诉您提交的位置.如果我有很多代码需要浏览，通常会在代码上运行DBMS_HPROF，然后在HPROF结果中查找落实(这将告诉我确切的行号).

Unfortunately, this doesn't tell you where the commit happened. If I have a lot of code to look through, I'll generally run DBMS_HPROF on my code and then look for a commit in the HPROF results (which will tell me the exact line number).

  CREATE OR REPLACE PACKAGE BODY XXCUST_TRANSACTION_UTIL AS
  ----------------------------------------------------------------
  -- See package spec for comments
  ----------------------------------------------------------------
  TYPE no_commit_section_t IS RECORD (local_transaction_id VARCHAR2 (200));

  TYPE no_commit_sections_tab IS TABLE OF no_commit_section_t
                                   INDEX BY VARCHAR2 (80);

  g_no_commit_sections   no_commit_sections_tab;

  PROCEDURE start_no_commit_section (p_section_name VARCHAR2) IS
    l_section   no_commit_section_t;
  BEGIN
    l_section.local_transaction_id                          := DBMS_TRANSACTION.local_transaction_id (create_transaction => TRUE);
    g_no_commit_sections (SUBSTR (p_section_name, 1, 80))   := l_section;
  END start_no_commit_section;


  PROCEDURE end_no_commit_section (p_section_name VARCHAR2) IS
    l_local_transaction_id   VARCHAR2 (200);
  BEGIN
    l_local_transaction_id   := DBMS_TRANSACTION.local_transaction_id (create_transaction => TRUE);

    IF l_local_transaction_id != g_no_commit_sections (SUBSTR (p_section_name, 1, 80)).local_transaction_id THEN
      -- There has been a commit or a rollback in the no-commit section
      raise_application_error(-20001,'A commit or rollback has been detected in "No commit" section ' || p_section_name || '.');
    END IF;
  EXCEPTION
    WHEN no_data_found THEN
      -- Caller specified a non-existent commit section
      raise_application_error(-20001,'"No commit" section ' || p_section_name || ' not established.');
  END end_no_commit_section;
END XXCUST_TRANSACTION_UTIL;

这篇关于Oracle:我可以以编程方式告知过程是否包含提交吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！