Obsessed with Oracle PL/SQL

Thirty-six Oracle Database technologists competed on March 22nd in the 2017 PL/SQL Annual Championship at the Oracle Dev Gym . With five tough quizzes by yours truly, the competition was fierce! Congratulations first and foremost to our top-ranked players: 1st Place:  li_bao  of Russia 2nd Place:  mentzel.iudith  of Israel 3rd Place:  NielsHecker  of Germany Next, congratulations to everyone who played in the championship. We hope you found it entertaining, challenging and educational. Finally, our deepest gratitude to our reviewer, Elic, who has once again performed an invaluable service to our community. In the table below of results for this championship, the number next to the player's name is the number of times that player has participated in a championship. Below that table, you will find another list showing the championship history of each of these players. Rank Name Total Time % Correct Total Score 1 li_ba...

As anyone who has followed me over the years knows, I like the Oracle PL/SQL language. Sure, it's not the newest, coolest kid on the block (it probably never  was). But then, either am I. :-) PL/SQL is, on the other hand, a delightfully straightforward, easy to learn and write language that serves its purpose well: implement APIs to data (SQL) and business logic, right inside the database. To serve that purpose, of course, PL/SQL needs to support lots of "big ticket" functionality: super-smooth and easy native dynamic SQL , canonicalization of static SQL to minimize the need for hard-parsing, invoker rights (AUTHID CURRENT_USER) and so much more. But I must confess: the features of PL/SQL that I love the best are the relatively "little" things that make it easy for me to be productive as I churn out the packages (and, yes, I still do write lots of PL/SQL code, most lately for the Oracle Dev Gym , an "active learning" website featuring quizzes, wor...

Dynamic SQL means a SQL statement that is constructed, parsed and executed "dynamically" at run time (vs. "statically" at compile time). It's very easy to write static SQL in PL/SQL program units (one of the great joys of working with this database programming language). It's also quite easy to implement dynamic SQL requirements in PL/SQL. But that doesn't mean you should . The bottom line regarding dynamic SQL is: Construct and execute SQL at runtime only when you have to. There are several good reasons to avoid unnecessary dynamic SQL: Security : dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data. Performance : while the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL. Maintainability : the code you write to support dynamic SQL is more - literally more code - and harder to understand and maintain. Sometime...

I received this question yesterday: Is there a shortcut for referencing package variables in the package body? In Java, as an example, you can use the "this" keyword as a reference to the current object. This came about as I needed to create a copy of a package for debug purposes and realized I had to rename all the references to the package name within the package body. Before I answer, let's look at an example of what Tony is talking about. I create a package specification and then a body with package-level variable (declared outside any subprogram of the package): CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc (n_in IN NUMBER); END; / CREATE OR REPLACE PACKAGE BODY pkg IS smallest NUMBER; PROCEDURE proc (n_in IN NUMBER) IS BEGIN IF n_in < pkg.smallest THEN DBMS_OUTPUT.put_line ('too small'); END IF; END; END; / Notice the line in blue and bold . I reference the package level variab...

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL? ] Right? Good. And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist ] Right? Good. So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND): It works pretty much like COUNT in SQL. If the collection is empty, COUNT returns 0, not NULL. If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows. If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND. To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gy...

The best way to build applications on top of Oracle Database is to build lots of APIs (application programmatic interfaces) to your data with PL/SQL packages. And that means you end up with lots of packages in your application. That's just great! Now, when a user selects a feature of your application that in turn references an element in a package (invokes a procedure or function, or reads the value of a constant), that package must be instantiated and initialized for that user's session. As described in the documentation : When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.  When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable: Assigning initial values to public constants Assigning initial values to public variables whose declarations specify them Executing t...

When a SQL statement executes a user-defined function, your users pay the price of a context switch , which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues. Fortunately, you can use PL/Scope in  Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there. I go through the steps below. You can run and download all the code on LiveSQL . First, I turn on the gathering of PL/Scope data in my session: ALTER SESSION SET plscope_settings='identifiers:all, statements:all' / Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope: CREATE TABLE my_data (n NUMBER) / CREATE OR REPLACE FUNCTION my_function1 RETURN NUMBER AUTHID DEFINER IS BEGIN RETURN 1; END; / CREATE OR REPLACE FUNCTION my_function2 ...

Copyright @2017 CodeNewsWire "News coders can use", dateline 1 April 2017 Wikileaks dumped its latest batch of revelations on the world on April 1, 2017, this time focusing on the world of software programming. From dishing out the dirt on the origins of the Internet (think: Area 51) to emails candidly deriding JSON as nothing more than the latest attempt (XML being the last one) to avoid carefully designing your database, this trove of previously secret secrets is sure to keep Silicon Valley gossiping for months. But buried deep within the 2.5 trillion byte download is evidence of a conspiracy so vast, so unbelievable, so extraordinary, that it is hard, well, to believe. But if it came from Wikileaks it must be true. And that conspiracy was built around - and is maintained around - this incredible bit: All modern programming languages, from Java to JavaScript, Scala to Go, are actually all implemented in the Oracle PL/SQL language. Oh, and Linux, too. You are probab...

Way back in Oracle Database 12c Release 1, the PL/SQL team added whitelisting to the language. This means you can use the ACCESSIBLE BY clause to specify the "white list" of program units that are allowed to invoke another program unit (schema-level procedure, function, package). For details on the 12.1 ACCESSIBLE BY feature set, check out my  Oracle Magazine  article,  When Packages Need to Lose Weight . In that article, I step through the process of breaking up a large package body into "sub" packages whose access is restricted through use of the ACCESSIBLE BY  feature. I'll wait while you read the article. Tick, tock, tick, tock.... OK, all caught up now? Great! In 12.2, there are just one enhancement, but a really nice one: You can now specify whitelisting for a subprogram within a package.  This is a very nice fine-tuning and is sure to come in handy. In this post, I also show how you can specify the "unit kind" (program unit type) ...

Here goes: I execute this statement: CREATE OR REPLACE FUNCTION ndf RETURN NUMBER IS BEGIN RAISE NO_DATA_FOUND; END; / We all know what that function is going to do, right? #Fail, as one might say on Twitter. So the brain teaser is:  I n the block below, replace <statement> with a single statement that does not contain any exception handling, but does call the NDF function, so that after the block executes, "NDF? What NDF?" is displayed on the screen. DECLARE n NUMBER; BEGIN <statement> DBMS_OUTPUT.PUT_LINE ( 'NDF? What NDF?' ); END; / I will wait a bit to post my answer. I encourage you to post yours as a comment to this blog post. Wait.... Wait.... Wait for it.... OK! After a couple of days of collecting responses on this post and also in LinkedIn, it's time to publish the answer: You can replace <statement> with any DML statement that executes the function, and the failure of the function with an unh...

Just received this feedback on an Oracle Magazine article : I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments. Now, I must confess that I am tempted to respond with such questions along these lines: Did you try it out? That's the best way to learn! But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-) The answer is quite straightforward: Comments do not interfere with "pinpointing the exact line number" on which the error was raised. But I bet you want proof, so let's get down to business. I create a procedure with comments and a RAISE statement: CREATE OR REPLACE PROCEDURE comments_throw_off_line# IS /* Here's my header Blah blah blah */ BEGIN DBMS_OU...

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL . It's very cool and a lot of fun to put together. Hopefully it will be way more  fun for you to see if you can solve our challenges. Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality. Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out. First, though, if you haven't done anything  with JSON yet, start by looking over these: JSON in Oracle Database JSON Support in Oracle Database 12c Release 1 (12.1.0.2) Plus, all the code shown below can be seen, and run, in LiveSQL ...

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language. And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years. In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility.  In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past." If a tree falls in a forest and no one is around to hear it, does it make a sound? You've heard that before, right? Well, how about this one: If you write a prog...