I am designing some changes to the application suite my project supports. There are some requirements that need me to add new tables to the database schema. So I requested the DBAs to add them. Our project has some Oracle consultants which review the database change requests. One of my new tables was grouped in with some other needs into a global table. The consultants had a lot of ideas on this new table and its access. That sounded good to me. One consultant wrote a PL/SQL package that managed the table, including things such as auditing.Before I go on vacation, I wanted to make sure the development staff had what they needed to implement the code changes. So I tested out the PL/SQL package written by the consultant. I logged into SQL*Plus. There were a bunch of functions exported by the PL/SQL package. I executed a SELECT FROM DUAL for one of the getter functions. It worked fine. Then I tried to SELECT FROM DUAL one of the functions that modified the underlying database table. The function returned an error to me. So I pushed it back to the consultant because I thought maybe he had a bug.
The consultant came back to me with an answer. The function was trapping an ORA-14551: cannot perform a DML operation inside a query. Of course that made sense. The SELECT FROM DUAL cannot change a value in the database. Why didn’t I think about that. The way to test out this stuff is to write some PL/SQL code, and call the functions from there. That is how the developers are going to have to implement this. The consultant also cautioned me against testing this directly from SELECTs in SQL*Plus because the functions are autonomous transactions. Apparently things get screwy when you call autonomous transactions like that.
The bottom line is that you cannot issue a SELECT FROM DUAL on a function that modified the database. There is a rule in Oracle that prohibits this. I have actually run into this before. However since I did not write the implementation for this package, I did not see what was going on under the hood. Developers beware.
