Oracle: Can I tell programmatically if a procedure contains a commit?

Question

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.

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;

Answer 1

You can find 99.99% of all (static, explicit, unwrapped) commits by combining DBA_DEPENDENCIES with some lexical analysis.

As you said, it's reasonable to exclude pathological cases like execute immediate 'c'||'o'||'m'||'m'||'i'||'t'. But a simple query like select * from dba_source where lower(text) like '%commit%'; can make mistakes for reasonable code. Some examples are comments, strings, and substrings of other words.

Tokenizing the code makes it easy to filter out COMMIT used in comments, strings, and as substrings. Looking ahead one token reduces mistakes even more because the COMMIT syntax requires the next "real" token to be one of WORK, COMMENT, FORCE, or ;. That eliminates almost all cases of using COMMIT as an identifier. Only a full parser could be 100% accurate but none of the existing PL/SQL parsers are up to the challenge. (Except for Oracle's parser, which they aren't sharing.)

The code below uses an open-source tokenizer that I created.

Install

create or replace type nvarchar2_table is table of nvarchar2(2 char);

create or replace type token is object
(
    type     varchar2(4000),
    value    nclob,
    --Although called "SQL" code and errm, these may also apply to PL/SQL.
    --They would not match the real PL/SQL error messages, but the information
    --should still be helpful to parse broken code.
    sqlcode  number,
    sqlerrm  varchar2(4000)
);

--Use VARRAY because it is guaranteed to maintain order.
create or replace type token_table is varray(2147483647) of token;

--Download file from https://raw.githubusercontent.com/jonheller1/plsql_lexer/master/tokenizer.plsql
@tokenizer.plsql

Custom Function

--Return 1 if a COMMIT; is found, else return a 0.
create or replace function has_commit(p_owner varchar2, p_name varchar2, p_type varchar2) return number is
    type varchar2_nt is table of varchar2(4000);
    v_text varchar2_nt := varchar2_nt();
    v_source nclob;
    v_tokens token_table;

    --Return the next concrete token's value, or NULL if there are no more.
    function get_next_concrete_value(p_token_index in number) return nvarchar2 is
    begin
        --Loop through the tokens.
        for i in p_token_index + 1 .. v_tokens.count loop
            --Return it if it's concrete.
            if v_tokens(i).type not in ('whitespace', 'comment', 'EOF') then
                return v_tokens(i).value;
            end if;
        end loop;

        --Return NULL if nothing was found.
        return null;
    end get_next_concrete_value;

begin
    --Get source.
    select text
    bulk collect into v_text
    from all_source
    where owner = p_owner
        and name = p_name
        and type = p_type;

    --Convert source into a single NCLOB.
    for i in 1 .. v_text.count loop
        v_source := v_source || v_text(i);
    end loop;

    --Tokenize the source.
    v_tokens := tokenizer.tokenize(v_source);

    --Look for a COMMIT token followed by either WORK, COMMENT, FORCE, or ;.
    --Return 1 (true) if it's found.
    --http://docs.oracle.com/database/121/SQLRF/statements_4011.htm#i2060233
    for v_token_index in 1 .. v_tokens.count loop
        if
        (
            upper(v_tokens(v_token_index).value) = 'COMMIT'
            and 
            upper(get_next_concrete_value(v_token_index))
                in ('WORK', 'COMMENT', 'FORCE', ';')
        ) then
            return 1;
        end if;
    end loop;

    --Not found, return 0.
    return 0;
end has_commit;
/

More Sample Procedures

In addition to P1, P2, and P3, below are some more unusual syntax examples.

--A valid, but unusual way to commit.
create or replace procedure p4 as
begin
    commit work comment 'commiting';
end;
/

--Difficult but reasonable code that does not contain a commit.
create or replace procedure p5 as
    --Good luck parsing this string with a regular expression.
    a_string varchar2(1000) := q'<'
        commit;
    >';

begin
    /* Don't count this!
        commit;
    */
    --Or this
    --commit;

    --Getting a bit ridiculous here...
    select 1 commit into a_string from dual;
end;
/

--Unreasonable code that will incorrectly reports as having a commit. 
create or replace procedure p6 as
    v_number number;
begin
    select 1
    into v_number
    from
    (
        --Naming a variable commit is legal but obviously a bad idea.
        select 1 commit
        from dual
    )
    --Uh-oh... "COMMIT" followed by ";" is bad news.
    where 1 = commit;
end;
/

Test the Procedures and Function

select object_name, has_commit(owner, object_name, object_type) has_commit
from dba_objects
where owner = user
    and object_type = 'PROCEDURE'
    and object_name like 'P_'
order by object_name;

OBJECT_NAME   HAS_COMMIT
-----------   ----------
P1                     1 --Correct
P2                     0 --Correct
P3                     0 --Correct
P4                     1 --Correct
P5                     0 --Correct
P6                     1 --WRONG

Object Hierarchy

Use a statement like this to create the object hierarchy.

--Object hierarchy.
--Creating temporary table for quick performance issues.
create table object_hierarchy as
select dba_objects.owner, dba_objects.object_name, dba_objects.object_type
    ,level object_level
    ,connect_by_root(dba_objects.owner) root_owner
    ,connect_by_root(dba_objects.object_name) root_object_name
    ,connect_by_root(dba_objects.object_type) root_object_type
from dba_objects
left join dba_dependencies
    on dba_objects.owner = dba_dependencies.owner
    and dba_objects.object_name = dba_dependencies.name
    and dba_objects.object_type = dba_dependencies.type
start with (dba_objects.owner, dba_objects.object_name, dba_objects.object_type) in 
(
    select owner, object_name, object_type
    from dba_objects
    where owner = user
        and object_type = 'PROCEDURE'
        and object_name like 'P_'
)
connect by dba_objects.owner = prior referenced_owner
    and dba_objects.object_name = prior referenced_name
    and dba_objects.object_type = prior referenced_type
order siblings by object_name;

Hierarchy with COMMIT status

--Object Hierarchy with HAS_COMMIT per row and per group.
select root_owner, root_object_name, root_object_type, owner, object_name, object_type, object_level,
    max(has_commit) over (partition by root_owner, root_object_name, root_object_type) has_any_commit,
    has_commit
from
(
    --Combine hierarchy with HAS_COMMIT.
    select root_owner, root_object_name, root_object_type, object_level
        ,object_hierarchy.owner, object_hierarchy.object_name, object_hierarchy.object_type
        ,has_commit
    from object_hierarchy
    join
    (
        --Add has_commit
        select owner, object_name, object_type
            --Standard is an exception because it defines the procedure "COMMIT;".
            ,case
                when owner = 'SYS' and object_name = 'STANDARD' then
                    0
                else
                 has_commit(owner, object_name, object_type)
            end has_commit
        from
        (
            select distinct owner, object_name, object_type
            from object_hierarchy
        )
    ) add_has_commit
        on object_hierarchy.owner = add_has_commit.owner
        and object_hierarchy.object_name = add_has_commit.object_name
        and object_hierarchy.object_type = add_has_commit.object_type
) hierarchy_has_commit
order by root_owner, root_object_name, root_object_type, object_level,
    owner, object_name, object_type;

Results Sample

The results are grouped by the root owner, name, and type. HAS_ANY_COMMIT indicates whether any procedure in the family has a COMMIT, and HAS_COMMIT indicates whether that specific object has a COMMIT.

ROOT_OWNER  ROOT_OBJECT_NAME  ROOT_OBJECT_TYPE  OWNER    OBJECT_NAME   OBJECT_TYPE   OBJECT_LEVEL   HAS_ANY_COMMIT   HAS_COMMIT
JHELLER     P1                PROCEDURE         JHELLER  P1            PROCEDURE     1              1                1
JHELLER     P2                PROCEDURE         JHELLER  P2            PROCEDURE     1              0                0
JHELLER     P3                PROCEDURE         JHELLER  P3            PROCEDURE     1              1                0
JHELLER     P3                PROCEDURE         JHELLER  P1            PROCEDURE     2              1                1

Answer 2

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

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.

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;