Obsessed with Oracle PL/SQL

I received this question today: I f I wanted to write a tech book, where/how would I start? Rather than provide an individual answer, I thought I'd answer on my blog. Here goes. First, how I answer this question for myself (the variation being: "Do you want to write another book?"): No, don't do it. :-) I decided a few years ago that I would not write new books and instead keep my core set of books on PL/SQL up to date (for anyone who's wondering, that means essentially 3 out my 10 books on PL/SQL ). It takes a lot of time to write a book, any sort of book. And certainly with a technical book you need to be concerned about technical accuracy (slightly less critical with fiction :-) ). In addition, people aren't buying books like they used to. Gee, thanks, Google (and people publishing ripped-off e-copies of books, and all the free content published on blogs and...). So you definitely should not go into such a project thinking you are going

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

When a SQL statement executes a user-defined function, your users pay the price of a context switch , which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues. Fortunately, you can use PL/Scope in  Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there. I go through the steps below. You can run and download all the code on LiveSQL . First, I turn on the gathering of PL/Scope data in my session: ALTER SESSION SET plscope_settings='identifiers:all, statements:all' / Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope: CREATE TABLE my_data (n NUMBER) / CREATE OR REPLACE FUNCTION my_function1 RETURN NUMBER AUTHID DEFINER IS BEGIN RETURN 1; END; / CREATE OR REPLACE FUNCTION my_function2