Someone who attended my sessions at the Bucharest Oracle Summit earlier on this year sent me an example of a quirky little bug, possibly related to the newer “fine-grained” invalidation mechanisms, possibly related to ANSI syntax SQL, that’s very easy to reproduce. (That’s always nice for Oracle support – a perfect test case.)
All it takes is two tables and a packaged procedure that queries those tables. The package is coded to do something that should not be allowed in production code; but “should not” and “is not” are very different things. For anyone who wants to play with the example, here’s the script to create the necessary objects:
drop package pkg_test; drop table t2 purge; drop table t1 purge; create table t1 (id1 number, val1 varchar2(10)); create table t2 (id2 number, val2 varchar2(10)); insert into t1 values(1,rpad('x',10,'x')); insert into t2 values(1,rpad('x',10,'x')); execute dbms_stats.gather_table_stats(user,'t1') execute dbms_stats.gather_table_stats(user,'t2') create or replace package pkg_test is procedure pr_call; end pkg_test; / create or replace package body pkg_test as procedure pr_call is cursor cur_ids is select * -- Naughty ! from t1 join t2 on t2.id2 = t1.id1 ; rec_id cur_ids%rowtype := null; begin open cur_ids; fetch cur_ids into rec_id; close cur_ids; dbms_output.put_line(rec_id.val1 || '-' || rec_id.val2); exception when others then if cur_ids%isopen then close cur_ids; end if; raise; end pr_call; end pkg_test; /
Having created the procedure I’m now going to call it – and then add a column to table t1. What’s that going to do to a packaged procedure with a “select *”?
Pause for thought …
Here’s some SQL to run the test.
set serveroutput on prompt *** Make a first call to the procedure: no error *** execute pkg_test.pr_call prompt *** add a column to one of the tables alter table t1 add col_test varchar2(20); prompt *** Make two more calls to the procedure: ouch! *** execute pkg_test.pr_call execute pkg_test.pr_call prompt *** Recompile before a third call *** execute dbms_ddl.alter_compile('package body', user, 'pkg_test') execute pkg_test.pr_call
Unless I’ve managed to cut-n-paste the wrong bits of code, you would have got the following error for the 2nd and 3rd calls to the package:
BEGIN * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got - ORA-06512: at "TEST_USER.PKG_TEST", line 25 ORA-06512: at line 2
The package body should (I believe) have invalidated and recompiled itself for the second execution, and even if it failed on the first attempt surely it should have invalidated itself on the ORA-932 and recompiled itself and succeeded on the third execution. (If you remove the exception clause you’ll find that the error is intially raised at the fetch, by the way).
If we change the “select *” to explicitly name the columns we want, viz:“select t1.id1, t1.val1, t2.id2, t2.val2” we don’t get the ORA-00932 errors (just as we would probably expect). What we might not expect is that the errors also disappear if we leave the “select *” in place but change the query from ANSI syntax to traditional Oracle syntax.
Footnote:
Obviously you shouldn’t use the lazy “*” notation in any production code – it can cause several different problems (including the dangers of “whoops, I didn’t mean to make that one invisible”) – but if you do you may find that you end up with packaged procedures that crash for no apparent reason until you recompile them. Perhaps ORA-00932 is the only possible error message, but maybe it’s possible to cause other errors to appear. Even worse, though I haven’t tried to force it yet, you may find that you can construct cases where the package reports no error but modifies the wrong data.
I’ve tested this code on versions 11.2.0.4 and 12.1.0.2 and see the same results on both.
Hello, Jonathan!
I’ve got the same results in 12C but is that true for 11G?
Thank you.
Comment by Viacheslav Andzhich — July 22, 2015 @ 2:10 pm BST Jul 22,2015 |
Viacheslav,
I forgot to add versions to the article – and I’ll correct that immediately – but I’ve tested 11.2.0.4 and 12.1.0.2 and both misbehave.
Comment by Jonathan Lewis — July 22, 2015 @ 7:45 pm BST Jul 22,2015 |
Fyi – Reproducible on 11.2.0.3 as well.
Comment by Dom Brooks — July 24, 2015 @ 12:43 pm BST Jul 24,2015 |
After many years of reading this blog, I finally get to make a constructive comment!
Oracle Education’s 9i OCA PL/SQL Developer exam typically had a question regarding how to avoid invalidation errors in production code. The Correct Answer (!) was to code SELECT * FROM… (this was from the exam prep. materials; I have no inside knowledge of the actual thinking). Apparently, someone thought that avoiding an invalidation of a PL/SQL object was something to be sought at all costs.
This comment should not be interpreted to assume I support the above use of SELECT * FROM…
Someone brighter than me will have to figure out why there is the difference between using the traditional Oracle syntax vs. ANSI syntax.
Comment by Phil from Brighton (Michigan) — July 23, 2015 @ 7:44 pm BST Jul 23,2015 |
Phil,
Interesting.
I know they were always keen on using %rowtype – I wonder if the two seemed like a natural fit.
Comment by Jonathan Lewis — July 24, 2015 @ 9:00 pm BST Jul 24,2015 |
FYI – In my 12.1.0.2, the call to DBMS_DDL errors unless 2nd argument is NULL
execute dbms_ddl.alter_compile(‘PACKAGE BODY’, user, ‘pkg_test’)
Error report –
ORA-06563: name has too many parts
ORA-06512: at “SYS.DBMS_UTILITY”, line 156
ORA-06512: at “SYS.DBMS_DDL”, line 157
execute dbms_ddl.alter_compile(‘PACKAGE BODY’, null, ‘PKG_TEST’);
anonymous block completed
Comment by Dom Brooks — July 24, 2015 @ 12:35 pm BST Jul 24,2015 |
Also, on the subject of the closing of the cursor in the exception block, shouldn’t this be unnecessary because the cursor is declared at proc level (as opposed to package level)… I accept that it is good practice in case you move where the cursor is declared.
Comment by Dom Brooks — July 24, 2015 @ 12:50 pm BST Jul 24,2015 |
Also … :) … If you make it select t1.*, t2.* then the dependency seems to be tracked.
Comment by Dom Brooks — July 24, 2015 @ 12:57 pm BST Jul 24,2015 |
Valentin Nikotin has some examples in the comments here:
http://plsql-challenge.blogspot.co.uk/2011/01/exploring-nuances-of-11g-fine-grained.html
Comment by Dom Brooks — July 24, 2015 @ 1:00 pm BST Jul 24,2015
Dom,
Thanks for all the follow-up points, especially the URL
Comment by Jonathan Lewis — July 24, 2015 @ 9:03 pm BST Jul 24,2015 |