Skip to main content

Posts

Showing posts from 2019

Writing code to support multiple versions of Oracle Database

3rd in a series on conditional compilation. See end of post for links to all posts in the series.

Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options:

Use only those features available in all versions ("lowest common denominator" or LCD programming).
or Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming).

And let's face it, both have some serious drawbacks.

The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay.

The SCAM approach, well, "sure to create a mess" says it all. Wh…

Nine Years at the Oracle Dev Gym

Waaaaay back in 2010, on April 8 to be specific, I started a website called the PL/SQL Challenge. It featured a daily PL/SQL quiz (yes, that's right - a new quiz every weekday!) and gave Oracle Database developers a way to both deepen and demonstrate their expertise. Players were ranked and competed for top honors in our annual championships.

Not quite as waaaaay back, in 2014, I rejoined Oracle Corporation after 22 years away (from the company, not from the technology). The PL/SQL Challenge came with me, and a year later we rebranded it as the Oracle Dev Gym.

Today, we offer quizzes on SQL, PL/SQL, database design, logic, Java and Application Express. We've added workouts and classes.

Yesterday we celebrated the ninth anniversary of the Dev Gym / PL/SQL Challenge. And my oh my but Oracle Database developers have been busy!


Here are some stats from those nine years: Almost 35,000 developers and DBAs have taken quizzes on the site, a total of 1.27M answers submitted.They spent a…

Viewing conditionally compiled code: what will be run?

2nd in a series on conditional compilation. See end of post for links to all posts in the series.

In the previous (first) post in my series on conditional compilation, I covered use cases and presented some simple examples.

In this post, I show you how you can confirm what code is actually going to be executed after compilation. Without conditional compilation, this is of course a silly exercise. The code that is executed is the same as the code you see in your editor.

But with conditional compilation, the code that is compiled and therefore runs could depend on any of the following:
The version of the database in which it is compiledThe values of user-defined conditional compilation flagsThe values of pre-defined (system) conditional compilation flags, like ##plsq1_optimize_level It can be a little bit nerve-wracking for a developer to not be entirely sure what is going to execute, so we provide the DBMS_PREPROCESSOR package, with its two subprograms: print_post_processed_source - disp…

One exception handler for all packaged subprograms?

This question was submitted as a comment in one of my videos today:
Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms? The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that subprogram.

I can certainly see why this question would come up. A package body can have its own exception handler. Here's an example:
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE proc IS BEGIN RAISE NO_DATA_FOUND; END; BEGIN NULL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Was proc executed?'); END; / And it kinda, sorta looks like if I execute the following block, I will see "Was proc executed?" on my screen.
BEGIN pkg.proc; END; / But I would be wrong. Instead, I will see:
ORA-01403: no data found ORA-0651…

European Union Mandates All Business Logic in Database by 2020

DatelineDB: April 1st 2019

The European Union turned heads today with a surprise announcement:
Starting 1 January 2020, all business logic in applications must be made available via code stored inside the database. While we recommend that you use Oracle Database and PL/SQL, that will not be required. This position was apparently taken after close review of the groundbreaking research conducted by Toon Koppelaars of Oracle Corporation, in which he showed that by putting business logic in the database, the overall work - and therefore energy consumption - of the application is reduced, sometimes by as much as 235%. While improving the overall performance of the application by 500%.

A close confidant of the President of the European Union told DatelineDB that the EU would soon adopt a resolution stating that we are now in a climate emergency and every effort must be made in every aspect of human activity to slow down the warming of our planet.

"So the decision to require business lo…

An introduction to conditional compilation

1st in a series on conditional compilation. See end of post for links to all posts in the series.

Conditional compilation allows the compiler to compile selected parts of a program based on conditions you specify using $ syntax in PL/SQL. When you see statements like $IF, $ELSE, $END and $ERROR in your PL/SQL code, you are looking at conditional compilations, sometimes also referred to as "ifdef" processing.

There's a really good chance you've never taken advantage of conditional compilation in PL/SQL, so I thought I'd write up a few blog posts about why you might want to use it - and then how to put it to use.

Conditional compilation comes in very handy when you need to do any of the following:
Compile and run your PL/SQL code base on different versions of Oracle, taking advantage of features specific to those versions. Run certain code during testing and debugging, but then omit that code from the production code. Or vice versa. Install/compile different elements…

Results of the Oracle Dev Gym PL/SQL Challenge Championship for 2018

You will find below the rankings for the PL/SQL Challenge Championship for quizzes taken in 2018. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Congratulations first and foremost to our top-ranked players:

1st Place: mentzel.iudith
2nd Place: Andrey Zaytsev
3rd Place: Tony Winn


Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these players did.

Finally, many thanks and our deepest gratitude to our reviewers, especially Elic, who has once again performed an invaluable service to our community.

R…

Using sparse collections with FORALL

FORALL is a key performance feature of PL/SQL. It helps you avoid row-by-row processing of non-query DML (insert, update, delete, merge) from within a PL/QL block. Best of all, almost always, is to do all your processing entirely within a single SQL statement. Sometimes, however, that isn't possible (for example, you need to sidestep SQL's "all or nothing" approach) or simply too difficult (not all of us have the insane SQL writing skills of a Tom Kyte or a Chris Saxon or a Connor McDonald).

To dive in deep on FORALL, check out any of the following resources:
FORALL documentationVideos at Practically Perfect PL/SQL Tim Hall on Bulk Binds In this post, I am going to focus on special features of FORALL that make it easy to work with space collections: the INDICES OF and VALUES OF clauses.

Typical FORALL Usage with Dense Bind Array

Here's the format you will most commonly see with FORALL: the header looks just like a numeric FOR loop, but notice: no loop keywords. Two…

Results of the Dev Gym Logic Championship for 2018

You will find below the rankings for the Logic Annual Championship for quizzes played in 2018. The number next to the player's name is the number of times that player has participated in a championship. Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Congratulations first and foremost to our top-ranked players:

1st Place: Stelios Vlasopoulos

2nd Place: Pavel Zeman

3rd Place: Sartograph


Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these players did.

Finally, many thanks to Eli Feuerstein, the Logic Quizmaster who provided a very challenging set of quizzes, and our deepest gratitude to our reviewers…

Use PL/SQL to Build and Access Document Stores

What does soda have to do with PL/SQL and Oracle Database? Not much...but SODA. Ah, there we have a different story to tell.

SODA stands for "Simple Oracle Document Access." It's a set of NoSQL-style APIs that let you create and store collections of documents (most importantly JSON) in Oracle Database, retrieve them, and query them, without needing to know SQL or how the documents are stored in the database. Read lots more about SODA here.

As of Oracle Database 18c, we offer SODA APIs for Java, C,  Node.js (JavaScript), PythonREST and PL/SQL.



I published an article on SODA for PL/SQL in Oracle Magazine; in this blog post, I focus on some highlights. Please do read the full article (and others still to come!). Also, Tim Hall of Oracle-BASE offers his usual outstanding treatment of this topic here.

SODA for PL/SQL? Whatever for?

First and most important, why would a database developer who writes PL/SQL want to avoid SQL and pretend that the amazing relational Oracle Data…

Three Hot Tips for Working With Collections

Collections in PL/SQL make it easy for you to implement lists, arrays, stacks, queues, etc. They come in three flavors: associative arrays, nested tables, and varrays. The three types of collections share many features, and also have their own special characteristics.

Here are some tips for making the most of collections. At the bottom of the post, I offer links to a number of resources for diving in more deeply on collections.

You Can Query From Collections

Collections are, for the most part, variables you will declare and manipulate in PL/SQL. But you can query from them using the TABLE operator (and in 12.2 and higher you even leave off that operator).

Use this feature to:
Manipulate table data and in-session collection data within a single SELECT.Use the set-oriented power of SQL on your in-session data.Build table functions (functions that return collections and can be called in the FROM clause of a query. Here's a simple demonstration:
CREATE OR REPLACE TYPE list_of_names_t …

Logic Reigns in the Oracle Dev Gym Logic Championship

Logic is at the very heart of programming, so we complement our quizzes on SQL, PL/SQL and so on with a weekly Logic tournament. And then at the end of the year, the top 50 ranked players qualify for our annual championship.

The following players will be invited to participate in the Logic Annual Championship for 2018, currently scheduled to take place on 19 February.

The number in parentheses after their names are the number of championships in which they have already participated.

Congratulations to all listed below on their accomplishment and best of luck in the upcoming competition!

NameRankStelios Vlasopoulos (5)1Pavel Zeman (4)2mentzel.iudith (5)3Ludovic Szewczyk (1)4James Su (5)5Chad Lee (5)6Tony Winn (3)7Rytis Budreika (5)8ted (5)9Kanellos (4)10Cor van Berkel (4)11K├Âteles Zsolt (4)12Vijay Mahawar (5)13RalfK (4)14pjas (1)15Mike Tessier (3)16seanm95 (5)17NickL (4)18Michal P. (1)19Eric Levin (4)20Sandra99 (5)21Mehrab (5)22JasonC (5)23Talebian (4)24NielsHecker (5)25richdellheim (5)…

Time for another Dev Gym PL/SQL Championship!

A new year has arrived, and that means that it's time (or will soon be time) for the PL/SQL Challenge Championship, when up to fifty top players from last year's PL/SQL tournament quizzes compete for top honors.

The following players will be invited to participate in the PL/SQL Challenge Championship for 2018, currently scheduled to take place on 21 March (hey, it takes some time to put together five advanced quizzes without any mistakes in them!).

The number in parentheses after their names are the number of championships in which they have already participated (note: from 2010 through 2013, we held quarterly championships for our then daily PL/SQL quiz!).

Congratulations to all listed below on their accomplishment and best of luck in the upcoming championship!


NameRankStelios Vlasopoulos (15)1mentzel.iudith (18)2Tony Winn (7)3NielsHecker (19)4Andrey Zaytsev (7)5patch72 (5)6Ivan Blanarik (12)7siimkask (18)8Rakesh Dadhich (10)9Rytis Budreika (6)10Vyacheslav Stepanov (17)11li_ba…

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks.

In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods.

Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods.

Here are the methods you are most likely to use:

A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL:

Error Handling Behavior

By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not raised back to your block.

If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greater than 0.

Array I…