Tuesday, June 30, 2015

The Oracle Database Developer Choice Awards: We Need You!

Years ago, Oracle Corporation gave out Excellence Awards for developers. For example, I was honored to be chosen as "PL/SQL Developer of the Year" in both 2002 and 2006. But it's been a while since developers received those awards. The focus seems to have shifted more to C-level awards.

Well, hey, C-level folks need to feel appreciated, too.

But these days, application developers rule the roost (or, to be less playful word-wise, build the apps) and it's critical that we at Oracle both celebrate and help support more effectively the communities of developers building apps on top of Oracle Database.

That's why I put together the Oracle Developer Advocates team - and that's why Andy Mendehlson, Executive Vice President of Server Technologies at Oracle, authorized the establishment of a new awards program, under the sponsorship of the Oracle Technology Network: 

For 2015, awards will be given out in the following technology areas:

  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

This awards program is different from the Excellence Awards and different from the ACE program in a number of ways, the most important of which are:

Nominations come from the community (you).
Panels of ACEs decide on a set of finalists.
Winners are determined by popular vote (that is, by you).

In other words, as much as is possible for a program implemented by Oracle, Oracle will not be driving the process and selecting the winners. You will.

Another difference between the Dev Choice Awards and the earlier Excellence Awards is that while we are certainly looking for technical expertise in our winners, we are also prioritizing contributions to the Oracle Database community. 

For example: you might not be the ultimate expert in every nuance of SQL, but you organize weekly, free seminars on SQL to university students. You don't know everything about PL/SQL, but you spend lots of time on the SQL-PL/SQL forum helping newcomers be productive.

Because, after all, an expert is "just" someone who knows enough to teach you something new, right?

Nominations Open Through August 15

We encourage you to nominate an Oracle technologist you believe is deserving of this award. We are accepting nominations through August 15. Just provide her or his name, email address (if you are not sure, just put in yours! :-) ) and a description of why you think this person should win.

Panels Choose Finalists by September 15

A panel of judges, composed of Oracle ACEs and Oracle employees (our current plan is to limit Oracle employee involvement to just one person per technology panel) will then choose a set of finalists in each category.

You Vote for the Winners Sept 15 - Oct 15

The worldwide Oracle technologist community votes on the finalists from September 15 through October 15.

Winners Announced at YesSQL! Celebration at OOW15

The winners of the Oracle Database Developer Choice Awards will be announced at the YesSQL! Celebration during Oracle OpenWorld 2015. As for prizes, we have opted to emphasize recognition over monetary prizes. All winners will enjoy:
  • Feature Profile on the OTN Community Platform 
  • Feature Profile in the OTN Database Community Newsletter
  • Inclusion in a feature story in Oracle Magazine
  • Oracle Developer Choice Badge on the OTN Community Platform
  • Award points on Community Platform
  • Commemorative Plaque or Certificate
We Can't Do It Without You

It would be easier (and less risky) for us (Oracle) to keep total control of the process. No public nominations, no finalizing lists by ACEs, no popular vote for winners. 

Instead, we are relying on you, members of our technical community, to participate and to drive the process, and, ultimately, to vote. Only with enough nomination and enough votes will this awards program be successful, so please participate. Early and often, as we say in Chicago.

Help Oracle Build the Community!

This is your chance to shine and earn highly deserved recognition for yourself or your respected peers.

Please take a moment out of your day to think about people from the community who have had a positive impact on your Oracle development life. And remember: they do not have to be world famous!

One of the key objectives for this awards program is to increase awareness of the many, many Oracle technologists who are not yet widely known, but have deep expertise and are having a substantial impact in their region, or their online community, or their local user group.

So check out all the details about the Oracle Database Developer Choice Awards

Then visit our nominations page and tell us about your favorite contributors. 

It'll only take a few moments - and the impact on both the global community and the person you nominate could be substantial.

Steven Feuerstein

Thursday, June 18, 2015

Travel tip for Kscope15 and Beyond: Bring a water bottle!

I encourage everyone attending Kscope15 to take a vow:

I will not drink water from a disposable plastic bottle.

It won't be hard to find them. I imagine the conference organizers will provide them by the bushel.

But remember: every piece of plastic you consume will haunt the planet for hundreds, probably thousands of years.

The worst thing to use plastic for is something disposable.

And bottled water? Much of it is not even cleaner or "better" than tap water (though it will almost certainly have filtered out the nasty chlorine taste). Much of it is actually nothing more than filtered tap water (for example, Dasani-Coke).

Instead, bring a steel water bottle (best not to drink or eat out of plastic altogether - there are many inexpensive such bottles available via Amazon and so forth). Refill it as needed.

If lots of us foreswore plastic water bottles at Kscope15, we could reduce landfill and waste by hundreds of bottles. ODTUG would likely even save some money.

And if you forget or can't get hold of a reusable water bottle, no worries! Drink the water from your first and only disposable plastic bottle - and then reuse it for the rest of the conference!

On behalf of Planet Earth and all of its inhabitants, I thank you!

Tuesday, June 16, 2015

Checklist Driven Development: TDD on the cheap - VERY cheap

I've built (or helped build) two unit testing frameworks for PL/SQL over the years: utPLSQL (open source) and Code Tester for Oracle (Dell). I pushed long and hard with my audiences to follow test driven development, to build automated regression tests, etc.

The peak of my TDD/unit testing drive was represented by my "Six Simple Steps to Unit Testing Happiness." I had fun doing these presentations, and my audiences laughed at how I made fun of we all tested (or rather did not test). And I am sure that I did have some impact on behavior....but....

While I am still convinced of their value, I spend far less time promoting these ideas these days, mostly for the following three reasons:

1. I hate hypocrites (who doesn't?). And the hypocrite I detest more than any other? Me. Bottom line is that I did not / do not use either of those tools (nor SQL Developer's integrated unit testing) when I write code. Why? All the usual excuses I pooh-poohed in my talks: "Not enough time" "Hard to set up and tear down database state" and many more. At my advanced stage of life (I turn 57 in September), I am less tolerant of my own hypocrisies.

2. I don't believe it is possible to dramatically change the behavior of humans, not through lecturing them anyway. People who have been avoiding testing for years or decades (or, to be a bit kinder, unable to test due to lack of time and resources) are not likely to start. Barriers to entry are too high, by which I mean:

3. It's still too hard. utPLSQL requires writing too much code. Code Tester for Oracle and SQL Developer both generate a bunch of code, but defining a stable dataset for testing and refresh that dataset remain very off-putting tasks.

I try, these days, to live in the real world, to accept things as they are, and work from there. Doing full-blown test driven development (TDD) simply is not going to happen widely in the Oracle database programming world.

That doesn't mean, however, that we should just throw up our hands and say "Oh well, I'd love to 'do things right' but I just can't" and then fall back into our lovely "rapid application development": an absolute minimum of process, standards and collaboration, and the maximum possible number of short-cuts. (which we do all too eagerly, if we are really honest with ourselves)

No, no, no, we should not do that!

So here's where I get to the point of this post: I'd like to offer a simple, practical, eminently do-able "poor man's" version of TDD, one that anyone can do, one that doesn't take a lot of time to do, one that can give you some very quick positive feedback, thereby encouraging you to do it more and more:

Checklist Driven Development (ChLDD)

[Humbling note: we live in a post-modern world. So of course I am not the first to come up with this term and associated meaning. Check out Dan DeMeyere's blog. But hey! I am still blogging. He stopped "back" in 2014. So there.]

Assuming you are reasonably competent and want to write high quality code, I suggest one of your (our) biggest challenges is simply to remember what it is we need to do, and what we need to check for, when producing a new program.

So why a checklist?

Many surgeons - whose work is way more complicated and life-critical than ours - have discovered in the past few years that using a simple (almost ludicrously simple) checklist before starting surgery can have an enormous impact on success. Their checklist might feature questions like:

  • Check the patient's identity.
  • Confirm the limb you are supposed to remove. 
I know, I know. Seems crazy obvious, right? But it works, it really works. Which is a sad commentary on the medical system, sure. The main thing, however, is to focus on getting better. 

So....why wouldn't it work for developers, too? Here is my suggestion for "Checklist Driven Development" (specifically as it applies to a substitute for actual unit testing):

Before you are about to start writing a new program (or upgrading an existing one), ask yourself this question:

"How will I know when I am done?"

After all, you are going to start thinking and typing and pressing buttons, quite furiously, and then at some point, you are going to stop and say to yourself: "Good (enough). I am done. Now I can work on the next program."

How will you know when it's safe to stop, and move on?

All too often, the answer is "I'll know it when I see it." or "When I am done testing it." But so few us actually have any kind of test plan, so testing really comes down to "Try a few of the most obvious - to me - scenarios and that should be good enough."

Or if we do have a test plan, it is written after the program has been written. And that's a really, really bad idea. Here's why:

Ever try to proofread your own writing? It's nearly impossible to catch typos and grammatical errors. Since you wrote it, you know it too well, and your eyes/mind just skip over errors, automatically adjusting to what you expect to be there, rather than reading what actually is there. 

That's why you will hear the recommendation to proofread by reading the words in reverse. That way your brain can't glom onto the meaning. It has to process each word individually.

Kind of hard to do with code and, anyway, the compiler will take care of most typos.

But if you build your test plan after you "finish" writing the program, your mind will subconsciously focus on test cases for the parts of the program in which you have the most confidence. And that same mind of yours will veer away from the parts that you pretty well know are sub-par and likely to be socked full of bugs (primary rationalization here: "(Almost) no one's going to use those features, anyway." Yeah, uh huh.).

Don't fall for that simple trap!

Instead, build the checklist first

Right before you are going to start that next program, ask yourself: "What does this program have to be able to do in order for me to report to my manager that I am done?" 

Then open up an editor and put it into a document. It doesn't have to be anything fancy. Here's what I (pledge to) use (from now on):

This way, your list will not be affected (corrupted) by the fact that you only have five minutes to test when all is said and done. It will not be perverted by subconscious bias. You can, with a light heart and optimistic outlook, simply list the things the problem should do.

Here's an example of a specific checklist:

Is ChLDD an adequate substitute for real testing, unit or otherwise? Absolutely not. 

Is it better than launching oneself full speed into writing a program before you've thought through what is needed and how you are going to build it? I think so.

Could this be improved? Sure, you could add a section for defining behavior when errors occur, for example. But at least for this post and for my initial use, I want to keep it dead simple. Which means I can fill it out quickly and get immediate value for the time spent.

Let me know if you try it, and what your experience is like.

Monday, June 15, 2015

Welcome, Connor McDonald, to the Oracle Developer Advocates team!

I am very pleased to announce that as of 15 June 2015, Connor McDonald, ACE Director extraordinaire, recognized worldwide for both his depth of knowledge on Oracle Database and his excellent presentations, is now an Oracle Developer Advocate for SQL.

Welcome, Connor!

Connor McDonald has, over the past 18 years, worked with Oracle Database systems in Australia, the UK, Southeast Asia, Europe, and the United States. 

He has co-authored three books, and has been a popular speaker at Oracle conferences around the world, specializing in topics on the database engine, in particular SQL and PL/SQL. He has twice won the Inspirational Speaker award by the UK Oracle User Group in 2009 and 2011.  Whether speaking in a full day seminar to a room of developers, or just a "Hey, check out this cool feature" to the person sitting next to him, Connor has always loved sharing information about technology with others.  

While working for clients over the years, Connor realized that the most satisfying part of his job was seeing others grow and develop their skills in Oracle Database technology, helping them discover the amazing things of which the database engine is capable: so much more that simply the place where the bits and bytes are stored.  

When Connor saw the Oracle Developer Advocate team being formed, he asked himself: "So why not take this to the next logical step, and accomplish the same thing on a larger scale?"

Fortunately for Oracle and the Oracle Database user community, Connor answered that question with: "Yes, why not? Let's do it!" To which I agreed with great enthusiasm.

I greatly look forward to the presentations, videos, quizzes, trainings and more that Connor will be unleashing upon the world. 

As many of you likely know, Connor is tucked away in Perth, Australia, so if you are coming to Oracle Open World, be sure to take advantage of the opportunity to congratulate him in person. I sure will!

You can reach/follow Connor as follows:

Email: connor.s.mcdonald@oracle.com
Twitter: @connor_mc_d

Wednesday, June 10, 2015

PL/SQL Challenge Website Joining Oracle!

When I (re)joined Oracle in March 2014, the PL/SQL Challenge website was also acquired by Oracle. I'd been thinking that in a few months or so, we'd have it up and running on an Oracle server, re-branded with lots of red.

But then, well, I got kind of busy with all sorts of other stuff. My bad.

But I am very happy to announce that over the coming weekend (13-14 June)PL/SQL Challenge will go offline for hopefully no more than a few days and then resurface as an Oracle website.

And that's why for the first time in five years, we will not offer new, competitive quizzes on SQL or PL/SQL or anything else this coming week (we will still put up some of our "deja vu" quizzes). I don't want to set up quizzes and then not give you sufficient time to take them (and you never quite know what's going to happen so....).

Now, those of you who've been to the website know that we use lots of orange (why? Because it's a pleasant color and also is the thematic color of my Oracle PL/SQL books published by O'Reilly Media):

You are probably also very familiar with Oracle's use of red:

Well, do not worry - we are not going to replace all that orange with red. That would make the website unreadable, an assault on the eyes. But come 15 June, our banner will be transformed as follows:

Chills running down my spine....so exciting!

The site will be largely unchanged from current functioning. You will, however, need to accept the Oracle Terms of Use. In addition, since some players may have been using an email address on the PL/SQL Challenge different from their Oracle Single Sign-on email, we will give you an opportunity to synchronize the two accounts:

The "fine print" asks you to authorize us to transfer profile information from the PL/SQL Challenge to your Oracle Profile. We are asking for this, because the PL/SQL Challenge collected all sorts of profile information, some of which is already in your Oracle Profile, which is the "source of truth" at Oracle. So you will no longer be able to provide your name, country or company in the PL/SQL Challenge. Instead this information is stored in your Oracle Profile.

We plan over time to integrate points on the PL/SQL Challenge with OTN community rankings, but that may take a little while to complete. Do not worry, though! All your hard work and dedication on this site will be recognized.

I look forward to a greatly increased level of quiz-taking activity, as well as a broader array of quizzes offered, and I hope you do, too!

Thursday, June 4, 2015

Table Functions, Part 4: Streaming table functions

In my last post on table functions, I showed how I was able to reduce many Application Express interactive reports into one by pushing all the complexity into a table function, thereby reducing the query in the report to nothing more than a "parameterized view":

     qdb_rankings.ir_other_ranking_tf (category_in  => :p443_category,
                                period_type_in      => :p443_period_type,
                                competition_id_in   => :p443_competition_id,
                                period_in           => :p443_period)) pr

I hope you agree that this is a nice trade-off: keep the code in Application Express really simple, move the complexity to the backend.

Another common usage of table functions is to stream data directly from one process or transformation, to the next process without intermediate staging. Hence, a table function used in this way is called a streaming table function.

As you might be able to tell from the reference to transformation above, this technique is most often used in data warehouses. 

Before getting into the details of implementation, here's what such a streaming process might look like in SQL:

INSERT INTO tickertable
     FROM TABLE (stockpivot (CURSOR (SELECT *
                                       FROM stocktable)))

Here's an explanation of the transformation (I will show below all the details of the database objects referenced):
  1. Take all the data from the stocktable....
  2. Convert it into a cursor variable with the CURSOR expression....
  3. Pass that cursor variable to the stockpivot table function....
  4. The function returns a nested table of object type instances....
  5. The TABLE operator converts that collection into a relational table format....
  6. SELECT all the rows from that pseudo-table....
  7. Insert them into the ticker table.
And as the image indicates, you can perform multiple transformations, as in:

INSERT INTO ticker table
  FROM TABLE (ticker pivot (
                CURSOR (SELECT *
                          FROM TABLE(stockpivot (
                                  CURSOR (SELECT * 
                                            FROM stocktable

Hopefully that is enough to get you thinking about the possibilities. As for the code....here's the fairly artificial example I will implement:

The stocktable contains the open and close prices for each stock market ("ticker") symbol and date:

CREATE TABLE stocktable
   ticker        VARCHAR2 (20)
 , trade_date    DATE
 , open_price    NUMBER
 , close_price   NUMBER

I need to transform each row in stocktable to two rows in tickertable (one for the open price and another for the close price):

CREATE TABLE tickertable
   ticker      VARCHAR2 (20)
 , pricedate   DATE
 , pricetype   VARCHAR2 (1)
 , price       NUMBER

And I want to do it entirely in a single SQL statement.

Now here's where "artificial" comes into play: you do not need to use a table function to implement this transformation. The following SQL statement does the trick:

   INTO tickertable (ticker, trade_date, price_type, price) 

        values (ticker, trade_date, 'O', open_price)
   INTO tickertable (ticker, trade_date, price_type, price) 

        values (ticker, trade_date, 'C', close_price)
SELECT * FROM stocktable;

or you could use unpivot (thanks, Chris Saxon @chrisrsaxon, for this technique!):

insert into ticker table 
   (ticker, trade_date, price_type, price)
 select *
 from   stocktable
 unpivot (price for price_type in (
             open_price as 'O', close_price as 'C'))

But please assume for the sake of my blog post that the transformation is way more complex. In fact, let's be clear: the actual implementation of the transformation (table) function is obviously going to be application specific in the extreme. So I keep my transformation logic very simple and will zoom in on the key steps that you would take for whatever real-world transformation you need to implement.

As you surely know by now, a table function returns a collection. And as I explained in a earlier post in this series, when you need to return a collection whose elements contain more than a single scalar value, you need to create an object type for the datatype of the collection. So here goes:

/* Gee, looks just like the table! */

   (ticker VARCHAR2 (20)
  , pricedate DATE
  , pricetype VARCHAR2 (1)
  , price NUMBER

CREATE TYPE tickertype_nt AS TABLE OF tickertype;

So a collection of tickertypeset is what the function will be returning. But what will I pass into the function? When you are building a streaming table function, that generally means that you are feeding into the function the result set of a SELECT statement. Now, you cannot pass a SELECT statement itself as an argument to a function.

So, instead, I will use the CURSOR expression to instantiate a cursor variable that points to the SELECT's result set. To do that, I need to define a REF CURSOR type whose RETURN type matches the SELECT list of the query:

   TYPE refcur_t IS REF CURSOR
      RETURN stocktable%ROWTYPE;
END refcur_pkg;

And now I can write the header of my function:

      dataset refcur_pkg.refcur_t)
   RETURN tickertype_nt

I will also need to declare some local constants and variables:

   /* Avoid hard-coding the BULK COLLECT LIMIT 
      in the fetch statement */
   c_limit CONSTANT PLS_INTEGER := 100;

   /* Container for rows fetched from the cursor variable. */
   TYPE dataset_tt IS TABLE OF stocktable%ROWTYPE
                         INDEX BY PLS_INTEGER;
   l_dataset   dataset_tt;

   /* The nested table that will be returned. */
   retval      tickertype_nt := tickertype_nt ();

The body of function fetches N rows at a time, and then converts each of those single stocktable rows into two object types instances, each of which are added to the retval nested table. The code in purple is my very specific transformation logic. Yours will vary greatly and almost certainly be lots more complicated.

      /* Fetch next 100 rows. */
      FETCH dataset BULK COLLECT INTO l_dataset
         LIMIT c_limit;

      EXIT WHEN l_dataset.COUNT = 0;

      /* Iterate through each row.... */
      FOR l_row IN 1 .. l_dataset.COUNT
         /* First the opening price row */
         retval (retval.LAST) := 
            tickertype (
               l_dataset (l_row).ticker, 
               l_dataset (l_row).open_price,
               l_dataset (l_row).trade_date);         

         /* Next the closing price row */
         retval (retval.LAST) := 
            tickertype (
               l_dataset (l_row).ticker, 
               l_dataset (l_row).close_price,
               l_dataset (l_row).trade_date); 
      END LOOP;

   CLOSE dataset;

   RETURN retval;

There, that wasn't so hard, was it? :-)

But what if you are transforming lots and lots and LOTS of data, and you've invested in the Parallel Query option to speed things up?

Unfortunately, the default behavior of a PL/SQL function is that when you call it, your session halts, waiting for the function to return its data. In other words, it forces serialization. So if you want to use a table function in a parallelized query, you need to take the next step (in understanding and complexity) re: table functions and explore....

Pipelined table functions: one of the most interesting constructs in PL/SQL

And that, dear reader, is the subject of my next post in this series on table functions.

Links to Table Function Series

Tuesday, June 2, 2015

PL/SQL Brain Teaser: when does a COMMIT not commit?

So your users make changes to tables (with great care and security, through your PL/SQL API), your app calls this procedure and that function. At some point along the way, a COMMIT statement is executed successfully in a user's session.

Yet that same user's session still has uncommitted changes!

Huh? How is this possible?

Think you know? Comment below!

About terminating characters for SQL statements

I recently tweeted this link to an article you published in the November/December 2004 edition of Oracle Magazine.

Bryn Llewellyn, PL/SQL Product Manager, followed up with a message I thought we might all benefit from reading, so here goes:

A side box in the article ends with: "Dynamic PL/SQL statements must end in a semicolon; dynamic SQL statements may not end in a semicolon."

That's like saying << when wearing a red pullover, you must make subject and verb in a sentence agree in number; you must not write "myself" when you mean "I">>. Taken literally, it's not untrue. But the implication is that things are different when you wear a differently colored pullover. But, of course, they are not different.

It's just a property of SQL that it has no larger building block than the single statement. There is no need for a terminator character. And the use of one, whatever might take your fancy, causes a syntax error. This a universal truth. It has nothing to do with dynamic SQL.

The real point is the special nature of PL/SQL's embedded SQL. And, for that matter, the rules of the SQL*Plus scripting language.

It's terribly sad that almost every single example SQL statement that you see, anywhere on the planet, presents it as the SQL*Plus command that you'd use submit it in the regime you get following this SQL*Plus command:


I hate to see you encouraging wooly thinking with a rote rule rather than attempting to expose the correct mental model. Best practice, in any discipline, starts with sound understanding of basic concepts.

I recommend putting this in one's glogin.com:


The discipline that this will require will encourage the right way to think.