Why I Completely Forgot That Programming Languages Have While Loops

I’ve recently made an embarassing discovery:

Yes. In all of my professional work with PL/SQL (and that has been quite a bit, in the banking industry), I have never really used a WHILE loop – at least not that I recall. The idea of a WHILE loop is simple, and it is available in most languages, like PL/SQL:

WHILE condition
LOOP
   {...statements...}
END LOOP;

Or Java:

while (condition)
    statement

So, why have I simply never used it?

Most loops iterate on collections

In hindsight, it’s crazy to think that it took Java until version 5 to introduce the foreach loop:

String[] strings = { "a", "b", "c" };
for (String s : strings)
    System.out.println(s);

It is some of Java’s most useful syntax sugar for the equivalent loop that uses a local loop variable that we simply don’t care about:

String[] strings = { "a", "b", "c" };
for (int i = 0; i < strings.length; i++)
    System.out.println(strings[i]);

Let’s be honest. When do we really want this loop variable? Hardly ever. Sometimes, we need to access the next string along with the current one, and we want to stick to the imperative paradigm for some reason (when we could do it more easily with functional programming APIs). But that’s it. Most loops simply iterate the entire collection in a very dumb and straightforward way.

SQL is all about collections

In SQL, everything is a table (see SQL trick #1 in this article), just like in relational algebra, everything is a set.

Now, PL/SQL is a useful procedural language that “builds around” the SQL language in the Oracle database. Some of the main reasons to do things in PL/SQL (rather than e.g. in Java) are:

  • Performance (the most important reason), e.g. when doing ETL or reporting
  • Logic needs to be “hidden” in the database (e.g. for security reasons)
  • Logic needs to be reused among different systems that all access the database

Much like Java’s foreach loop, PL/SQL has the ability to define implicit cursors (as opposed to explicit ones)

As a PL/SQL developer, when I want to loop over a cursor, I have at least these options:

Explicit cursors

DECLARE
  -- The loop variable
  row all_objects%ROWTYPE;

  -- The cursor specification
  CURSOR c IS SELECT * FROM all_objects;
BEGIN
  OPEN  c;
  LOOP
    FETCH c INTO row;
    EXIT WHEN c%NOTFOUND;
    dbms_output.put_line(row.object_name);
  END LOOP;
  CLOSE c;
END;

The above would correspond to the following boring Java code that we wrote time and again prior to Java 5 (in fact, without the generics):

Iterator<Row> c = ... // SELECT * FROM all_objects
while (c.hasNext()) {
    Row row = c.next();
    System.out.println(row.objectName);
}

The while loop is absolutely boring to write. Just like with the loop variable, we really don’t care about the current state of the iterator. We want to iterate over the whole collection, and at each iteration, we don’t care where we’re currently at.

Note that in PL/SQL, it is common practice to use an infinite loop syntax and break out of the loop when the cursor is exhausted (see above). In Java, this would be the corresponding logic, which is even worse to write:

Iterator<Row> c = ... // SELECT * FROM all_objects
for (;;) {
    if (!c.hasNext())
        break;
    Row row = c.next();
    System.out.println(row.objectName);
}

Implicit cursors

Here’s how many PL/SQL developers do things most of the time:

BEGIN
  FOR row IN (SELECT * FROM all_objects)
  LOOP
    dbms_output.put_line(row.object_name);
  END LOOP;
END;

The cursor is really an Iterable in terms of Java collections. An Iterable is a specification of what collection (Iterator) will be produced when the control flow reaches the loop. I.e. a lazy collection.

It’s very natural to implement external iteration in the above way.

If you’re using jOOQ to write SQL in Java (and you should), you can apply the same pattern in Java as well, as jOOQ’s ResultQuery type extends Iterable, which means it can be used as an Iterator source in Java’s foreach loop:

for (AllObjectsRecord row : ctx.selectFrom(ALL_OBJECTS))
    System.out.println(row.getObjectName());

Yes, that’s it! Focus on the business logic only, which is the collection specification (the query) and what you do with each row (the println statement). None of that cursor noise!

OK, but why no WHILE?

If you love SQL as much as me, you probably do that because you like the idea of having a declarative programming language to declare sets of data, just like SQL does. If you write client code in PL/SQL or Java, you will thus like to continue working on the entire data set and continue thinking in terms of sets. The imperative programming paradigm that operates on the intermediate object, the cursor, is not your paradigm. You don’t care about the cursor. You don’t want to manage it, you don’t want to open / close it. You don’t want to keep track of its state.

Thus, you will choose the implicit cursor loop, or the foreach loop in Java (or some Java 8 Stream functionality).

As you do that more often, you will run into less and less situations where the WHILE loop is useful. Until you forget about its mere existence.

WHILE LOOP, you won’t be missed.

Using Stored Procedures With JPA, JDBC… Meh, Just Use jOOQ

The current edition of the Java magazine has an article about Big Data Best Practices for JDBC and JPA by Josh Juneau:
http://www.javamagazine.mozaicreader.com/MayJune2016

The article shows how to use a stored procedure with JDBC (notice how resources aren’t closed, unfortunately. This is commonly forgotten, even in Java Magazine articles)

// Using JDBC to call upon a database stored
// procedure
CallableStatement cs = null;
try {
    cs = conn.prepareCall("{call DUMMY_PROC(?,?)}");
    cs.setString(1, "This is a test");
    cs.registerOutParameter(2, Types.VARCHAR);
    cs.executeQuery();

    // Do something with result
    String returnStr = cs.getString(2);
} catch (SQLException ex){
    ex.printStackTrace();
}

And with JPA:

// Utilize JPA to call a database stored procedure
// Add @NamedStoredProcedureQuery to entity class
@NamedStoredProcedureQuery(
    name="createEmp", procedureName="CREATE_EMP",
    parameters = {
        @StoredProcedureParameter(
            mode= ParameterMode.IN,
            type=String.class,
            name="first"),
        @StoredProcedureParamter(
            mode = ParameterMode.IN,
            type=String.class,
            name="last")
    })

// Calling upon stored procedure
StoredProcedureQuery qry =
    em.createStoredProcedureQuery("createEmp");
qry.setParameter("first", "JOSH");
qry.setParameter("last","JUNEAU");
qry.execute();

Specifically the latter was also recently discussed in blog posts by Vlad Mihalcea and Thorben Janssen.

Do you like verbosity and complexity?

No? We neither. This is why we give you a third option instead: Just use jOOQ. Here’s the equivalent jOOQ code:

// JDBC example:
String returnStr = Routines.dummyProc(
    config, "This is a test");

// JPA example
Routines.createEmp(config, "JOSH", "JUNEAU");

Yes! That’s it. Don’t waste time manually configuring your bind variables with JDBC API calls, or JPA annotations. No one likes writing annotations for stored procedures. With jOOQ and jOOQ’s code generator, procedure calls are:

  • A one-liner
  • A no-brainer
  • A way to bring back the fun to stored procedures

Learn more about using Oracle stored procedures with nested collections and object types here:
https://blog.jooq.org/2014/11/04/painless-access-from-java-to-plsql-procedures-with-jooq

It’s the Little Things: The PL/SQL NULL Statement, and why Every Language Should have One

Syntax is one of those topics. One of those emotional topics that lead to very very very important discussions. I personally like PL/SQL. It is extremely verbose, and precise. It forces you to adhere to a very strong and rigid type system, slowing you down, which is likely to help you avoid mistakes.

There is one thing in PL/SQL that I like in particular. There is no such thing as an empty block.

While in Java, you could write:

// Just an empty block:
{}

// An empty block with a label:
label1: {}

// Or, in fact, the empty statement:
;
label2: ;

The problem with the above from a mere syntactic perspective is that an empty block may have been left unintentionally empty. An empty statement may not even be visible at all. Consider this in the context of an if statement:

if (something) {

}

if (somethingElse) ;

In PL/SQL, this isn’t possible. There is no such thing as an empty block. The following doesn’t compile:

BEGIN
END;

Nope:

ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "END" when expecting ...

Or, take the IF statement. No emptiness allowed here, either:

IF 1 = 1 THEN
END IF;

Doesn’t work.

If you DO want to create empty blocks (e.g. as placeholders for code you’ll write later on), you’ll have to explicitly put a dummy statement there. PL/SQL has such a statement. The NULL statement:

BEGIN
  NULL;
END;

IF 1 = 1 THEN
  NULL;
END IF;

That makes sense. You immediately see: OK, nothing going on here.

Conclusion

Verbosity helps decrease the number of bugs in your code. The less people can be concerned with syntax (see again, the discussion about very very very important topics), the more they can focus on what they really intend to write. Let’s swallow our pride. When we get used to a language, we’ll accept ANY language. They’re all flawed and quirky. It doesn’t matter. But at least, the language should keep us from arguing about different ways to style it.

Now, let me go reformat all that moron’s lower-case PL/SQL code. Who the hell would write lower-case begin and end!??

jOOQ Newsletter: January 21, 2015 – Groovy and Open Source – jOOQ and the strong Swiss Franc

Subscribe to this newsletter here

Tweet of the Day

Today, we’re very happy to have “spied” on our users as we can now show you a whole Tweet Conversation of the Day

RxJooq, or reactive jOOQ. How does that sound!? Yes, jOOQ is growing to become a hype among SQL and fluent API aficionados. A recent discussion on reddit already puts jOOQ on the same level with Hibernate with more than 10 mentions in answers to the question “Java: What ORM to use”. Our goal has always been for a Java developer to ask themselves at the beginning of a project:

Is this a jOOQ project, or is this a Hibernate project (or both)?

It is too early to announce anything, but at Data Geekery, we’re very interested and thus putting efforts into collaborating with Red Hat to make the jOOQ / Hibernate integration work more seamlessly, so stay tuned for more goodness in that area.

Groovy and Open Source – What it means for us

You may have heard of Pivotal’s recent announcement about their withdrawing sponsorship from the Groovy and Grails ecosystem. This isn’t exactly a surprise to many people as Pivotal’s main focus has shifted towards their PaaS business quite some time ago. The interesting aspect from our perspective is the fact that a whole ecosystem seems to have relied on the benevolence of a single sponsor. Quite a risk!

We think that Open Source should work differently. Open Source is a fine means of offering freemium and (legally) riskless software to potential customers in order to help customers start engaging with a brand. The ultimate vendor goal with Open Source is always upselling. As our valued jOOQ users and jOOQ newsletter and blog readers, we obviously hope that you will eventually understand all the combined SQL value put into jOOQ, and thus upgrade to a commercial jOOQ subscription.

This wasn’t necessarily the case at Pivotal. There is no obvious path from using Groovy (or Grails) to buying Pivotal’s cloud platform solutions. To make things worse, in order to survive, the Groovy platform now depends on a new, arbitrary sponsor whose incentive to sponsor Groovy might be 100% different from Pivotal’s. For the end user, this will not be the same Groovy any more – so it is hard to believe that Groovy will not suffer heavily from any future transition.

We believe that vendors shouldn’t depend on benevolence. We believe that vendors should have a very clear strategy why they’re creating a product, and do everything necessary to satisfy real customer’s needs. So we want to take the opportunity and thank you for being with us, and for making jOOQ (both the Open Source Edition and the Commercial Editions) what it is: A platform valued by both users of Open Source and commercial databases.

More information about our take on Pivotal and Groovy can be found on our blog:

It’s jOONuary! Profit from our 20% Discount Promotion

Speaking of our customers, there has never been a better time to become one!

Your budget for 2015 has been set in stone? You spent too much money on geeky infrastructure during the Holiday Season? Not a problem for your planned jOOQ integration! If you purchase new jOOQ licenses in jOONuary (January 2015), we will offer you a limited-time 20% discount on all price plans. Act quickly!

http://www.jooq.org/joonuary

jOOQ and the Strong Swiss Franc

We’re a Switzerland-based company, and as such are heavily influenced by recent events on the currency exchange markets. The EUR (which is our sale currency) has plummeted almost 20% compared to the CHF (which is our accounting currency).

This affects all of the Swiss export industry, and many companies are starting to take measures. We will not take any measures thus far and continue with our existing EUR-based price model. For our international customers, nothing will change. For our Swiss customers, this means that in addition to the above jOONuary discount, you will now also benefit from a “Euro discount”! Did we say there has never been a better time to become our customer?

jOOQ 3.6 Outlook

The upcoming jOOQ 3.6 will not be less exciting than the previous versions in the least bit. Here is a quick outline of what we’re going to be doing in the upcoming release:

  • SAP HANA support. We’ve been talking to database vendors in the past, and we continue to do so, maintaining good relationships with the technical and community people at the vendor side. This time, the collaboration initiative came from the vendor directly, and we’ve heard them.

    SAP HANA is an emerging cloud SQL and in-memory SQL platform, with a big Java and Scala based tool chain, which constitutes a perfect match for the jOOQ ecosystem. We’re going to support both HANA’s SQL features as well as HANA’s SQLScript features in the jOOQ 3.6 Enterprise Edition. If you’re an SAP HANA user and interested in details, or in a free preview of jOOQ 3.6.0, please contact sales right away. We’re more than happy to provide you with more info.

  • Nested records and tables. One of the SQL standard’s most underestimated features is the capability of nesting records and tables. In a true ORDBMS, tables (or MULTISETs) can be nested any level deep. If your SQL database supports these features, it is very easy to materialise a nested object graph directly in the database, instead of relying on the JOIN-based workarounds provided by modern ORMs.

    Nesting of records can also be very useful when reusing common data structures, such as audit columns (creation_date, creation_user, modification_date, modification_user). JPA supports the @Embedded annotation for this, and we’ll delve into these features as well.

    We believe that true MULTISET support will obsolete our competing products’ most important asset: mapping. Once you can declare all mapping already in SQL, you will no longer miss JPA once you’ve migrated to jOOQ.

  • A new ConverterProvider SPI. Converters are great for supporting custom data types, but having to register them all the time is tedious. What if jOOQ just supported T <-> U conversion right out of the box, for any combination of T and U? We’ll let you register all your favourite converters and jOOQ figures out the conversion path through the converter graph.
  • Even better PL/SQL support. PL/SQL types are ubiquitous, but they are not easily accessible via JDBC, and thus via jOOQ. We’re researching a variety of possibilities of working around JDBC’s limitations to allow you to use your favourite PL/SQL types: BOOLEAN, RECORD types, perhaps even table types.

 

Upcoming jOOQ Events

Have you missed one of our talks and presentations in the recent past? No problem at all, we’re back on the road after a short winter break. Here are all of our upcoming events:

Keep up to date with our own and third-party jOOQ events on our news website:http://www.jooq.org/news.

We’re looking forward to meeting you and to talking about all things Java and SQL!

jOOQ Tuesdays: Yalım Gerger brings Git to Oracle

We’re excited to launch a new series on our blog: the jOOQ Tuesdays. In this series, we’ll publish an article on the third Tuesday every month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics.

yalim-gergerWe have the pleasure of starting this series with Yalım Gerger who will show us why he thinks that Oracle PL/SQL developers are more than ready for Git!

Hi Yalım – you’re the founder of Gerger, the company behind Formspider. What’s Formspider?

Hi. Formspider is an application development framework for Oracle PL/SQL developers. It enables PL/SQL developers to build high quality business applications using only PL/SQL as the programming language. No Java or JavaScript skills are needed to use Formspider.

Interesting, even from a Java developer’s perspective! Essentially, you’re offering a way to completely bypass Java as middleware (and of course HTML, JavaScript, CSS).

This is not entirely true. We still use Java in our product. Formspider has a middletier application that our customers can deploy to any JEE compliant application server. This middletier application helps us bridge Formspider JavaScript library running in the client’s browser to the PL/SQL running in the database. We also use Java Libraries to generate Excel files from the data stored in an application, a common use case for business applications. So yes, the applications are not coded in Java. Our customers are PL/SQL developers. But we use Java to improve our product. Same with HTML and Javascript. Our job is to understand these technologies and their capabilities really well and expose them as intuitive API’s to PL/SQL developers.

Do you also have customers that access their PL/SQL APIs both through Formspider as well as through their home-grown Java / .NET applications?

Yes. We have customers who have both PL/SQL teams that work with Formspider and Java teams that work with Java technologies. This requires a great deal of collaboration between two teams and that’s not always possible.

Usually, what happens is that Java/.NET teams try to move the application away from the database as much as possible. I was just talking to a friend who works at a large financial institution in which the OO guys are pushing hard to eliminate all the PL/SQL API’s. He was going mad. There are various reasons for this. It is partly political turf wars, partly pure ignorance about the capabilities of database software and PL/SQL.

We can feel the pain. There is no such thing as a magic bullet… So what should they do then? How should an application be architected? Do you think there is a “right” architecture?

No. I think it depends so much on the context. Are you building a consumer app or are you building a business app? Are you a company building a horizontal product or an IT department serving a business operating in a particular vertical? There are so many parameters to consider. At the risk of being too generic, I think an IT department serving a large enterprise should not try build a database agnostic application. That’s silly. On the contrary it should take full advantage of the database software, and other software it uses. You shouldn’t pretend to be seven different organizations building seven different layers of the application just happen to be collaborating. You are just one organization. Act like that. Cut through the fat. Integrate as deeply as you can. This is the most cost effective way to build well performing applications on time and on budget. Database agnostic software is for horizontal software companies.

We’ve recently blogged about the caveats of dual licensing, where we said that shipping our sources to paying customers is essential for a company that calls themselves an Open Source company like Data Geekery does. I’ve seen you ship your sources as well – but you’re not really doing “Open Source”. How would you describe your offering?

I loved that blog post by the way. I think the way jOOQ is licensed is brilliantly fair i.e. it is free if the database is free and it has a price tag if your database has a license fee. In our case, the database always has a license fee. So we don’t have a free option for Formspider. For the Oracle community and for the price tags that they are used to, our license fee is so small that it is practically free. Anyone who is thrown off by our price tag is probably not serious about using Formspider anyway.

Our customers who sign up for our highest level of support service may get the source code of our product for the duration of the professional service. This option is attractive for customers who invest a lot into the application they build with Formspider.

Yes, Oracle price tags have a reputation… Yalım, you seem to be an Oracle person. And as such, you are about to launch gitora. What is it?

Gitora is a free version control tool that integrates the Oracle database with Git. This is a little embarrassing to bring up in a blog mainly read by Java developers but very common version control tasks that most Java developers take for granted are very hard to do in PL/SQL. There is a good reason for this. PL/SQL has no concept of a working directory. PL/SQL is not a file based language i.e. source code units do not reside in the private file system of a developer but in the Oracle database as packages, procedures and functions globally  available to any developer. That makes version control very difficult if not impossible.

So what do people do?

Nothing mostly. Daily backups are used as a way to get back to a previous state of the code if needed.

Some teams create one working directory that is hooked to version control and store all their PL/SQL code in this directory by extracting the DDL’s, usually manually. That’s as sophisticated as it gets.

Proper team development and merging in PL/SQL is very difficult and I haven’t seen it done successfully very often. And I’ve interacted with a lot of PL/SQL teams all around the World. Gitora makes this very easy. It turns the database schema to a working directory. If you execute a Git command, any change to the working directory happens automatically in your database schema.

Interesting. We’ve recently implemented a home-grown “solution” for a customer, which implements automatic version control and installation from a Microsoft Team Foundation Server repository. Maybe, we should migrate to Gitora then?

I didn’t know that. That’s so cool. If you build a version control tool which works for PL/SQL and talks to TFS instead of Git, I think that is also very valuable. Essentially we build the same product but used different version control products. I encourage you to put it out there.

Why not. Maybe we’ll contribute!

Thanks for this very interesting insight, Yalım!

If this interview has triggered your interest, follow Yalım, FormSpider, or Gitora on Twitter:

For more information about Gitora, watch the Gitora tutorial:

Painless Access from Java to PL/SQL Procedures with jOOQ

A procedural language combined with SQL can do miracles in terms of productiveness, performance and expressivity.

In this article, we’ll see later on, how we can achieve the same with SQL (and PL/SQL) in Java, using jOOQ.

But first, a little bit of history…

Accessing PL/SQL from Java

One of the biggest reasons why Java developers in particular refrain from writing their own PL/SQL code is because the interface between PL/SQL and Java – ojdbc – is a major pain. We’ll see in the following examples how that is.

Assume we’re working on an Oracle-port of the popular Sakila database (originally created for MySQL). This particular Sakila/Oracle port was implemented by DB Software Laboratory and published under the BSD license.

Here’s a partial view of that Sakila database.

Sakila-film-actor-category

ERD created with vertabelo.comlearn how to use Vertabelo with jOOQ

Now, let’s assume that we have an API in the database that doesn’t expose the above schema, but exposes a PL/SQL API instead. The API might look something like this:

CREATE TYPE LANGUAGE_T AS OBJECT (
  language_id SMALLINT,
  name CHAR(20),
  last_update DATE
);
/

CREATE TYPE LANGUAGES_T AS TABLE OF LANGUAGE_T;
/

CREATE TYPE FILM_T AS OBJECT (
  film_id int,
  title VARCHAR(255),
  description CLOB,
  release_year VARCHAR(4),
  language LANGUAGE_T,
  original_language LANGUAGE_T,
  rental_duration SMALLINT,
  rental_rate DECIMAL(4,2),
  length SMALLINT,
  replacement_cost DECIMAL(5,2),
  rating VARCHAR(10),
  special_features VARCHAR(100),
  last_update DATE
);
/

CREATE TYPE FILMS_T AS TABLE OF FILM_T;
/

CREATE TYPE ACTOR_T AS OBJECT (
  actor_id numeric,
  first_name VARCHAR(45),
  last_name VARCHAR(45),
  last_update DATE
);
/

CREATE TYPE ACTORS_T AS TABLE OF ACTOR_T;
/

CREATE TYPE CATEGORY_T AS OBJECT (
  category_id SMALLINT,
  name VARCHAR(25),
  last_update DATE
);
/

CREATE TYPE CATEGORIES_T AS TABLE OF CATEGORY_T;
/

CREATE TYPE FILM_INFO_T AS OBJECT (
  film FILM_T,
  actors ACTORS_T,
  categories CATEGORIES_T
);
/

You’ll notice immediately, that this is essentially just a 1:1 copy of the schema in this case modelled as Oracle SQL OBJECT and TABLE types, apart from the FILM_INFO_T type, which acts as an aggregate.

Now, our DBA (or our database developer) has implemented the following API for us to access the above information:

CREATE OR REPLACE PACKAGE RENTALS AS
  FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
  FUNCTION GET_ACTORS RETURN ACTORS_T;
  FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
  FUNCTION GET_FILMS RETURN FILMS_T;
  FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
  FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

This, ladies and gentlemen, is how you can now…

… tediously access the PL/SQL API with JDBC

So, in order to avoid the awkward CallableStatement with its OUT parameter registration and JDBC escape syntax, we’re going to fetch a FILM_INFO_T record via a SQL statement like this:

try (PreparedStatement stmt = conn.prepareStatement(
        "SELECT rentals.get_film_info(1) FROM DUAL");
     ResultSet rs = stmt.executeQuery()) {

    // STRUCT unnesting here...
}

So far so good. Luckily, there is Java 7’s try-with-resources to help us clean up those myriad JDBC objects. Now how to proceed? What will we get back from this ResultSet? A java.sql.Struct:

while (rs.next()) {
    Struct film_info_t = (Struct) rs.getObject(1);

    // And so on...
}

Now, the brave ones among you would continue downcasting the java.sql.Struct to an even more obscure and arcane oracle.sql.STRUCT, which contains almost no Javadoc, but tons of deprecated additional, vendor-specific methods.

For now, let’s stick with the “standard API”, though.

Interlude:

Let’s take a moment to appreciate JDBC in times of Java 8.

When Java 5 was introduced, so were generics. We have rewritten our big code bases to remove all sorts of meaningless boilerplate type casts that are now no longer needed. With the exception of JDBC. When it comes to JDBC, guessing appropriate types is all a matter of luck. We’re accessing complex nested data structures provided by external systems by dereferencing elements by index, and then taking wild guesses at the resulting data types.

Lambdas have just been introduced, yet JDBC still talks to the mainframe.

Rhonda said, she put that STRUCT right between jack 73 and 75 on array F-B4. I wonder if I need my AC/DC converter to plug it

Rhonda said, she put that STRUCT right between jack 73 and 75 on array F-B4. I wonder if I need my AC/DC converter to plug it
Image in public domain

And then…

And here be dragons. And STRUCTS

And here be dragons. And STRUCTS
Original image in public domain

OK, enough of these rants.

Let’s continue navigating our STRUCT

while (rs.next()) {
    Struct film_info_t = (Struct) rs.getObject(1);

    Struct film_t = (Struct) film_info_t.getAttributes()[0];
    String title = (String) film_t.getAttributes()[1];
    Clob description_clob = (Clob) film_t.getAttributes()[2];
    String description = description_clob.getSubString(1, (int) description_clob.length());

    Struct language_t = (Struct) film_t.getAttributes()[4];
    String language = (String) language_t.getAttributes()[1];

    System.out.println("Film       : " + title);
    System.out.println("Description: " + description);
    System.out.println("Language   : " + language);
}

From the initial STRUCT that we received at position 1 from the ResultSet, we can continue dereferencing attributes by index. Unfortunately, we’ll constantly need to look up the SQL type in Oracle (or in some documentation) to remember the order of the attributes:

CREATE TYPE FILM_INFO_T AS OBJECT (
  film FILM_T,
  actors ACTORS_T,
  categories CATEGORIES_T
);
/

And that’s not it! The first attribute of type FILM_T is yet another, nested STRUCT. And then, those horrible CLOBs. The above code is not strictly complete. In some cases that only the maintainers of JDBC can fathom, java.sql.Clob.free() has to be called to be sure that resources are freed in time. Remember that CLOB, depending on your database and driver configuration, may live outside the scope of your transaction.

Unfortunately, the method is called free() instead of AutoCloseable.close(), such that try-with-resources cannot be used. So here we go:

List<Clob> clobs = new ArrayList<>();

while (rs.next()) {
    try {
        Struct film_info_t = (Struct) rs.getObject(1);
        Struct film_t = (Struct) film_info_t.getAttributes()[0];

        String title = (String) film_t.getAttributes()[1];
        Clob description_clob = (Clob) film_t.getAttributes()[2];
        String description = description_clob.getSubString(1, (int) description_clob.length());

        Struct language_t = (Struct) film_t.getAttributes()[4];
        String language = (String) language_t.getAttributes()[1];

        System.out.println("Film       : " + title);
        System.out.println("Description: " + description);
        System.out.println("Language   : " + language);
    }
    finally {
        // And don't think you can call this early, either
        // The internal specifics are mysterious!
        for (Clob clob : clobs)
            clob.free();
    }
}

That’s about it. Now we have found ourselves with some nice little output on the console:

Film       : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad 
             Scientist who must Battle a Teacher in
             The Canadian Rockies
Language   : English             

That’s about it – You may think! But…

The pain has only started

… because we’re not done yet. There are also two nested table types that we need to deserialise from the STRUCT. If you haven’t given up yet (bear with me, good news is nigh), you’ll enjoy reading about how to fetch and unwind a java.sql.Array. Let’s continue right after the printing of the film:

Array actors_t = (Array) film_info_t.getAttributes()[1];
Array categories_t = (Array) film_info_t.getAttributes()[2];

Again, we’re accessing attributes by indexes, which we have to remember, and which can easily break. The ACTORS_T array is nothing but yet another wrapped STRUCT:

System.out.println("Actors     : ");

Object[] actors = (Object[]) actors_t.getArray();
for (Object actor : actors) {
    Struct actor_t = (Struct) actor;

    System.out.println(
        "  " + actor_t.getAttributes()[1]
       + " " + actor_t.getAttributes()[2]);
}

You’ll notice a few things:

  • The Array.getArray() method returns an array. But it declares returning Object. We have to manually cast.
  • We can’t cast to Struct[] even if that would be a sensible type. But the type returned by ojdbc is Object[] (containing Struct elements)
  • The foreach loop also cannot dereference a Struct from the right hand side. There’s no way of coercing the type of actor into what we know it really is
  • We could’ve used Java 8 and Streams and such, but unfortunately, all lambda expressions that can be passed to the Streams API disallow throwing of checked exceptions. And JDBC throws checked exceptions. That’ll be even uglier.

Anyway. Now that we’ve finally achieved this, we can see the print output:

Film       : ACADEMY DINOSAUR
Description: A Epic Drama of a Feminist And a Mad 
             Scientist who must Battle a Teacher in
             The Canadian Rockies
Language   : English             
Actors     : 
  PENELOPE GUINESS
  CHRISTIAN GABLE
  LUCILLE TRACY
  SANDRA PECK
  JOHNNY CAGE
  MENA TEMPLE
  WARREN NOLTE
  OPRAH KILMER
  ROCK DUKAKIS
  MARY KEITEL

When will this madness stop?

It’ll stop right here!

So far, this article read like a tutorial (or rather: medieval torture) of how to deserialise nested user-defined types from Oracle SQL to Java (don’t get me started on serialising them again!)

In the next section, we’ll see how the exact same business logic (listing Film with ID=1 and its actors) can be implemented with no pain at all using jOOQ and its source code generator. Check this out:

// Simply call the packaged stored function from
// Java, and get a deserialised, type safe record
FilmInfoTRecord film_info_t = Rentals.getFilmInfo1(
    configuration, new BigInteger("1"));

// The generated record has getters (and setters)
// for type safe navigation of nested structures
FilmTRecord film_t = film_info_t.getFilm();

// In fact, all these types have generated getters:
System.out.println("Film       : " + film_t.getTitle());
System.out.println("Description: " + film_t.getDescription());
System.out.println("Language   : " + film_t.getLanguage().getName());

// Simply loop nested type safe array structures
System.out.println("Actors     : ");
for (ActorTRecord actor_t : film_info_t.getActors()) {
    System.out.println(
        "  " + actor_t.getFirstName()
       + " " + actor_t.getLastName());
}

System.out.println("Categories     : ");
for (CategoryTRecord category_t : film_info_t.getCategories()) {
    System.out.println(category_t.getName());
}

Is that it?

Yes!

Wow, I mean, this is just as though all those PL/SQL types and procedures / functions were actually part of Java. All the caveats that we’ve seen before are hidden behind those generated types and implemented in jOOQ, so you can concentrate on what you originally wanted to do. Access the data objects and do meaningful work with them. Not serialise / deserialise them!

Let’s take a moment and appreciate this consumer advertising:

jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.

Not convinced yet?

I told you not to get me started on serialising the types to JDBC. And I won’t, but here’s how to serialise the types to jOOQ, because that’s a piece of cake!

Let’s consider this other aggregate type, that returns a customer’s rental history:

CREATE TYPE CUSTOMER_RENTAL_HISTORY_T AS OBJECT (
  customer CUSTOMER_T,
  films FILMS_T
);
/

And the full PL/SQL package specs:

CREATE OR REPLACE PACKAGE RENTALS AS
  FUNCTION GET_ACTOR(p_actor_id INT) RETURN ACTOR_T;
  FUNCTION GET_ACTORS RETURN ACTORS_T;
  FUNCTION GET_CUSTOMER(p_customer_id INT) RETURN CUSTOMER_T;
  FUNCTION GET_CUSTOMERS RETURN CUSTOMERS_T;
  FUNCTION GET_FILM(p_film_id INT) RETURN FILM_T;
  FUNCTION GET_FILMS RETURN FILMS_T;
  FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer_id INT) RETURN CUSTOMER_RENTAL_HISTORY_T;
  FUNCTION GET_CUSTOMER_RENTAL_HISTORY(p_customer CUSTOMER_T) RETURN CUSTOMER_RENTAL_HISTORY_T;
  FUNCTION GET_FILM_INFO(p_film_id INT) RETURN FILM_INFO_T;
  FUNCTION GET_FILM_INFO(p_film FILM_T) RETURN FILM_INFO_T;
END RENTALS;
/

So, when calling RENTALS.GET_CUSTOMER_RENTAL_HISTORY we can find all the films that a customer has ever rented. Let’s do that for all customers whose FIRST_NAME is “JAMIE”, and this time, we’re using Java 8:

// We call the stored function directly inline in
// a SQL statement
dsl().select(Rentals.getCustomer(
          CUSTOMER.CUSTOMER_ID
      ))
     .from(CUSTOMER)
     .where(CUSTOMER.FIRST_NAME.eq("JAMIE"))

// This returns Result<Record1<CustomerTRecord>>
// We unwrap the CustomerTRecord and consume
// the result with a lambda expression
     .fetch()
     .map(Record1::value1)
     .forEach(customer -> {
         System.out.println("Customer  : ");
         System.out.println("- Name    : " 
           + customer.getFirstName() 
           + " " + customer.getLastName());
         System.out.println("- E-Mail  : " 
           + customer.getEmail());
         System.out.println("- Address : " 
           + customer.getAddress().getAddress());
         System.out.println("            " 
           + customer.getAddress().getPostalCode() 
           + " " + customer.getAddress().getCity().getCity());
         System.out.println("            " 
           + customer.getAddress().getCity().getCountry().getCountry());

// Now, lets send the customer over the wire again to
// call that other stored procedure, fetching his
// rental history:
         CustomerRentalHistoryTRecord history = 
           Rentals.getCustomerRentalHistory2(dsl().configuration(), customer);

         System.out.println("  Customer Rental History : ");
         System.out.println("    Films                 : ");

         history.getFilms().forEach(film -> {
             System.out.println("      Film                : " 
               + film.getTitle());
             System.out.println("        Language          : " 
               + film.getLanguage().getName());
             System.out.println("        Description       : " 
               + film.getDescription());

// And then, let's call again the first procedure
// in order to get a film's actors and categories
             FilmInfoTRecord info = 
               Rentals.getFilmInfo2(dsl().configuration(), film);

             info.getActors().forEach(actor -> {
                 System.out.println("          Actor           : " 
                   + actor.getFirstName() + " " + actor.getLastName());
             });

             info.getCategories().forEach(category -> {
                 System.out.println("          Category        : " 
                   + category.getName());
             });
         });
     });

… and a short extract of the output produced by the above:

Customer  : 
- Name    : JAMIE RICE
- E-Mail  : JAMIE.RICE@sakilacustomer.org
- Address : 879 Newcastle Way
            90732 Sterling Heights
            United States
  Customer Rental History : 
    Films                 : 
      Film                : ALASKA PHANTOM
        Language          : English             
        Description       : A Fanciful Saga of a Hunter
                            And a Pastry Chef who must
                            Vanquish a Boy in Australia
          Actor           : VAL BOLGER
          Actor           : BURT POSEY
          Actor           : SIDNEY CROWE
          Actor           : SYLVESTER DERN
          Actor           : ALBERT JOHANSSON
          Actor           : GENE MCKELLEN
          Actor           : JEFF SILVERSTONE
          Category        : Music
      Film                : ALONE TRIP
        Language          : English             
        Description       : A Fast-Paced Character
                            Study of a Composer And a
                            Dog who must Outgun a Boat
                            in An Abandoned Fun House
          Actor           : ED CHASE
          Actor           : KARL BERRY
          Actor           : UMA WOOD
          Actor           : WOODY JOLIE
          Actor           : SPENCER DEPP
          Actor           : CHRIS DEPP
          Actor           : LAURENCE BULLOCK
          Actor           : RENEE BALL
          Category        : Music

If you’re using Java and PL/SQL…

… then you should click on the below banner and download the free trial right now to experiment with jOOQ and Oracle:

jOOQ generates Java code from your database and lets you build type safe SQL queries through its fluent API.

The Oracle port of the Sakila database is available from this URL for free, under the terms of the BSD license:

https://www.jooq.org/sakila

Finally, it is time to enjoy writing PL/SQL again!

Further reading:

See also this article about how jOOQ 3.9 provides access to PL/SQL RECORD types.

PL/SQL backtraces for debugging

For many PL/SQL developers, this might be common sense, but for one of our customers, this was an unknown PL/SQL feature: Backtraces.

When your application raises an error somewhere deep down in the call stack, you don’t get immediate information about the exact source of the error. For large PL/SQL applications, this can be a pain. One workaround is to keep track of statement numbers that were last executed before any error occurred:

DECLARE
  v_statement_no := 0;
BEGIN
  v_statement_no := 1;
  SELECT ...

  v_statement_no := 2;
  INSERT ...

  v_statement_no := 3;
  ...
EXCEPTION
  WHEN OTHERS THEN
    -- Log error message somewhere
    logger.error(module, v_statement_no, sqlerrm);
END;

The above looks an awful lot like println-debugging, a thing that isn’t really known to Java developers ;-)

But println-debugging isn’t necessary in PL/SQL either. Use the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, instead! An example:

DECLARE
  PROCEDURE p4 IS BEGIN
    raise_application_error(-20000, 'Some Error');
  END p4;
  PROCEDURE p3 IS BEGIN
    p4;
  END p3;
  PROCEDURE p2 IS BEGIN
    p3;
  END p2;
  PROCEDURE p1 IS BEGIN
    p2;
  END p1;

BEGIN
  p1;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(
      dbms_utility.format_error_backtrace
    );
END;
/

The above PL/SQL block generates the following output:

ORA-20000: Some Error
ORA-06512: at line 3
ORA-06512: at line 6
ORA-06512: at line 9
ORA-06512: at line 12
ORA-06512: at line 16

You can see exactly what line number generated the error. If you’re not using local procedures in anonymous blocks (which you quite likely aren’t), this gets even more useful:

CREATE PROCEDURE p4 IS BEGIN
  raise_application_error(-20000, 'Some Error');
END p4;
/
CREATE PROCEDURE p3 IS BEGIN
  p4;
END p3;
/
CREATE PROCEDURE p2 IS BEGIN
  p3;
END p2;
/
CREATE PROCEDURE p1 IS BEGIN
  p2;
END p1;
/

BEGIN
  p1;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(
      dbms_utility.format_error_backtrace
    );
END;
/

The above now outputs:

ORA-20000: Some Error
ORA-06512: at "PLAYGROUND.P4", line 2
ORA-06512: at "PLAYGROUND.P3", line 2
ORA-06512: at "PLAYGROUND.P2", line 2
ORA-06512: at "PLAYGROUND.P1", line 2
ORA-06512: at line 2

To learn more about the DBMS_UTILITY package, please consider the manual. True to the nature of all things called “UTILITY”, it really contains pretty much random things that you wouldn’t expect there :-)