Infinite Query

This is the query that never ends,
It just goes on and on, my friends.
Some people started fetching not knowing what it was,
And now they can’t stop fetching forever just because…

This is the query that never ends,

CREATE TYPE number_table_type IS TABLE OF NUMBER;

CREATE FUNCTION row_generator
RETURN number_table_type
PIPELINED IS
BEGIN
  LOOP
    FOR i IN 1..100 LOOP
      PIPE ROW (i);
    END LOOP;
  END LOOP;
  RETURN;
END;

SELECT * FROM TABLE(row_generator);

…inspired by…


My First Quiz

I was pleased to see my PL/SQL quiz question presented yesterday. It was about how PL/SQL variable names and other identifiers whose names conflict with names of tables and columns can still be referenced in SQL within PL/SQL.

It demonstrates how a local variable or parameter of a procedure or function may be referred to unambiguously, by referring to it by the name of the procedure or function, e.g.:

FUNCTION getempid (employee_id IN plch_employees.employee_id%TYPE)
   RETURN plch_employees.employee_id%TYPE
IS
   the_id   plch_employees.employee_id%TYPE;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.the_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;
   RETURN getempid.the_id;
END getempid;

(notice that the INTO and RETURN parts don’t actually need to reference the function name to refer to the local variable, but I’ve done it this way for clarity)

In the case of an anonymous block, it is necessary to supply a label for the block:

<<getempid>>
DECLARE
   employee_id   plch_employees.employee_id%TYPE := 100;
BEGIN
   SELECT plch_employees.employee_id
     INTO getempid.employee_id
     FROM plch_employees
    WHERE plch_employees.employee_id = getempid.employee_id;

   DBMS_OUTPUT.put_line (getempid.employee_id);
END getempid;

The quiz answers also explore what happens if a variable name conflicts with a column name, and the identifiers are not referenced; another answer considers the case where the block label happens to conflict with a table name.

I heartily recommend thinking about and writing your own quiz questions and submitting them for Steven to consider for the Challenge. If you do, here are some tips:

  • Focus on one topic – remove any irrelevant details
  • Build a full test case and run it (and re-run it carefully after every single change you make!)
  • Try to remove as much code as possible (without ruining it)
  • Re-read the question the next day, and imagine being a typical Challenger, who nitpicks every single statement 🙂

To Exist or Not To Exist

An interesting discussion on the PL/SQL Challenge blog here has led to me changing my mind about “the best way” to loop through a sparse PL/SQL associative array.

Normally, if we know that an array has been filled, with no gaps in indices, we would use a simple FOR LOOP:

DECLARE
  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  a t;
BEGIN
  SELECT x BULK COLLECT INTO a FROM mytable;
  FOR i IN a.FIRST..a.LAST LOOP
    -- process a(i)
  END LOOP;
END;

If, however, the array may be sparsely filled (i.e. there might be one or more gaps in the sequence), this was “the correct way” to loop through it:

Method A (First/Next)

DECLARE
  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  a t;
  i BINARY_INTEGER;
BEGIN
  ...
  i := a.FIRST;
  LOOP
    EXIT WHEN i IS NULL;
    -- process a(i)
    i := a.NEXT(i);
  END LOOP;
END;

Method A takes advantage of the fact that an associative array in Oracle is implemented internally as a linked list – the fastest way to “skip over” any gaps is to call the NEXT operator on the list for a given index.

Alternatively, one could still just loop through all the indices from the first to the last index; but the problem with this approach is that if an index is not found in the array, it will raise the NO_DATA_FOUND exception. Well, Method B simply catches the exception:

Method B (Handle NDF)

DECLARE
  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  a t;
BEGIN
  ...
  FOR i IN a.FIRST..a.LAST LOOP
    BEGIN
      -- process a(i)
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        NULL;
    END;
  END LOOP;
END;

This code effectively works the same (with one important proviso*) as Method A. The difference, however, is in terms of relative performance. This method is much faster than Method A, if the array is relatively dense. If the array is relatively sparse, Method A is faster.

* It must be remembered that the NO_DATA_FOUND exception may be raised by a number of different statements in a program: if you use code like this, you must make sure that the exception was only raised by the attempt to access a(i), and not by some other code!

A third option is to loop through as in Method B, but call the EXISTS method on the array to check if the index is found, instead of relying on the NO_DATA_FOUND exception.

Method C (EXISTS)

DECLARE
  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  a t;
BEGIN
  ...
  FOR i IN a.FIRST..a.LAST LOOP
    IF a.EXISTS(i) THEN
      -- process a(i)
    END IF;
  END LOOP;
END;

The problem with this approach is that it effectively checks the existence of i in the array twice: once for the EXISTS check, and if found, again when actually referencing a(i). For a large array which is densely populated, depending on what processing is being done inside the loop, this could have a measurable impact on performance.

Bottom line: there is no “one right way” to loop through a sparse associative array. But there are some rules-of-thumb about performance we can take away:

  1. When the array is likely often very sparsely populated with a large index range, use Method A (First/Next).
  2. When the array is likely often very densely populated with a large number of elements, use Method B (Handle NDF). But watch how you catch the NO_DATA_FOUND exception!
  3. If you’re not sure, I’d tend towards Method A (First/Next) until performance problems are actually evident.

You probably noticed that I haven’t backed up any of these claims about performance with actual tests or results. You will find some in the comments to the afore-mentioned PL/SQL Challenge blog post; but I encourage you to log into a sandpit Oracle environment and test it yourself.


InSync10 Day 2

Another good day in Melbourne. Heard Richard Foote talk about Indexing New Features in Oracle 11g release 1 and 2. One thing he demonstrated was the creation of an index on only part of a table – normally I’d use a function-based index for this sort of thing, but his technique results in an index that is useful without adding strange predicates to all relevant queries in the application; it involves creating a globally partitioned index, in an UNUSABLE state, then rebuilding only selected partitions. This could be very useful for customers who have the partitioning option.

Of interest to me was Discovering the Power to Save the Planet, presented by Robin Eckermann (Smart Grid Australia) – having worked for a short time at Western Power, it was interesting to hear his perspective on the future of the generation and distribution of power. He compared the state of the art in power to broadband, as it was 15 years ago – and asserts that the smart grid will enable all sorts of new applications for customers to regulate their demand intelligently, and is essential for the coming wave of electric cars.

After that was Steven Feuerstein’s second talk, “Golden Rules for Developers“, which was well worth a good listen. I recommend you download and read the powerpoint if you missed it. If you take even just one of his recommendations (e.g. Don’t Repeat Anything, Don’t Take Shortcuts, Build On A Foundation, Don’t Code Alone), I think you will improve the quality of your code, reduce the cost of maintenance for your employer/client, and be much more satisfied with your work. I certainly intend to – I’ve been guilty of “starting from scratch” many times – I do carry around a portable hard drive with a large collection of bits and pieces I’ve collected along the way, but nothing I can just plug in and use with confidence. Steven also gave another PL/SQL talk at the end of the day, this time for DBAs, and that was interesting to me (as a developer). If you’re a DBA, but think that you have no need for PL/SQL, think again.

After that, during lunch, Steven announced the winners of the previous day’s quiz – and wouldn’t you know it, I won 🙂


InSync10 Day 1

After a scrumptious breakfast at the Armoury I headed in what I believed was the general direction of the Melbourne Convention Centre – after making a wrong turn I eventually spotted a footbridge over the river that rung a bell from my GoogleEarthing; after taking some photos I was finally at InSync10.

The first session was Connor McDonald’s 11g Features for Developers, which was an eclectic mix of bits and pieces you won’t get from reading the New Features Guide or from Oracle Marketing, along with some gratuitous use of photos of his kids.

Steven Feuerstein didn’t present next, instead he made us think by running a Developer Quiz. Much like the PL/SQL Challenge (at which, by the way, you should sign up this instant if you haven’t already), it was fun and challenging, and I suspect everyone learned at least one new thing. Me, I learned what SUBSTR returns if the 2nd parameter (which normally starts at 1) is zero. As always, Steven was completely open to criticism, and with Connor and Tom in the room he certainly didn’t get off scot free 🙂

As it happened, I happened to disagree on one question, which was regarding the USING clause and how many bind variables must be supplied to a given statement. One of the responses (from memory) was that “you must always supply as many bind variables as there are placeholders”. I knew that if the statement being executed was SQL, the number of bind variables must match the number of placeholders, even if some of them have the same names (e.g. INSERT INTO emp VALUES (:a, :b, :a, :b) would require four bind variables). However, I also knew that if the statement is a PL/SQL block, each unique placeholder requires a different bind variable – if the placeholder appears more than once in the block, you don’t repeat the bind variable in the USING clause. I therefore ticked this answer as “correct” – if, for example, the block was BEGIN call_something(:a, :b, :a, :b); END;, you would have to provide two bind variables, because that is how many distinct placeholders there are in the block.

There was some discussion about this, because the answer was marked incorrect – according to Steven the number of placeholders in the block above is four, not two – and I agree that the meaning of a “placeholder” is different to a “bind variable”, although I usually speak as if to conflate the two ideas. However, I still hold to the opinion that a “placeholder” in the context of a PL/SQL block is a reference to this: :a, and I would say that the one placeholder :a appears twice in the PL/SQL block. I believe I have the documentation to back me up:

If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the USING clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the USING clause. In Example 7-7, all references to the placeholder x are associated with the first bind argument a, and the second unique placeholder y is associated with the second bind argument b.

(emphasis added) Source: Using Duplicate Placeholders with Dynamic SQL

This is really just an argument over semantics, so no big deal. Some of the other questions had much more interesting discussion, so it was well worth attending. If you’re in Perth on Friday, Steven is running it again (I won’t be able to attend, unfortunately). I presume he will be using different questions…

After that I went for a walk through Melbourne, since it was sunny outside. The climate in Melbourne, I discovered, is a tad different to Perth. Wandering along the riverside, I ended up experiencing a blast of all four seasons within the space of an hour – a lovely spring breeze, a somewhat warmish summer, then a cold blustering windy autumn (a bit out of order that) – there was a few seconds where it was difficult to remain upright – followed by a sudden rainstorm. I managed to find shelter under one of the many bridges that cross the river, waited for about ten minutes, then was able to walk back to the centre without getting any wetter. In fact, by the time I got back to the convention centre it was sunny again.

After a light lunch it was my turn to talk, and I think my presentation on APEX Themes and Templates went quite well. I appreciated the comments and questions that came back, and had some further discussion with a few people afterward as well, which was good.

I forwent Connor’s excellent Partitioning presentation which I’ve heard before, instead heard Kyle Hayle – Database Performance Made Easy – demonstrate the virtues of database tuning using a tool such as the one he’s produced at Embarcadero. I haven’t made use of many graphical tuning tools before, preferring just “the numbers”, but Kyle made an excellent case for the use of pictures instead of words for not only visualising the workload on the database (such as presented by Oracle’s Enterprise Manager, which Kyle had a hand in), but also for visualising the structure of a query. Personally, I’ve grown accustomed to using the traditional explain plan and I suspect I’ll probably continue to, but the Embarcadero product does have some features that automate some of the work I’d normally do by hand (such as examining the constraints on the tables and obtaining filter percentages).

Last of all, Tom Kyte presented The Best Way, in which he laid to rest for once and for all the answer to the age-old (and oft-repeated) question, “what is The Best Way to …?”. Finally, we can stop arguing over which way is worthy of being called Best Practice, and get on with the job 😉

Went out for a nice dinner at a small japanese restaurant, which had a great cozy atmosphere, and on the way back to the hotel was surprised by these great explosions of flame from these pillars. I could feel the heat from hundreds of meters away. At the end, a quick stop at a store allowed me to procure what I’d been coveting all day: Farmer’s Union Iced Coffee.


In Oracle, ” = NULL but NULL != ”

When I get the result of my PL/SQL quiz for the day, I’m pleased when I got it right, but if I got it wrong, I’m either annoyed or overjoyed:

  1. If I disagreed with the result, I’m annoyed.
  2. If I agreed with the result, I’m overjoyed – because I learned something new, or I was reminded of something I should have remembered.

Option #2 was my experience this morning – yesterday’s quiz featured the following code snippet:

...
   EXECUTE IMMEDIATE 'update my_table set my_column = :value'
   USING NULL;
...

This was one of four other, very similar, options – and I failed to notice that this version was binding NULL directly into the statement, instead of using a variable as any normal, reasonable, rational human being would. This snippet raises PLS-00457: expressions have to be of SQL types, which in this case is due to the fact that NULL is of no particular SQL type.

If one wanted to bind a literal NULL into a statement such as the one above, you don’t necessarily need a variable:

...
   EXECUTE IMMEDIATE 'update my_table set my_column = :value'
   USING '';
...

Proving that while is NULL, NULL is not – they are not always interchangeable.

P.S. please ignore the post title – I know it is incorrect to write ” = NULL or NULL != ” – but it wasn’t meant to be code, ok?


Quick Draw Fail

It rarely pays to answer too quickly.

Yesterday’s question at the PL/SQL Challenge was about aliases for expressions in cursor-based record types.

DECLARE
   CURSOR profitable_internet_stocks
   IS
      SELECT SUM (share_price) FROM nasdaq_listings
       WHERE profits > 0 AND sector = 'INTERNET';
   few_and_far_in_between   profitable_internet_stocks%ROWTYPE;
BEGIN
   OPEN profitable_internet_stocks;
   FETCH profitable_internet_stocks
   INTO few_and_far_in_between;
   CLOSE profitable_internet_stocks;
   /*[display_statement]*/
END;

The question asked what code, inserted where the “display_statement” comment is, would display the result of the query. Without thinking too hard about it, I ticked the fourth option as correct:

DBMS_OUTPUT.PUT_LINE (few_and_far_in_between.sum_share_price);


I had this stupid idea that Oracle would step in and magically create an alias for the column*. A millisecond after clicking “Accept”, I realised I’d got it wrong. I’d assumed that at least one of the answers were likely to be correct, which is obviously not true.

* EDIT: Gary Myers has blogged about this too and explains that Oracle does, indeed, create an alias.


Another way to learn something new every day

The PL/SQL ChallengeMy morning routine for the last few weeks has had a small addition – just a few minutes to log on The PL/SQL Challenge at 8am (WST) and answer a quiz question.

This is a great way to test the depth and breadth of your knowledge of Oracle PL/SQL. Whether you’re a beginner in the field or have years of experience, I’m certain you’ll occasionally learn something new – plus, it’s completely free!

Some of the questions are fairly general, others are trivial; while they’re never “trick” questions, they can still trip you up if you’re not observant!

I think I have an unfair advantage, being in this timezone – early morning is when I am most alert and ready to tackle any challenge.

A couple of things I’ve learned recently:

  • UTL_FILE.putf can be used if you want to use C-style “printf” syntax, e.g. '\n'
  • SIGNTYPE is a standard subtype that can only take values of NULL, -1, 0 or 1

(I’ve learned a few other things as well, but I’ve forgotten what they were :|)