Obsessed with Oracle PL/SQL

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