Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is indeed strange issue, thanks for sharing!</p> <p>It really looks like a behavior change in Oracle starting with Oracle 11.1 and there is even confirmed bug with similar issue on metalink (bug#10425196). Unfortunately at the moment there is no much information available on metalink on subject mater, but I've also opened SR with Oracle asking to provide more information.</p> <p>While at the moment I can not provide you an explanation why it happens and if there is a (hidden) parameter that can reverse this behavior back to 10g style, I think I can provide you with workaround. You can use Oracle flashback query functionality to force Oracle to retrieve data as to expected point in time. </p> <p>If you change your code as follows:</p> <pre><code>OPEN crs FOR SELECT col1 &gt;&gt;&gt; FROM vw1 as of scn dbms_flashback.get_system_change_number WHERE col1 = 'TEST1'; </code></pre> <p>then result should be the same as in 10g.</p> <p>And this is simplified version of original test case:</p> <pre><code>SQL&gt; drop table tbl1; Table dropped SQL&gt; create table tbl1(col1 varchar2(10), col2 number); Table created SQL&gt; create unique index tbl1_idx on tbl1(col1); Index created SQL&gt; insert into tbl1(col1,col2) values('TEST1',0); 1 row inserted SQL&gt; DECLARE 2 cursor web_cursor is 3 SELECT col1 4 FROM tbl1 5 WHERE col2 = 0 and col1 = 'TEST1'; 6 7 rec1 web_cursor%rowtype; 8 BEGIN 9 OPEN web_cursor; 10 11 UPDATE tbl1 12 SET col2 = 1 13 WHERE col1 = 'TEST1'; 14 15 -- different result depending on commit! 16 commit; 17 18 DBMS_OUTPUT.PUT_LINE('Start'); 19 LOOP 20 FETCH web_cursor 21 INTO rec1; 22 23 EXIT WHEN web_cursor%NOTFOUND; 24 25 DBMS_OUTPUT.PUT_LINE(rec1.col1); 26 END LOOP; 27 DBMS_OUTPUT.PUT_LINE('Finish'); 28 END; 29 / Start Finish PL/SQL procedure successfully completed </code></pre> <p>If you comment out commit on line 16 than the output will be:</p> <pre><code>Start TEST1 Finish PL/SQL procedure successfully completed </code></pre>
    singulars
    1. This table or related slice is empty.
    1. PODifferent behavior for REF CURSOR between Oracle 10g and 11g when unique index present?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEdgar Chupit
    1. USEdgar Chupit
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COMetalink Bug 10425196 is the exact problem we are encountering, thanks for digging this up! I haven't been able to test your workaround using DBMS_FLASHBACK yet because our local test instances do not have it enabled. I don't think we would be able to use this workaround since our clients host their own Oracle instances and we can't guarantee what edition they run, whether it's enabled or how it's configured, plus we'd have to find and modify all the affected code. I think we'll have to wait and see whether Oracle comes out with a bug fix and maybe get on the list to beta test their patch.
      singulars
      1. PO
      1. USwweicker
    2. COAdded clarification to the original post indicating why I can't remove the COMMIT
      singulars
      1. PO
      1. USwweicker
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload