StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOracle 10: Incomprehensible behaviour on INSERT into a view?
text
Body
copied!<p>we have a strange problem here, we can't explain to ourselves.</p> <p>We have a view in an Oracle DB Version 10.2.0.5.8. The view uses an INSTEAD OF trigger.</p> <p>This is the code for the trigger:</p> <pre><code>CREATE OR REPLACE TRIGGER V1_T1_BIUD INSTEAD OF INSERT OR UPDATE OR DELETE ON V1_T1 FOR EACH ROW DECLARE AnyId NUMBER; BEGIN IF INSERTING THEN INSERT INTO Table T1 ( F1, F2, F3, F4, F5 ) VALUES ( :new.F1, :new.F2, :new.F3, :new.F4, :new.F5 ); ELSIF UPDATING THEN UPDATE T1 SET F1 = :new.F1, F2 = :new.F2, F3 = :new.F3, F4 = :new.F4, F5 = :new.F5 WHERE F1 = :old.F1; ELSIF DELETING THEN DELETE FROM T1 WHERE F1 = :old.F1; END IF; END; / </code></pre> <p>This is an example INSERT statement:</p> <pre><code>INSERT INTO V_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 ALL SELECT 'E', 'Y', 'QWE', 'O', 'E' FROM DUAL UNION ALL SELECT 'I', 'Y', 'GHJ', 'I', 'I' FROM DUAL ) A ORDER BY 1, 2, 3; COMMIT; </code></pre> <p>Pay attention to the ORDER BY clause at the end of the select. The result of this INSERT statement is something like this:</p> <pre><code>F1 F2 F3 F4 F5 --------------- E N ABC I I E Y QWE I I I Y GHJ I I </code></pre> <p>As you can see, the 4th and 5th column are incorrectly filled with the values of the last datarow in all other datarows.</p> <p>If we change the INSERT statement like this:</p> <pre><code>INSERT INTO V_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 ALL SELECT 'E', 'Y', 'QWE', 'O', 'E' FROM DUAL UNION ALL SELECT 'I', 'Y', 'GHJ', 'I', 'I' FROM DUAL ) A ORDER BY 1, 2, 3, 4, 5; COMMIT; </code></pre> <p>the result is this:</p> <pre><code>F1 F2 F3 F4 F5 --------------- E N ABC E E E Y QWE O E I Y GHJ I I </code></pre> <p>Again, pay attention to the ORDER BY clause, which now orders all rows instead of the first three in the first insert statement.</p> <p>edit: If you omit the ORDER BY clause the result is also as expected (e. g. like in example 2).</p> <p>Can someone explain this behaviour to me? </p> <p>P. S. Concerning the comments:</p> <p>I have not time to investigate or deliver any more infos on this topic today. I will create a complete example on our database and publish it here in the next few days. Thank you for your patience!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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