Skip to main content


Showing posts from August, 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, …