Sunday, September 21, 2014

Coming down to earth at OOW14

Before I joined Oracle, I was honored to be an ACE Director for several years, and boy did I get spoiled. Especially at Oracle Open World time.

Oracle paid for my airfare and hotel. They set up the hotel reservation. They picked me up in (non-stretch) limo and delivered me to that hotel. They drove me back to the airport. Nice....

Then I rejoined Oracle in March of this year.

In late August, I was talking with a friend at Oracle and mentioned that I had yet to arrange my hotel.

"Whaaaat?" she practically screeched in the phone. "You don't have your hotel? Oh, Steven, you better get on that right away."

Turns out I had received an email on 7 July saying, in effect, "Congrats, Steven as Oracle employee. We have confirmed your registration for the now. NOW GO RESERVE YOUR HOTEL ROOM."

But I didn't notice that last part. Just filed the email away.

So I followed the advice of my friend, and went on-line to get my hotel reservation set up.

Guess what? I am staying in Emeryville, across the Bay. An hour long BART commute back and forth, that still leaves me 2 miles from the hotel. A big change in the daily dynamic. In the past, I could casually stroll from hotel to first session of day, or to a breakfast meeting. Then at the end of the day, I always knew the hotel was just a few minutes away.

Now, I must commute to OOW each day (and back at night). I will not have my hotel room as a place to return during the day. I will lug my backpack around all day. I will think about how late I want to stay at that "one more" event in the evening.

Yep, a care-free "celebrity" no more.

Kind of a relief, really. I never did feel very comfortable having a driver just for me (not that I ever turned down the airport pickup, though I often arranged to get back on my own).

And I always found it a little bit silly to be featured in the "Celebrity Seminar Series" when I did trainings for Oracle University.

I realize it's going to be tough, but I think I can do it. I can ride public transportation. I can pay more attention to my plan for the day and evening.

Or maybe I can find a friend with a hotel room at Union Square to crash with.

Friday, September 19, 2014

Use COLUMN_VALUE when selecting from scalar table function

Received this question today:

I don’t have a problem to select from collection when collection is based on objects with columns/attributes. What about a collection defined as:

TYPE list_of_numbers_t IS TABLE OF NUMBER;

What would be the column name when you select from the collection?

Short answer:


Longer answer: here's a script I used to demonstrate several different features of nested tables. See query at bottom.

CREATE OR REPLACE TYPE list_of_names_t


   happyfamily     list_of_names_t := list_of_names_t ();
   children        list_of_names_t := list_of_names_t ();
   grandchildren   list_of_names_t := list_of_names_t ();
   parents         list_of_names_t := list_of_names_t ();
   /* Can extend in "bulk" - 6 at once here */
   happyfamily.EXTEND (6);
   happyfamily (1) := 'Veva';
   happyfamily (2) := 'Chris';
   happyfamily (3) := 'Lauren';
   happyfamily (4) := 'Loey';
   happyfamily (5) := 'Eli';
   happyfamily (6) := 'Steven';
   /* Individual extends. */
   children (children.LAST) := 'Chris';
   children (children.LAST) := 'Eli';
   children (children.LAST) := 'Lauren';
   grandchildren (grandchildren.LAST) := 'Loey';

   /* Multiset operators on nested tables */
   parents :=
      (happyfamily MULTISET EXCEPT children)
         MULTISET EXCEPT grandchildren;

   FOR l_row IN 1 .. parents.COUNT
      DBMS_OUTPUT.put_line (parents (l_row));

   /* Use TABLE operator to apply SQL operations to
      a PL/SQL nested table */

   FOR rec IN (  SELECT COLUMN_VALUE family_name
                   FROM TABLE (happyfamily)
               ORDER BY family_name)
      DBMS_OUTPUT.put_line (rec.family_name);

And note that as of 12.1, you can use the TABLE operator with associative arrays as well!

Thursday, September 18, 2014

RTFM? KISS? Comment? Whatever, just get the code to work right!

We've been hitting a snag of late with new registrants at the PL/SQL Challenge. As with many sites, to ensure that a person's email is not being hijacked, we send an email with a verification URL.

It was working for quite a while, but then we noticed players reporting a bug:

On clicking the verification link am getting an error message like:
The PL/SQL Challenge website is temporarily unavailable.
Please try again later or join the PL/SQL Challenge twitter group:
Sorry for the inconvenience.

Ugh. Well, the site is certainly available. So what's going on? Turns out the verification URL is missing the all-important "/pls/":


should be this:,P34_CODE:...

Looked into the code and found:

   FUNCTION apex_website_url RETURN VARCHAR2
              WHEN qdb_config.prod_state THEN APEX_UTIL.HOST_URL 
              ELSE website_url ()
              || '/apex/';

Now, a few things immediately come to mind: 
  • Huh? Why the CASE statement? Why distinguish between PROD and another instance of the app? Or to put it another way: please document your code!
  • Glad to have that built-in (APEX_UTIL.HOST_URL), but what does it do, really?
  • Does it need to be this complicated?
  • Oh, and where is it used?
Let's take that last item first. Before changing any code currently in production use (or even still in dev, but potentially in use across the app), it is so important to find out where that code is used, and how.

So I did that. I found two "hits" - one that is used to generate the verification URL and a second used in a program that sends out emails with the previous week's quiz results. 

But I took the analysis a little bit further and found that the second usage was "vestigial". Probably did use it at some point, but currently the function is called and assigned to a variable, but that variable is not used.

OK, add that to my clean-up list.

And, very nicely, I now know that this function is only used in the buggy scenario I am trying to fix. That gives me much more latitude to make changes.

So back to the apex_website_url. Since I am relying on a built-in, I suppose it might now hurt to actually look at the documentation. Where I find the following:

Well, woudja look at that....HOST_URL takes a parameter! And one value for that parameter is "SCRIPT" and if I use that, it automatically appends /pls/apex.

Confession: when I saw this I felt dumb. Or maybe it made me feel once again (happens pretty often) like the "closet amateur programmer" I believe myself to be (never studied computer science or algorithms or just about anything CS related in college). 

So I thought to myself: maybe, just maybe, I could change my function to nothing more than:

   FUNCTION apex_website_url RETURN VARCHAR2

I made the change. I tested the change. It worked!

THEN I moved it into production - and immediately tested again. Still worked. (ha! Who's the amateur now?). 

Another day, another problem (of our own making) solved.

Just one mystery remains: why did the verification email STOP working in mid September? Perhaps I will report back to you on that later. :-) 

This little episode was such a great reminder of some of the most important pieces of advice I can give to myself:

1. Read the documentation. Don't assume. Make sure you fully leverage all that the software vendor has done for you.

2. Keep your code as simple as possible. 

3. If for some reason you have to make it complicated, explain that complication with a comment.

Tuesday, September 16, 2014

Received this note from a PL/SQL developer today:

I have an idea to build the database of my application in such a way that all DML operations for the application are managed at the back end. There should be generic functions/procedures that will perform the DML operations. Also there should be functions developed that will provide the "Select" data. In short, I want to remove all sort of DML from front end and bring them to back end. In case of any DB structural changes, we should not require a large development effort. For example, if we have to add couple of columns to a table then it should be easily manageable/configurable in order to minimize the development effort cost.

This is the sort of note that makes me happy.

I have proposed for years to Oracle Database developers that we should see SQL as a service that is provided to us, not something we should write over and over and over again in our "high level" application code (as opposed to low level generic utilities like error loggers). 

After all, every SQL statement is a hard-coding just like every literal is a hard-coding.

And now I can all hear you saying, as my granddaughter is wont to say: "Whaaaaat?!"

Yes, every SQL statement is a hard-coding, and they are far worse and more dangerous than the hard- coding of literals.

In both cases, we make the assumption that this thing (a numeric value or a 6 way join) not only "works" (is accurate) today but will stay the same in the future.


Ha, ha!

What a funny joke!

Of course it's all going to change. Now, if you hard-code the literal 76034 in your application in 5,000 places, no big deal, really. Just do a global search and replace in your code. 

There is very little chance you will change anything but the literal value of interest.

[Of course, little chance and no chance are two different things entirely. So when doing a GSAR, please do visually check each change. Years ago, right after I left Oracle, I worked as a consultant in the HQ of one of the biggest "fast food" chains in the world. I fixed a bug and "while I was at it" optimized some other code (or so I thought). Passed it on to QA, went into production on Sunday, and on Wednesday they had to roll back three days of production activity. Why? Because my GSAR had change "WHERE X = Y" into "WHERE X = X" - I won't bore you with the details or why the actual change didn't seem quite as stupid as that. The good news? I was not blamed. It was the QA team's fault. Ha! Ha, ha!]

But what if you repeat the same or very similar 6-way join in 3 or 5 places? How can you find and upgrade all those "old" SQL statements?

Bottom line: if you do not build a layer of code around your data, essentially around your SQL statements, you end up with an out of control, hard to optimize, impossible to test and maintain application. Yuch.

So use views to hide complex and often needed WHERE clauses. Build packages to hide transactions and queries and non-query DML operations.

And, ideally, you should be able to generate lots of this code. I have, in the past, build and offered really powerful code generators including the incredibly awfully named QNXO and its descendent, Quest CodeGen Utility. Neither, sadly, are available anymore. But I hope in the next year to offer a simpler substitute that could be used very flexibly.

In the meantime, the next time you start to write a SELECT directly inside your high-level code, stop and ask yourself: Might I or someone else need this elsewhere? Might I someday want to use the result cache feature on this query?"

Then build a package and put the query inside a function (or add it to an existing package). Return a record or a collection, as is appropriate. And call that function. Your code is more robust and you spend less time writing and debugging that code.

For example, don't do this:

CREATE OR REPLACE PROCEDURE do_stuff_with_employee (
   employee_id_in IN employees.employee_id%TYPE)
   l_employee   employees%ROWTYPE;
   l_manager   employees%ROWTYPE;
     INTO l_employee
     FROM employees
    WHERE employee_id = employee_id_in;
   /* Do some stuff... and then, again! */

     INTO l_manager
     FROM employees
    WHERE employee_id = l_employee.manager_id;   

do this:

   FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
      RETURN employees%ROWTYPE;

   FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
      RETURN employees%ROWTYPE
      onerow_rec   employees%ROWTYPE;
      SELECT *
        INTO onerow_rec
        FROM employees
       WHERE employee_id = employee_id_in;

      RETURN onerow_rec;
CREATE OR REPLACE PROCEDURE do_stuff_with_employee (
   employee_id_in IN employees.employee_id%type)
   l_employee   employees%ROWTYPE;
   l_manager   employees%ROWTYPE;
   l_employee := employees_sql.onerow (employee_id_in);
   /* Do some stuff... and then, again, but now 
      just a function call! */

   l_manager := employees_sql.onerow (l_employee.manager_id);

Wednesday, September 3, 2014

All My OOW14 Sessions

For those of you attending OOW14 and interested in PL/SQL-related sessions, here are mine. You can add them to your schedule via Schedule Builder.

Session ID: CON9027
Session Title: YesSQL! A Celebration of SQL and PL/SQL
Venue / Room: Moscone South - 103
Date and Time: 9/29/14, 18:30 - 20:00

Session ID: CON8450
Session Title: SQL (and PL/SQL) Tuning Experts Panel
Venue / Room: Moscone South - 306
Date and Time: 9/30/14, 17:00 - 17:45

Session ID: CON7828
Session Title: The Whys and Wherefores of New Oracle Database 12c PL/SQL Features
Venue / Room: Moscone South - 103
Date and Time: 10/1/14, 15:30 - 16:15

Session ID: CON8265
Session Title: PL/SQL: The Scripting Language Liberator
Venue / Room: Moscone South - 307
Date and Time: 10/1/14, 12:45 - 13:30