Something I didn't know up TO_DATE

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Look at this (my session date format is DD/MM/YYYY):

select to_date('2011','YYYY') d from dual;

D                   
---------------------
01-02-2011 00:00:00

select trunc(to_date('2011','YYYY'),'Y') d from dual;

D                   
---------------------
01-01-2011 00:00:00 

I didn't know that when converting a year from characters to a date, Oracle would return the value using the current month (in the SQL reference I can't find anything about this behavior).

I found this out because while working on some queries spanning over a 12 month period supposedly starting from the first month of the year, I could not understand why I was seeing a missing January 2011 and an unwanted January 2012 in the result set.

Initially I feared that this was something introduced with Oracle 11gR2, but I later verified that this happens on Oracle 10gR2 as well, so it must have always been like this.

Fortunately in my applications I never store date information as strings, so I don't really expect this to affect my software, but finding this at a customer's site, makes me think that I'll need to search each and every occurrence of this and replace it with the TRUNCated expression in order to make their queries consistent. 

You never stop learning.

ERR-1002 Unable to find item ID for item... after branching to page

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Can't find why Apex 4.0 is returning the following error?

If you copy and paste the error message into a fixed-width text editor, you'll will hardly notice that there is an extra space between the first double quotes and the name of the item, that I highlighted in red color.

Unexpected error, unable to find item name at application or page level.
Error ERR-1002 Unable to find item ID for item " P33_LOCATION_ID" in application "21673".

When I inspected the branch action, I realized that I had inserted a blank between the comma and the item name in comma separated list of items and this breaks the toy.

Clearly item names are not trimmed by the internal PL/SQL function that does the splitting, so watch out for the extra blanks, they can drive you mad for a while.
I could not check yet if this happens in Apex 4.1.