{"id":1555,"date":"2009-12-18T12:00:00","date_gmt":"2009-12-18T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1555"},"modified":"2009-12-18T12:00:00","modified_gmt":"2009-12-18T12:00:00","slug":"bug-hunting","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/12\/18\/bug-hunting\/","title":{"rendered":"Bug Hunting"},"content":{"rendered":"

It’s early days but I think I became the joint father of a bug report this week. This is bug number 9219636.<\/p>\n

\nSQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER\n\u00a0 2\u00a0 \/\n\nType created.\n\nSQL> \nSQL> CREATE TABLE V_SESSION_VALID (\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID NUMBER ,\n\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SESSION_ID NUMBER NOT NULL,\n\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT SID_PK PRIMARY KEY (SID) VALIDATE ,\n\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT SESSIONID_UK UNIQUE (SESSION_ID) VALIDATE)\n\u00a0 6\u00a0 \/\n\nTable created.\n\nSQL> \nSQL> CREATE TABLE SESSION_VIEW ( VIEW_ID NUMBER ,\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SESSION_ID NUMBER NOT NULL,\n\u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_COUNT NUMBER NOT NULL,\n\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT VIEWID_PK PRIMARY KEY (VIEW_ID) VALIDATE)\n\u00a0 5\u00a0 \/\n\nTable created.\n\nSQL> \nSQL> CREATE TABLE BATCH_SESSION (BATCH_ID NUMBER NOT NULL , ROW_ID NUMBER NOT NULL , \n                                 SID NUMBER NOT NULL )\n\u00a0 2\u00a0 \/\n\nTable created.\n\nSQL> \nSQL> declare\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 l_sessions INTEGER_ARRAY_T;\n\u00a0 3\u00a0 begin\n\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select s.SESSION_ID\n\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bulk collect into l_sessions\n\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from V_SESSION_VALID s\n\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)\n\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join BATCH_SESSION b on (s.SID = b.SID)\n\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where\u00a0 v.ROW_COUNT > 0\n\u00a010\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and b.BATCH_ID between 1 and 1000\n\u00a011\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for update of s.SESSION_ID;\n\u00a012\u00a0 end;\n\u00a013\u00a0 \/\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select s.SESSION_ID\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 *\nERROR at line 4:\nORA-06550: line 4, column 2:\nPL\/SQL: ORA-00918: column ambiguously defined\nORA-06550: line 4, column 2:\nPL\/SQL: SQL Statement ignored\n<\/code><\/pre>\n
This works in all versions prior to 11.2.0.1, which is where it was identified during some 11gR2 RAC testing of the application I’m working on. There are actually quite a few notes about this type of error kicking around on My Oracle Support, but in most cases they aren’t bugs, but a tightening of ambiguous column checking, which is a good thing. However, if your app does use ANSI join syntax, keep an eye out for 11gR2 catching out previously suspect SQL statements when you’re upgrading.<\/p>\n
However, there is no ambiguous column definition here. If I convert it to traditional Oracle join syntax, it works, as does commenting out the offending part of the code.<\/p>\n<\/p>\n
<\/code>
SQL> declare\n\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 l_sessions INTEGER_ARRAY_T;\n\u00a0 3\u00a0 begin\n\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 select s.SESSION_ID\n\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 bulk collect into l_sessions\n\u00a0 6\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from V_SESSION_VALID s\n\u00a0 7\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join SESSION_VIEW v on (s.SESSION_ID = v.SESSION_ID)\n\u00a0 8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 join BATCH_SESSION b on (s.SID = b.SID)\n\u00a0 9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where\u00a0 v.ROW_COUNT > 0\n\u00a010\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and b.BATCH_ID between 1 and 1000;\n\u00a011\u00a0 -- for update of s.SESSION_ID;\n\u00a012\u00a0 end;\n\u00a013\u00a0 \/\n\nPL\/SQL procedure successfully completed.\n<\/p><\/pre>\n
So why do I say joint father of the bug report? Because having raised the SR, we delayed our 11gR2 upgrade work for unassociated reasons and so I didn’t put the work in to provide the proper test case that Oracle Support requested. A couple of weeks later, with the SR abandoned for now, one of the DBAs mentioned a bug he’d found on 11gR2 and my first question was ‘ANSI join syntax?’. Sure enough, it was the same as our example so he picked up the SR and ran with it and put together the test case here, which is also stripped of any company information. So we might have hit it first, but Cristian Banoiu put in the hard work. I’m sure he was pretty proud when it was assigned a bug number and I believe it’s currently in development.<\/p>\n","protected":false},"excerpt":{"rendered":"
It’s early days but I think I became the joint father of a bug report this week. This is bug number 9219636. SQL> CREATE TYPE INTEGER_ARRAY_T AS TABLE OF INTEGER \u00a0 2\u00a0 \/ Type created. SQL> SQL> CREATE TABLE V_SESSION_VALID ( \u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SID NUMBER , \u00a0 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SESSION_ID NUMBER NOT NULL, \u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT… Continue reading Bug Hunting<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1555","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1425,"url":"http:\/\/orcldoug.com\/blog\/2008\/08\/05\/hanging-audit-vault-warehouse-refresh-job\/","url_meta":{"origin":1555,"position":0},"title":"Hanging Audit Vault Warehouse Refresh Job","date":"August 5, 2008","format":false,"excerpt":"I've been working with Audit Vault 10.2.3 on AIX recently and ran into a problem that someone else might one day. I decided to capture the output as best I can and blog about it later. (As I don't have the same system available any more, the formatting might leave\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":907,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/10\/10g-optimiser-environment-views\/","url_meta":{"origin":1555,"position":1},"title":"10g Optimiser Environment Views","date":"November 10, 2005","format":false,"excerpt":"In a previous blog I mentioned that Julian Dyke had talked about these views during his presentation and I noticed Jonathan Lewis also includes it in an Appendix of his new book that I started reading last night. There are three versions of the viewConnected to:Oracle Database 10g Enterprise Edition\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1016,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/12\/table-reorgs-and-statistics\/","url_meta":{"origin":1555,"position":2},"title":"Table reorgs and statistics","date":"July 12, 2006","format":false,"excerpt":"While working on the ITL deadlock problem (which looks like it's been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1555,"position":3},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1481,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/09\/diagnosing-locking-problems-using-ash-part-4\/","url_meta":{"origin":1555,"position":4},"title":"Diagnosing Locking Problems using ASH \u2013 Part 4","date":"April 9, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) \"It's probably worth pointing out that\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":968,"url":"http:\/\/orcldoug.com\/blog\/2005\/07\/22\/deferred-constraint-checking\/","url_meta":{"origin":1555,"position":5},"title":"Deferred Constraint Checking","date":"July 22, 2005","format":false,"excerpt":"In my previous blog entry I highlighted a few of the reasons that integrity constraint checking should not be deferred, stated by Date in his recent book (and by him and others in other papers). At the time and while looking at an entry over on Jeff Hunter's Blog I\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1555"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1555\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1555"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}