Monthly Archives: November 2008

Oracle recommends optimizer_mode=choose

Here is a challenge: Find the place in the latest Oracle product documentation that states the following “To improve response time, verify that the optimizer_mode Oracle initialization parameter is set to choose.”

The first person to tell me in person where Oracle is recommending this, wins a beer. E-mail doesn’t count, because I can’t e-mail the prize 😉

I realize that this gives an unfair advantage to my colleagues and customers in Denmark, but since I’ll be at the UKOUG Conference 2008 in Birmingham this Monday and Tuesday, you can beat the Danes by telling me after one of my presentations. You can find me presenting What’s Hot and What’s Not – an Overview of Oracle Development Tools on Monday at 11:00 and Oracle portal products – should everyone migrate to WebCenter? on Tuesday at 12:10.

ODTUG podcast, SQL Developer Data Modeling

Did you know that ODTUG is podcasting? I hear a couple of podcasts on the way to work the other day, and they’re good. I encourage you to sign up at odtug.podhead.net.

The latest episode was an interview with Sue Harper about the upcoming SQL Developer 2.0, which will include data modeling, both at the conceptual (entity) and relational (table) level. It will offer a file-based and a database-based repository, import from Designer and even data flow diagrams. It seems that Oracle is re-building the parts of Designer that 90% of all users were using: The data modeling part.

There’s an early adopter release available for download. You can also find Sue Harper presenting SQL Developer at the UKOUG 2008 Conference  in Birmingham December 1-5. (I’ll be presenting there as well.)

Being unreasonable (Bug in PL/SQL Web Services)

If you have a lot of PL/SQL packages, it should be real easy to publish them as Web Services – after all, JDeveloper contains a nice wizard for exactly this purpose.

I expected that a lot of people were already using this functionality, so I was very surprised to find a fairly trivial bug in the code built by JDeveloper. I found it reasonable enough to have a stored procedure taking a PL/SQL VARCHAR2 collection as input and calling it with non-ascii characters in the collection elements, but my application server disagreed. The message was ORA-01460: unimplemented or unreasonable conversion requested.

After some investigation, we found that this is caused by defective code built by the JDeveloper wizard. It builds a call to OracleCallableStatement.setPlsqlIndexTable() where the last parameter (elemMaxLen) is set to zero. This is a request for the JDBC driver to automatically figure out the max length – but it doesn’t work. You can fix it by manually changing the zero to a large value like 4000.

Oracle has registered this issue as bug 7503269.