Obsessed with Oracle PL/SQL

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 d...

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 ...

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 documentation Videos 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 l...

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...

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), Python ,  REST 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 t...