Skip to main content

PL/Scope 12.2: Find all commits and rollbacks in your code

Yes, another post on PL/Scope, that awesome code analysis feature of PL/SQL (first added in 11., and then given a major upgrade in 12.2 with the analysis of SQL statements in PL/SQL code)!

A question on StackOverflow included this comment:
But there can be scenarios where it is difficult to identify where the ROLLBACK statement are executed in a complex PL SQL program (if you have to do only a modification to the existing code).
As of 12.2, it is super-duper easy to find all commits and rollbacks in your code.

Find all commits:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'COMMIT'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Find all rollbacks:

SELECT st.object_name,
       st.object_type,
       st.line,
       src.text
  FROM all_statements st, all_source src
 WHERE     st.TYPE = 'ROLLBACK'
       AND st.object_name = src.name
       AND st.owner = src.owner
       AND st.line = src.line
ORDER BY st.object_name,
         st.object_type   
/

Reminder: these data dictionary views are populated only when your session or program unit has these settings enabled:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'

Comments

  1. ParasJune 15, 2017 at 2:45 AM

    Nice article Steven

    ReplyDelete
  2. AnonymousJuly 4, 2017 at 8:34 AM

    Hi Steve,
    is there a difference when I execute the query as simple SQL or inside a PL/SQL procedure?
    In the latter case it returns no rows for an object belonging to another schema for which I have been granted DEBUG and EXECUTE priveleges.
    Do I need some extra privileges in that case?
    It puzzles me...

    Thank you
    Flavio

    ReplyDelete
    Replies
    1. Steven FeuersteinJuly 4, 2017 at 2:08 PM

      The code in those other schemas must be compiled with PL/Scope enabled, otherwise there will be no data for them. Other than that, execute should do it.

      Delete
    2. AnonymousJuly 4, 2017 at 2:13 PM

      Thank you, I'll check it out tomorrow.
      Flavio

      Delete
  3. UnknownJuly 24, 2017 at 2:16 AM

    nice post

    ReplyDelete

Post a Comment

Popular posts from this blog

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL Brain Teaser: Find all the hard-codings!

We all know that hard-coding is a bad thing (well, maybe not all of us . At one training several years past, I asked the audience "Does anyone think hard-coding is a good idea?" and one person raised his hand. Um, OK). You know hard-coding: when you say to yourself "This is never going to change." and so you put the "value" directly in your code, over and over again. I put "value" in quotes, because many developers think simply of hard-coded literal values when they think of hard-coding. But I think there are many more ways that hard-coding can seep into our programs. So I invite you to help find all the hard-codings in the procedure below. Here's the rule: you can only identify ONE HARD-CODING in each comment. I will delete a submission with > 1. It'll be more fun that way. Promise! I will give everyone a couple of days to submit your ideas, then offer my own views on the subject.
67 comments
Read more