Search

Top 60 Oracle Blogs

Recent comments

PL/SQL

OTN Appreciation Day – Instrument Your Damned Code. Please!

Today is OTN Appreciation Day.

This day is the idea of Tim Hall, Mr OracleBase, and you can See his post here. The idea is that as a sign of appreciation to OTN we do a technical (or not so technical) post on a feature of Oracle we like. I’m going to visit an area I have mentioned before…

DBMS_APPLICATION_INFO.

A very gentle introduction to Oracle PL/SQL programming for beginners

PL/SQL offers the entire suite of structured programming mechanisms, such as condition checking, loops, and subroutines, as shown in the following figure. (read more)

The Book.

I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.

The British part of me wants to mumble and say “oh, yes, hmmm, I did contribute to a book… but I think you should concentrate on the chapters by the other chaps, they are proper experts, very clever gentleman and lady… I was just involved in a couple of lesser chapters…”

The part of me that spent weeks and months of late nights and long weekends writing it wants to scream “Look! LOOK! I damn well got it done! And it was way more painful than any of my author friends told me it would be as, despite their best efforts, I did not get How Hard Writing A Book Is!
I BLED FOR THAT BOOK!”

Combining Features - Wrong Results With Scalar Subquery Caching

Quite often you can get into trouble with Oracle when you start combining different features.In this case of one my clients it is the combination of user-defined PL/SQL functions that can raise exceptions (think of currency conversion and a non-existent currency code gets passed into the function), DML error logging and attempting to improve performance by wrapping the PL/SQL function call into a scalar subquery to benefit from the built-in scalar subquery caching feature of the SQL runtime engine.As long as the scalar subquery didn't get used everything worked as expected, but after adding the scalar subquery after some while it became obvious that wrong results occurred - in that particular case here it meant rows that should have been rejected and written to the error logging table due to the exception raised in the user-defined PL/SQL function suddenly showed up in the target table, and what was even more worrying - they included a co

TABLE Operator with Locally Defined Types

postit-missed-itIn my OUG Ireland 2016 – Summary post I mentioned the Oren Nakdimon session called “Write Less (Code) with More (Oracle 12c New Features)”. One of the things he mentioned was the removal of restrictions associated with the use of the TABLE operator on local table types. If I had read about this or seen it before, it had certainly slipped my mind, so I made a note to write something about it and add a link to it from my PL/SQL new features article. So here it is.

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – Take Two

In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love examples!) based on the extension_invalidate_pkg_remote.sql script, what can happen when the table on which the extension is created is stored in a remote database.

Let’s start by creating the required objects. Notice that all objects are local but, because the package body references the table through a database link that points to the very same schema owning all other objects, the database engine considers it a remote table.

Getting Your Transaction SCN – USERENV(COMMITSCN)

A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.

I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it so I asked Jonathan and he pointed me straight to this post he did about it a while back. What a nice chap. However, the post is from 1999 (last CENTURY!) so I thought I should just check it out first…

PL/SQL Formatting : More pearls of wisdom from Bryn

glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

Preventing PL/SQL name clashes. You learn something new every day!

glasses-272399_1280-smallI mentioned in yesterday’s Oracle Midlands post, Bryn had an example of some syntax I had not seen in 20+ years of PL/SQL development.

You tend to name PL/SQL parameters and variables in such a way as to prevent name clashes with table columns. Let’s cause an obvious name clash…

The table DUAL has a column called DUMMY with a single row with the value ‘X’.