Obsessed with Oracle PL/SQL

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations: FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END betwnstr; I can then call it in a SQL statement: SELECT bewtnstr (last_name, 3, 6) FROM employees Nice, right? But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function. All of that takes time. And we'd much rather it didn't. Since...

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units.  These features include: Code-based access control: fine-tune access to database objects inside program units by granting roles to program units , rather than - or in addition to - roles granted to schemas. Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit. In part 1, I will explore the use of INHERIT [ANY] PRIVILEGES to clamp down on possible privilege escalation. Which means, of course, that I should first give you an example of what privilege escalation is, how it can come about, and what sorts of damage it can do. Suppose that there is a schema named POWERFUL_BOSS in the database instance, which is the boss's schema and has lots of privileges on many critical database objects, including the PERFORMANCE_REVIEWS table.  ...

We've been designing a new, modern, responsive UI for the taking quizzes on Oracle Database. The "old" site is the PL/SQL Challenge . The new site is the Oracle Dev Gym . At the beginning of each year, we hold championship tournaments for the top 50 ranked players in each of our focus areas (currently: SQL, PL/SQL, Database Design and Logic). This will be the first year in which you can choose to take the championship on the PL/SQL Challenge or  the Dev Gym.  So I thought it might be helpful to provide a tour of the Dev Gym's championship flow. If you qualified to play in a championship, you should have already received emails inviting you to confirm your participation in the championships. Once you have done that, you will see the championship on the Tournaments page, when it is within a week of the championship taking place. If you click on the championship card before it is time to start, you will see either: 1. The confirmation page, in...

As some of you may know, we've been working on a new "skin" for plsqlchallenge.oracle.com , one that is more modern and responsive, and that makes it easier to quickly take a quiz. The Oracle Dev Gym is still in an "early adaptor" state; you can take quizzes, including our weekly competitive quizzes, there. You can set up workouts and so forth. But we are not yet offering it as a day-by-day alternative to the "traditional" PL/SQL Challenge. Here's a quick update: 1. The URL devgym.oracle.com is now configured, so you can more easily go directly to the Oracle Dev Gym (instead of going "through" the PL/SQL Challenge. 2. We've come up with a simpler, more immediate design for our home page. Rather than having to choose "Take a Quiz" from the home page, and then go through another layer of selection from there, the home page now offers immediate access to quizzes. The previous home page: The new home page: ...

I've been publishing Oracle Magazine articles, blog posts and LiveSQL scripts on new PL/SQL features in Oracle Database 12c Release 2  (there, are those enough hyperlinks?). As have others. I thought it might be helpful to provide a single reference post from which you could check out all the others. I also include links to content from other experts who have posted on the same topics. I will update this post as more resources are published. First some overview articles that you will find as solid starting points: 12 Things Developers Will Love About Oracle Database 12c Release 2 , covering SQL and PL/SQL features, from Chris Saxon . It even comes with an infographic ! The Power of Cloud PL/SQL , my Oracle Magazine roundup article. No, I did not choose the title of the article. And now for specific enhancements.... PL/Scope Discovers SQL! PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in you...

Isn't that something you always wanted to do? :-) No, it's not. And I did that yesterday in my dev environment (well, of course, such a thing could never make it to production!). It is an enormous pain.  You press the Run button.  The process doesn't return in the usual 2 seconds. You think back over the changes you just made and feel sweat break out on your forehead. Because you can see right away what you did and.....oh, how could I be so stupid? Well, not stupid . Just in too much of a hurry. And careless. And over-confident. And thinking about too many things at once. You know, the sorts of things, "gurus" do all the time as a way of maintaining their high level of excellent to show to the world. :-( So yes, I did this yesterday, and I thought I'd share with you my mistake to hopefully help you avoid doing the same thing in the future. I am writing a program to automatically generate workouts for the Oracle Dev Gym (...