Skip to main content


Showing posts from 2019

PL/SQL 101: Introduction to Object Types, a.k.a., Classes

PL/SQL is a procedural language - mostly. But it can also be used to implement object-oriented features in Oracle Database. In "from the ground up" object-oriented languages like Java, everything is defined in classes. In Oracle Database, we have object types.

In this post, I introduce you to the basics of object types, and kick off a series of posts exploring many of the features of these programming elements.

You do not need to be an expert in object orientation to understand the basics of object types. I know this for a fact, because have no formal training in object orientation and was able to sort out at least the basics.

Before proceeding, though, you might wonder when you would want to use object types. Use cases include:
You'd like to apply object-oriented principles in your PL/SQL-based development. This makes sense if you are an experienced O-O developer and new to PL/SQL.You want to use the many features of Oracle Database that rely on object types for their i…

Feedback requested on upcoming changes to Dev Gym UI

Hello there, Dev Gym members!

We plan to release a new version of the Dev Gym (19.3 - we are aligning with the overall version schema followed by Oracle Database and sister sites, like Oracle LiveSQL).

Mainly, it is a tweak of the UI, to bring our site in line with and also to make better use of our "real estate." Before we apply the changes, though, we'd love to get some feedback from our users.

So when you have a moment or two, please scroll down the page, compare current and 19.3 pages, then let us know what you think.

Thanks very much,
Home PageCurrent Site
A few things to point out:
Orange themeLarge banner with search barDev Gym 19.3 Home Page
Things to note:
Switch to black-grey-red theme (just a little bit of red)Much smaller bannerSearch bar moved to top of site, independent of pageCleaner display of quizzes (we'd like to think) Moving on, in the workouts and classes pages, we've reduced the real estate for the banner (no need …

Change in ALL_ARGUMENTS as of 18c: no more composite "explosion"

The Oracle catalog contains hundreds of views (generally referred to as "data dictionary views") that provide information about the objects stored in the database (tables, views, PL/SQL program units, etc.). Several of these views are extremely helpful to PL/SQL developers in analyzing and managing their code.

Here are a few examples:
ALL_OBJECTS - information about all database objects which can be accessed by the current user.ALL_ARGUMENTS - information about every argument of every packaged subprogram and schema-level program unit for which the current user has EXECUTE authority.ALL_IDENTIFIERS - information about identifiers in program units, gathered by the Oracle Database 11g PL/Scope feature. In this blog post, I explore a change in behavior for ALL_ARGUMENTS (and its USER* and DBA* variants) as of Oracle Database 18c.

ALL_ARGUMENTS lists the arguments of the procedures and functions that are accessible to the current user. USER_ARGUMENTS, as you might know or guess, …

Use named notation for crystal clear code

The first and most important criteria of high quality code is that it meets user requirements (it's "correct").

The second most important criteria is that it fast enough to meet user needs. It doesn't matter that you might be able to make it go faster.

The third most important criteria of excellent code is that it is maintainable. Why, you might be wondering, is maintainabiliity so important?

Most of the code we write, especially code that lives in the database, sticks around for a long time. Well past the point when you were familiar with the code you wrote. And likely past the time when you are still working on that same code or project.

So the overall cost of development of that code is not just the initial build expense, but also must include the cost of maintaining that code. All too often we are so absorbed in meeting deadlines that we sacrifice clarity and readability to "get the job done."

And then we - or others - pay the price later. Conversely, …

Some Curiosities of Oracle Error Codes

A blog post about Oracle error codes? Curiosities about them, even?

If you doubt that this might be possible or of interest, then answer these two questions:

Can an error in Oracle have more than one error code? Are error codes positive or negative?
If you answered "yes" for the first and "yes and no" for the second, you probably don't need to read this post.
Oracle errors with more than one error code? Well that wouldn't be very normalized, would it? :-)

But it is true that there at least one error that has two different error codes associated with it, and it's one of the most common "errors" you'll encounter in your code: 

The NO_DATA_FOUND exception

When I execute a SELECT-INTO statement, Oracle will raise NO_DATA_FOUND if no row is found for the query. It will raise TOO_MANY_ROWS if more than one row is found.

So what error code is associated with NO_DATA_FOUND?

The following code demonstrates this curiosity. I create a table with …

When Lazy is Good: Overloading and APIs

When more than one subprogram (procedure or function) in the same scope share the same name, the subprograms are said to be overloaded. PL/SQL supports the overloading of procedures and functions in the declaration section of a block (named or anonymous), package specifications and bodies, and object type definitions. Overloading is a very powerful feature, and you should exploit it fully to improve the usability of your software.

Before exploring some of the details of overloading, a short quiz:

Which of the following is another name for overloading?
Dynamic PolymorphismInterface InheritanceStatic PolymorphismMultiple Monomorphism In a poll I conducted on Twitter, we saw the following results:
And I was very glad to see this, because Static Polymorphism is, indeed, another name for overloading, and here's why:

With overloading, at the time your code is compiled, PL/SQL resolves all references to named elements, such as a function invocation. If there is more than one subprogram w…

Best Type of Collection for FORALL?

I recently received this question in my In Box:

Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation says: "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post claims Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick AnswerThere is no significant difference I can see in the performance based on different collection types (with the limited tests I have run).Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2).Use the documentation as a starting, not ending, point of your exploration.Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read further, I will show you …

Make the Most of PL/SQL Bulk Processing

The bulk processing features of PL/SQL (BULK COLLECT and FORALL) are key tools for improving performance of programs that currently rely on row-by-row processing, an example of which is shown below.

Use this blog post to quickly get to some of the best resources on bulk processing - from articles to quizzes to workouts to tutorials.
LiveSQL Tutorial I offer a 19-module tutorial on all things bulk processing here. I complement the explanations with lots of code to run and explore, along with:
Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that moduleExercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up). Oracle-BASE Content You can always depend on Tim Hall to offer comprehensive coverage of SQL and PL/SQL features, with straightforward, easy-to-run code snippets to drive the points home. You'll find his coverage of bulk processing her…

How to make sure your code FAILS to compile

Huh, what?

Make sure my code fails to compile?

Why would I want to do that.

Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does).

Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems.

So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine?

I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL.

First, here's the code that demonstrates precisely the scenario outlined above.