Posts

Showing posts from June, 2017

So you want to write a technical book?

Image
I received this question today:
If 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 to make much, if any…

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…

More 12.2 PL/Scope Magic: Find SQL statements that call user-defined functions

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 RETURN NUMBER…