Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This does look like a bug, but I can't find an obvious match in the bug database (a few look possible, like 5842445, but are vague or don't quite line up). I can only make it happen with the trigger (so I assume your inserts being against <code>T1</code> rather than <code>V1_T1</code> are a transcription error); and only if <code>F4</code> and <code>F5</code> are <code>CHAR</code> not <code>VARCHAR2</code>:</p> <pre><code>create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3), f4 char(2), f5 char(2)); create view v1_t1 as select * from t1; </code></pre> <p>... and the <code>instead of</code> trigger exactly as shown in the question.</p> <p>The <code>:NEW</code> values inside the trigger are wrong, according to <code>DBMS_OUTPUT</code>, but how that's affected by the column data type is something only Oracle would be able to figure out I think.</p> <p>It also still happens in 11.2.0.3 (Linux). Interestingly if I change the <code>UNION ALL</code> to just <code>UNION</code> I get slightly different results; in 10g the two columns end up null, in 11g they have <code>x</code>:</p> <pre><code>insert into v1_t1 ( F1, F2, F3, F4, F5 ) SELECT A.V, A.S, A.F, A.T, A.Z FROM ( SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION SELECT 'E', 'Y', 'QWE', 'O', 'E' FROM DUAL UNION SELECT 'I', 'Y', 'GHJ', 'I', 'I' FROM DUAL ) A ORDER BY 1, 2, 3; 3 rows created. select * from v1_t1; F1 F2 F3 F4 F5 -- -- --- -- -- E N ABC x x E Y QWE x x I Y GHJ x x </code></pre> <p>... which is even stranger - looks like maybe a fix to some other bug has slightly affected this one.</p> <p>So not really an answer; you'd need to rase a service request with Oracle, and I'm fairly sure they'd just tell you to remove the <code>order by</code> since it doesn't have any value, as you already know.</p> <p>For Thilo; plan without any <code>order by</code> (11g):</p> <pre><code>---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 3 | 51 | 9 (34)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | V1_T1 | | | | | | 2 | VIEW | | 3 | 51 | 9 (34)| 00:00:01 | | 3 | SORT UNIQUE | | 3 | | 9 (78)| 00:00:01 | | 4 | UNION-ALL | | | | | | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- </code></pre> <p>And plan with <code>order by 1,2,3</code> <em>or</em> <code>1,2,3,4,5</code> - same plan hash value (11g):</p> <pre><code>---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 3 | 51 | 10 (40)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | V1_T1 | | | | | | 2 | SORT ORDER BY | | 3 | 51 | 10 (40)| 00:00:01 | | 3 | VIEW | | 3 | 51 | 9 (34)| 00:00:01 | | 4 | SORT UNIQUE | | 3 | | 9 (78)| 00:00:01 | | 5 | UNION-ALL | | | | | | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | 8 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------- </code></pre> <p>And I see the same sort of corruption selecting from other tables, but only if the results in the subquery are unioned before being ordered; though then I get nulls rather than <code>x</code>. (I briefly wondered if the <code>x</code> was coming from <code>dual</code> itself, but <code>dummy</code> is upper-case <code>X</code>, and this shows lower-case <code>x</code>).</p> <hr> <p>Following @Annjawn's comment, changing the insert from <code>V1_T1</code> to a direct insert in <code>T1</code> works fine (i.e. correct values inserted), and curiously has the same plan hash even though it shows the table name instead of the view in the <code>Name</code> column. Work with either <code>UNION</code> or <code>UNION ALL</code>, too, and in both 10gR2 and 11gR2. Seems to be the trigger that's confused by the union, I guess.</p> <hr> <p>Further to the datatype point... the view has to have <code>char</code> columns, the table does not necessarily, which isn't really a surprise since the trigger on the view seems to be the problem. If I set the table up with <code>char</code> columns but cast them to <code>varchar2</code> in the view then I don't see the problem:</p> <pre><code>create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3), f4 char(2), f5 char(2)); create view v1_t1 as select f1, f2, f3, cast(f4 as varchar(2)) f4, cast(f5 as varchar(2)) f5 from t1; </code></pre> <p>But If I do it the other way around it does exhibit the problem:</p> <pre><code>create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3), f4 varchar(2), f5 varchar(2)); create view v1_t1 as select f1, f2, f3, cast(f4 as char(2)) f4, cast(f5 as char(2)) f5 from t1; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POOracle 10: Incomprehensible behaviour on INSERT into a view?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAlex Poole
    1. USAlex Poole
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POOracle 10: Incomprehensible behaviour on INSERT into a view?
      singulars
      1. PTQuestion
    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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COExcellent research. Please link this in your bug report to Oracle (if you make one), and also update this thread with the bug ID.
      singulars
      1. PO
      1. USThilo
 

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