Update returning old values, possible solution

Warning:  While this appears to work, I do not know if the behavior is defined.

It seems that using a scalar subquery in the select list of an updatable view allows us to return the old value of a column.  I’ll update this post with more details when I (more...)

Quiz: index including a constant

I was surprised when i tried to create an index like this and it actually worked:

Create index t1idx on t1(n1,1);

The question is: Why would you want to do this?

There is at least one valid use case.


crazy rewrites

Jonathan Lewis’s posts on index joins inspired me to run a few test cases of my own. I thought it was pretty neat that the optimizer could split a single table query into a 6 way self join. It really blew my mind when I found it could do the reverse. I also found it quite cute that it does the opposite of what I tell it too. That’s just how stuff works for me.

This was run on a fresh install of the developer vm in VirtualBox:

Edit – I really need to learn to preview. Here is the (more...)

Thanks Computer Junkyard

Just a quick thank you to Computer Junkyard I had a great experience there today.

If you are in or around Tampa, please check them out.


A quick gripe

I really did not want the Oracle section of this blog to be my whining about all the nasty things Oracle does to me. It is just hard to find the time and motivation to write up anything positive when I run into three new (to me at least) bugs in three days. So, I’m going to gripe:

I hit the bug in metalink note 469587.1 where “Error PLS-00167 : keyword BULK is used in a wrong context” is raised with an incorrect interval literal. Of course, I had to come up with a unique twist of my own. (more...)

Default precision fractional for timestamp datatype is 6. Huh?

I’ve always read that the default precision for timestamps was 6.

From “Oracle® Database SQL Language Reference 11g Release 1 (11.1) Part Number B28286-03

TIMESTAMP Datatype

The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [(fractional_seconds_precision)]

where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this datatype, the value (more...)