Geek asks and answers

Oracle 10: Incomprehensible INSERT behavior in a view?

we have a strange problem, we cannot explain to ourselves.

We have a view in Oracle DB version 10.2.0.5.8. The view uses the INSTEAD OF trigger.

This is the code for the trigger:

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;
/

This is an example of an INSERT statement:

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;

Pay attention to the ORDER BY clause at the end of the selection. The result of this INSERT statement looks something like this:

F1 F2 F3  F4 F5
---------------
E  N  ABC I  I 
E  Y  QWE I  I 
I  Y  GHJ I  I

As you can see, the 4th and 5th columns are incorrectly filled with the values ​​of the last datarow in all other datarows.

If we change the INSERT statement as follows:

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;

The result is the following:

F1 F2 F3  F4 F5
---------------
E  N  ABC E  E 
E  Y  QWE O  E 
I  Y  GHJ I  I

Again, look at the ORDER BY clause, which now arranges all rows instead of the first three in the first insert.

edit: If you omit the ORDER BY clause, the result is also expected (for example, as in Example 2).

- ?

. S. :

. . !

+5
2

, ( , 5842445, ). ( , T1, V1_T1 - ); F4 F5 CHAR not VARCHAR2:

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;

... instead of, .

:NEW DBMS_OUTPUT, , , - , Oracle.

11.2.0.3 (Linux). , UNION ALL UNION, ; 10g , 11g x:

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

..., - , - .

; Oracle, , order by, , .

; - order by (11g):

----------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------

order by 1,2,3 1,2,3,4,5 - (11g):

----------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------

, , ; null, x. ( , x dual, dummy x, x).

@Annjawn, V1_T1 T1 ( , ), -, Name. UNION UNION ALL , 10gR2 11gR2. , , , .

datatype... CHAR, , , . CHAR, VARCHAR2 , :

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;

, :

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;
+6

- , T1, , Oracle 10g 11g.

create table t1 (f1 varchar2(10),
f2 varchar2(10),
f3 varchar2(10),
f4 varchar2(10),
f5 varchar2(10));

create or replace view v_t1 as select * from t1;

CREATE OR REPLACE TRIGGER V1_T1_BIUD
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON v_t1
  FOR EACH ROW
DECLARE
  AnyId   NUMBER;
BEGIN
  IF INSERTING THEN
    INSERT INTO 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;

--With UNION ALL
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;

select * from t1;

F1         F2         F3         F4         F5       
---------- ---------- ---------- ---------- ----------
E          N          ABC        E          E          
E          Y          QWE        O          E          
I          Y          GHJ        I          I 

delete from t1;
commit;

--With UNION
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
  SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION
  SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL           
) A
ORDER BY 1, 2, 3; 
commit;

select * from t1;

F1         F2         F3         F4         F5       
---------- ---------- ---------- ---------- ----------
E          N          ABC        E          E          
E          Y          QWE        O          E          
I          Y          GHJ        I          I

, F4 F5 char(10) varchar2(10) ( Alex Poole), 10g 11g.

0

More articles:


All Articles