Open cursor paranoia

Most PL/SQL developers will likely have witnessed this phenomenon several times during their career. But only in other people's code, of course :-). I'm talking about PL/SQL code where every program unit ends like this:exceptionwhen others then if c%isopen then close c; end if; raise;end;where lines 3 to 6 are repeated for every cursor in the block above.Proponents of open cursor paranoia

Runtime error ORA-01031: insufficient privileges

After a new version of software was installed in production, the end users reported a runtime error: ORA-01031: insufficient privileges, when selecting from a view. The developers of the code were investigating the problem and half way through, they asked me to have a look at the problem. I saw a function from schema3, which was used in a view in schema2, which was used by schema1. I had just

Questions about the result cache

Last year I have presented a couple of times about the result cache. You can download this presentation from my new Presentations and papers tabpage, by the way. After each of those sessions I received several good questions. To some questions I didn't know the answer, and for some other questions I did, but I hadn't tested it so I wasn't absolutely sure. I promised to address those questions

SQL Masterclass in Tallinn, Estonia

On June 9 and 10, I'll be doing a SQL Masterclass seminar for Oracle University in Tallinn, Estonia. You can find the details here.

A tip, a conference, an extension and a challenge

This post contains four unrelated notes.First a small SQL*Plus tip. I really like to know with which user I am connected to which database, so in my login.sql script I used to have this section:define gname=idlecolumn global_name new_value gnameselect lower(user) || '@' || substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_namefrom (select global_name,instr(global_name,'.'

Translating and more with Google API’s

Google has a lot of API's that you can use in SQL and PL/SQL as well. A couple of months ago I saw a very nice example on OTN here. It was an example of how to use Google's Translate API. Here is how to do it yourself.First of all, since version 11, you need to specify fine grained access to external network services, which is described here in the documentation. The package