Month: April 2010

Weird SQL Results

Chris Taylor raised a good question.

Here is the interesting part (to me) – Sometimes it returns 1 row, sometimes it returns more than 1 row, and sometimes it returns no rows. To my mind, “trunc(dbms_random.value(1,500)” should return a random value between 1 & 500 every time and just one value.  Is this weird, or is it just me???

Basically he’s wondering why querying a table on a randomly-chosen unique identifier does not always return exactly 1 row.

At first glance it seems nonsensical and looks like a bug, but really, it’s not – in fact, Oracle is doing exactly the right thing (anecdotally, a colleague has told me that SQL Server gets this particular behaviour wrong).

A simple test case: the inner query generates exactly 100 rows, with the numbers 1 to 100. The outer query then queries this result set, with the intention of picking one of those rows only:

select * from (
  select rownum r from dual connect by level <= 100
where r = trunc(dbms_random.value(1,100));

However, what happens here is that Oracle knows that dbms_random.value is not deterministic; and the predicates (WHERE clause) must be evaluated once for each row returned from the FROM clause. Since dbms_random.value is not deterministic, Oracle knows it must re-evaluate it for each row, separately – which means it is comparing a different number for each row returned.

So, for example, Oracle looks at row 1, with the value 1. It generates a random number, e.g. 12, and so the predicate evaluates to FALSE, and the row is not returned. Oracle then looks at row 2, which has the value 2. It generates a random number, e.g. 2, and so the predicate evaluates to TRUE, and the row is returned. It does this for each row until the 100th is evaluated and then the query stops.

If none of the 100 random numbers happen to coincide with the values returned from the table, then the query will return no rows.

Quiz question: what is the theoretical maximum number of rows the above query could ever return?

To fix it so that Oracle only evaluates the selection criteria once, we just need to wrap it in another subquery:

select * from (
  select rownum r from dual connect by level <= 100
where r = (select trunc(dbms_random.value(1,100)) from dual);


  lexists      BOOLEAN;
  lfile_len    NUMBER;
  lblocksize   NUMBER;
    location    => 'a',
    filename    => 'b',
    exists      => lexists,
    file_length => lfile_len,
    blocksize   => lblocksize);

I was trying to use this procedure in a 9i database and kept getting:

PLS-00103: Encountered the symbol ">"...

– complaining about line 8 (the “exists” parameter). If I removed the parameter names, it worked fine. Something was wrong with that “exists” parameter name.
In the 9i and 10g documentation:

    location    IN VARCHAR2,
    filename    IN VARCHAR2,
    exists      OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize   OUT NUMBER);

In the 11g documentation:

    location    IN VARCHAR2,
    filename    IN VARCHAR2,
    fexists     OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize   OUT BINARY_INTEGER);

Ah – the parameter was actually called “fexists”. Ok. Try again:

PLS-00306: wrong number or types of arguments in call to 'FGETATTR'

Aaarrgh. Time for more googling.
According to psoug:

    location    IN  VARCHAR2,
    filename    IN  VARCHAR2,
    fexists     OUT BOOLEAN,
    file_length OUT NUMBER,
    block_size  OUT BINARY_INTEGER);

Thank goodness I’ve got access to more than just the Oracle docs!

A Good Bad Example

I learnt something new today (thanks to Steven) about TRIM – in the past I’ve used RTRIM and LTRIM if I only want to trim from the left or right ends of a string, but I was not aware (or had forgotten) that SQL’s TRIM function allows this to be specified.

Oracle’s documentation of TRIM, however, has a somewhat confusing example:

“This example trims leading zeros from the hire date of the employees in the hr schema:

SELECT employee_id,
FROM employees
WHERE department_id = 60
ORDER BY employee_id;

----------- ---------
103 3-JAN-90
104 21-MAY-91
105 25-JUN-97
106 5-FEB-98
107 7-FEB-99

As an example of using the TRIM function the above code is adequate; however, why does it call the TO_CHAR function at that point? After all, TO_CHAR is only useful if the datatype of its argument is not a character string – but in this case, it’s a string already – TRIM always returns a string.

Not only does the example perform a useless datatype conversion, it involves an implicit conversion – from date to string. The expression would be much better like this:


Better – but still not perfect. Sure, we should probably specify the date format so the code is a bit more robust (what if the session has a default format of ‘MM/DD/YY’?), but we can see yet another implicit data type conversion: “LEADING 0“. The only purpose for TRIM is to remove characters. Sure, you can put in zero if you want, but it’s only going to be converted to a character, ‘0’ anyway – so why not make it obvious to the next coder who comes along:


There, much better! I think the above expression is much clearer about what it will actually do – i.e. it converts a date to a string of characters, and then removes any leading ‘0’ characters.

The exception we never knew we needed

If, by some great random cosmic chance, you are a reader of this blog, but not of Tom Kyte‘s, then you would have missed this post:

NO_DATA_NEEDED – something I learned recently

It appears to have been documented in the 9i documentation, complete with spelling error:

ORA-06548, 00000, "no more rows needed"
Cause:   The caller of a pipelined function does not
         need more rows to be produced by the pipelined
Action:  Catch the NO_DATA_NEEDED exception is an
         exception handling block.

Mind you, it’s not all that obvious since if the pipelined function does not handle the exception, nothing goes wrong – the exception is never raised by the calling SQL statement. It’s not obvious when ORA-06548 would ever be raised.


Looks like ORA-06548 can appear in the error stack.