Obsessed with Oracle PL/SQL

Back in April 2010, I started (with the help of many others!) the PL/SQL Challenge , which offered daily quizzes on PL/SQL, along with weekly quizzes on other technologies. In 2014, Oracle acquired the website and I suspended the daily quiz. Writing five new quizzes each week is a lot of work, and I had a big stack of new responsibilities! But the weekly quizzes continue(d): SQL, PL/SQL, Database Design, Logic and most recently a re-started weekly quiz on Oracle Application Express, made possible by Insum . And Oracle Database technologists kept on using the site to hone their skills. We are rapidly approaching 1,000,000 answers on the site. That all sounds good, right? So why is the title of this post "Check out the Oracle Dev Gym!"? Because the PL/SQL Challenge has, for a while, been in dire need or a "reboot"; not just a redesign built around the massively improved APEX5 , but also a rethinking of how this quiz platform could best serve the needs of the co...

I've published several posts on table functions . Here's another one! Why? A few days ago I tweeted the following: Two of my followers immediately asked to hear the story: "those are the fun ones, but pics or it didnt happen ;-)" and "may be interesting to view the step you do to find solutions more then result." Very good points; I especially agree with the latter. As I was going through the revisions to my code, I was thinking (as I have often done before): "It might be helpful to show the process I go through, because it sure feels like a powerful, positive way to build and improve code." The problems I run into when actually sitting down to tell the story are: 1. Time: yes, I know, we are all busy. Plus, isn't it my job to share thoughts on PL/SQL programming?  Yes it is! Well, part of my job, anyway. And I have  been neglecting my blog. But right now, at this moment, I am very focused on finishing the "early adap...

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display...

I've gotten a request or two to recommend places to eat and drink while in Chicago for Kscope16 . Confession: I am a home body, a creature of habit, and definitely not a downtown body. So....I don't have much of any ideas to offer in terms of eating around the conference hotel downtown. But I can certainly share with you some of my favorite restaurants on the north side of Chicago. Italian Anna Maria's Pasteria  - lovely, small, neighborhood restaurant Via Venetto  - I am not sure I have ever tasted anything better than their  Fagottini Di Ricotta E Pere   Mediterranean Andie's  - in the heart of Andersonville, we've been going to this restaurant for 20+ years. Seafood Glenn's Diner  - excellent seafood, not too expensive, small diner experience Mariscos El Venero  - Mexican seafood restaurant where Mexicans go. Nothing fancy, but amazing food and great experience overall! Indian If you really like Indian food and/or culture, you...

Let's take a look at the different ways you can define and use cursors (pointers to SQL result sets) in PL/SQL, including: implicit cursor, explicit cursor, cursor expressions, cursor variables, DBMS_SQL cursor handles.   Check out the LiveSQL script that demonstrates the points made in this post. SELECT-INTO (aka, Implicit Cursor) The SELECT-INTO statement is called an implicit  cursor, because we write the SELECT statement, and the PL/SQL engine takes care of all the cursor operations for us implicitly : open, fetch, close. SELECT-INTO is the best (smallest amount of code, best performance) way to fetch a single row. Here's an example, based on the standard HR schema . PACKAGE BODY employee_mgr IS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN hr.employees%ROWTYPE IS onerow_rec hr.employees%ROWTYPE; BEGIN SELECT * INTO onerow_rec FROM employees WHERE employee_id = employee_id_in; RE...

Received this request via email today: Hi Steven, in  our shop we have an ongoing debate regarding when to use packages and when to create a collection of procedures/functions.   We are not aligned at all, some people here tend to ignore packages for some strange reasons, I would like to nudge them in the right direction, so that where you come into play.     One of my co workers tried to look into a couple of you books to your argumentation for use of packages, and could not, to her astonishment, see any recommendations from you regarding this, can you point us in the right direction, surely you must have debated this issue somewhere. This came as a bit of a surprise to me (inability to find recommendations from me on packages). So I checked, and quickly found and reported back: In my humongous 6th edition Oracle PL/SQL Programming , there is a whole chapter on packages (18) and on 651 I offer “Why Packages?”.    In my Best Practices book I have...

On the PL/SQL Challenge , when you click on a link to play a quiz, you are taken to the "launch" page. We give you an opportunity to review assumptions and instructions, and then press the Start button when you are ready (your score is based in part on the time it takes you to answer). However, if you've taken that particular quiz before, and there have been no changes to assumptions or instructions, the launch page just gets in the way. So I decided to streamline the flow on our site as follows: 1. If a person has never taken this quiz before, go to the launch page. 2. Otherwise, if assumptions or instructions have changed since the last playing of the quiz, go to the launch page. 3. Otherwise, go straight to the Play Quiz page. I figured the way to do this is build a function that will be invoked from Oracle Application Express. Here is a first pass, using the top-down design technique, at implementing the function. CREATE OR REPLACE PACKAGE q...