PL/Scope Support

I forgot to blog this! The PL/SQL team is always reminding me to talk about PL/Scope. And I got to the point where I needed to remind myself, so I added this slide in all of my PL/SQL themed talks. Yet, I have apparently forgotten to blog about it. Oops. [Docs] PL/Scope is a compiler-driven tool that collects data... [Read More]

Debugging PL/SQL Collections Revisited

I talked about using the debugger to inspect a collection in PL/SQL using SQL Developer a few years ago. It’s been so long in fact that when Steven asked if I had a post on it, I said ‘no.’ We had gotten a question from a reader on how to look at collections while you’re debugging them. If you... [Read More]

Object Oriented Pl/Sql

Years ago, in my Oracle years I wrote an article on Oracle (Object) Types, and how those make Pl/Sql so much more powerfull. It was in Dutch, since I wrote it for our monthly internal consulting magazine called 'Snapshot'. Since it was in Dutch and I regularly refer to it on our blog or in questions on forums, I wanted to rewrite it for years. So let's go. Oracle Types are introduced in the Oracle (more...)

Oracle PL/SQL Programming 6th edition by Steven Feuerstein

opp6_catI have reviewed the latest edition of Oracle PL/SQL Programming. This is not a book, at least for me, that I would read front to back. I use it mostly as a reference, but the great thing about this book is that you can also use this book to learn programming PL/SQL from scratch.

The book is nicely ordered into different parts, like Program structure and how to use SQL in PL/SQL (which by the (more...)

Use the database to do the heavy lifting

Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these (more...)

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 (more...)

Synchronous Dynamic Actions in APEX 5.1

If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.

Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.

Consider this scenario of dynamic actions on change of P42_ITEM:

Synchronous vs Asynchronous server calls
First JavaScript takes value of P42_ITEM, concatenates a letter and places (more...)

AMIS25 Beyond the Horizon Conference – Session Resource Repository

amis25bthBelow you will find an overview of all the sessions that took place at the AMIS25 Beyond the Horizon conference (1-3 June 2016, Katwijk, The Netherlands) along with the associated session resources – such as the presentation slides and where applicable the source code.


Presenter(s) Title Session Resources
Aino Andriessen

Aino Andriessen

Deploy with joy: automate the build and deployment of your ADF Fusion application
Alan Arentsen

Ultimate Node.js countdown the coolest APEX examples
Alex (more...)

Top N- queries: using the 12c syntax.

One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".

For the examples I will use (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Debug PL/SQL Web Pages

What happens when you can’t get a PL/SQL Web Toolkit to work because it only prints to a web page? That’s more tedious because any dbms_output.put_line command you embed only prints to a SQL*Plus session. The answer is quite simple, you create a test case and test it inside a SQL*Plus environment.

Here’s a sample web page that fails to run successfully …


Subtleties – Part 2 (Nested Tables and Varrays)

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL.
One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option).
This works fine, in both SQL and PL/SQL, as long as the collection type that we use is Nested Table.

create type integer_ntt as table of integer

select person_id,set(cast(collect(project_id) as integer_ntt)) project_id_list

Subtleties – Part 1 (SQL and PL/SQL)

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement.
One example is the COLLECT aggregate function with the DISTINCT option.

To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. The same person may be assigned to the same project more than once, in different times.

create table  (more...)

Improving PL/SQL performance in APEX

One of the simplest tuning techniques to encapsulate PL/SQL used in APEX within packages, minimising the size of anonymous blocks. This applies to any PL/SQL within the page, including computations, processes, plugins, dynamic actions, validations, shortcuts and dynamic PL/SQL regions.

This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of interpreted at runtime.

Plug-ins can be wonderful black boxes and consumers may not care (more...)

The importance of cohesion

"Come on, come on, let's stick together" - Bryan Ferry

There's more to PL/SQL programs than packages, but most of our code will live in packages. The PL/SQL Reference offers the following benefits of organising our code into packages:

Modularity - we encapsulate logically related components into an easy to understand structure.

Easier Application Design - we can start with the interface in the package specification and code the implementation later.

Hidden Implementation (more...)

Email made Easier

an e-mail letter that has a @ sign on itSending emails from the Oracle database can be both simply deceptively braindead easy, and confoundingly perplexingly awful at the same time. Easy, because all you have to do is call one of the supplied mail packages to send an email:

  (sender     => ''
  ,recipients => ''
  ,subject    => 'Test Subject'
  ,message    => 'Test Message');
  (p_from => ''
  ,p_to   => ''
  ,p_subj => 'Test Subject'
  ,p_body => 'Test Message'

If (more...)

STRING_SPLIT im SQL Server 2016

Vor einiger Zeit habe ich in der Sektion database ideas bei OTN folgenden Wunsch geäußert: a string splitting function like SPLIT_PART in postgres. SPLIT_PART erhält als Argumente einen String und einen Delimiter, zerlegt den String an den Positionen der Delimiter-Zeichen in Substrings und liefert den n-ten Teilstring:


Das ist sicherlich keine höhere Magie und kann in SQL auf verschiedenen Wegen erreicht werden (etwa durch den Einsatz regulärer Ausdrücke), (more...)

Gear up for #AIOUG OTN Yathra’ 2016

Guys, AIOUG is back again with OTN Yathra’ 2016. It is a series of technology evangelist events organized by All India Oracle Users Group in six cities touring across the length and breadth of the country. It was my extreme pleasure to be the part of it in 2015 and I’m pleased to announce that … Continue reading

Three more principles

Here are some more principles which can help us design better programs. These principles aren't part of an organized theory, and they're aren't particularly related to any programming paradigm. But each is part of the canon, and each is about the relationship between a program's interface and its implementation.

The Principle Of Least Astonishment

Also known as the Principle of Least Surprise, the rule is simple: programs should do what we expect them to (more...)

It’s all about the interface

When we talk about program design we're mainly talking about interface design. The interface is the part of our program that the users interact with. Normally discussion of UI focuses on GUI or UX, that is, the interface with the end user of our application.

But developers are users too.

Another developer writing a program which calls a routine in my program is a user of my code (and, I must remember, six months after (more...)