The dbms_output says that 21 rows are updated and that the commit completes.  No errors are produced from this block.  However when the procedure finishes the csp_ld_enfrcmt_apl table does not have the updates. 

After the procedure finishes, if I then take the update from the code below, and run it in sqlplus.  It does the 21 updates.  I can commit and the changes are there.  There are 7 additional updates for different tables just like the code below.  They also have the exact same behavior as this one.

   begin  
      commit;
      update (select b.rgn_cd,b.case_id,b.prcsd_cd bpc,b.ld_stus_cd blc,b.sys_cd bsys,
                     a.rgn_cd,a.case_id,a.prcsd_cd apc,a.ld_stus_cd alc,a.sys_cd asys
              from   csp_ld_enfrcmt_basic b,csp_ld_enfrcmt_apl a 
              where  b.prcsd_cd is not null and b.prcsd_cd <> 'DL'
                     and a.rgn_cd = b.rgn_cd
                     and a.case_id = b.case_id) 
         set apc = bpc,
             alc = blc,
             asys = bsys;
      dbms_output.put_line(sql%rowcount||' apl dependent updt done');
      commit;
      dbms_output.put_line('apl dependent commit done');
      EXCEPTION
        WHEN others THEN
          proc_err_cnt := proc_err_cnt + 1;
          LOG_ERROR_MESSAGE(SYSDATE,'FIND_CASE_REJECT',SQLCODE,SUBSTR(SQLERRM,1,100),
                'Error setting prcsd_cd/ld_stus_cd in appeal');
    end;

Output:
21 apl dependent updt done
apl dependent commit done

PL/SQL procedure successfully completed.

SQL> select rgn_cd,case_id,prcsd_cd,ld_stus_cd,count(*) from csp_ld_enfrcmt_apl where prcsd_cd is not null group by rgn_cd,case_id,prcsd_cd,ld_stus_cd;

no rows selected

--Run the update from the procedure in sqlplus:
SQL> update (select b.rgn_cd,b.case_id,b.prcsd_cd bp
c,b.ld_stus_cd blc,b.sys_cd bsys,
09:59:39   2                       a.rgn_cd,a.case_id,a.prcsd_cd apc,a.ld_stus_c
d alc,a.sys_cd asys
09:59:39   3                from   csp_ld_enfrcmt_basic b,csp_ld_enfrcmt_apl a
09:59:39   4                where  b.prcsd_cd is not null and b.prcsd_cd <> 'DL'

09:59:39   5                       and a.rgn_cd = b.rgn_cd
09:59:39   6                       and a.case_id = b.case_id)
09:59:39   7           set apc = bpc,
09:59:39   8               alc = blc,
09:59:39   9               asys = bsys;

21 rows updated.

Elapsed: 00:00:00.04
09:59:40 qies_c1n9:qies_dev:SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
--Re-run query from above.
SQL> select rgn_cd,case_id,prcsd_cd,ld_stus_cd,count
(*) from csp_ld_enfrcmt_apl where prcsd_cd is not null group by rgn_cd,case_id,p
rcsd_cd,ld_stus_cd;

RGN_CD                                             CASE_ID
                      PR L   COUNT(*)
-------------------------------------------------- -----------------------------
--------------------- -- - ----------
1                                                  2032
                      ID R          1
2                                                  848
                      ID R          1
2                                                  1264
                      ID R          1
3                                                  1472
                      ID R          1
3                                                  1560
                      ID R          1
4                                                  2216
                      ID R          1
4                                                  4192
                      ID R          1
5                                                  8128
                      ID R          1
5                                                  9640
                      ID R          1
6                                                  960
                      ID R          1
6                                                  3256
                      ID R          1
6                                                  3712
                      ID R          1
6                                                  4072
                      ID R          1
6                                                  4224
                      ID R          1
7                                                  2288
                      ID R          1
7                                                  2440
                      ID R          1
8                                                  1000
                      ID R          1
9                                                  424
                      ID R          1
9                                                  440
                      ID R          3

19 rows selected.

Elapsed: 00:00:00.01

Why does the update statement work in sqlplus but not in the procedure?

 

(a when others not followed by RAISE is almost certainly a bug, here it is most certainly a bug waiting to bite you really hard)

provide reproducible test case like this:

ops$tkyte@ORA9IR2> create table t1 ( a int, b int, c int, d int );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( d int primary key, e int, f int, g int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 select null, null, null, rownum from all_users where rownum <= 5;
 
5 rows created.
 
ops$tkyte@ORA9IR2> insert into t2 select rownum, rownum+1, rownum+2, rownum+3 from all_users where rownum <= 5;
 
5 rows created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
         A          B          C          D
---------- ---------- ---------- ----------
                                          1
                                          2
                                          3
                                          4
                                          5
 
ops$tkyte@ORA9IR2> begin
  2    update ( select a,b,c,e,f,g
  3               from t1, t2
  4                      where t1.d = t2.d )
  5       set a = e, b = f, c = g;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
         A          B          C          D
---------- ---------- ---------- ----------
         2          3          4          1
         3          4          5          2
         4          5          6          3
         5          6          7          4
         6          7          8          5
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> rollback;
 
Rollback complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t1;
 
         A          B          C          D
---------- ---------- ---------- ----------
                                          1
                                          2
                                          3
                                          4
                                          5
 
ops$tkyte@ORA9IR2>   update ( select a,b,c,e,f,g
  2               from t1, t2
  3                      where t1.d = t2.d )
  4       set a = e, b = f, c = g;
 
5 rows updated.
 
ops$tkyte@ORA9IR2> select * from t1;
 
         A          B          C          D
---------- ---------- ---------- ----------
         2          3          4          1
         3          4          5          2
         4          5          6          3
         5          6          7          4
         6          7          8          5
 
ops$tkyte@ORA9IR2>


that demonstrates your issue and I'll be glad to take a look. 

Never mind, I figured it out.

Hi Tom

Once I had read your code of updating inline view which involves join. After that, I did it in real world only once that too without knowing the actual concept behind it. It reduced the query time more than 20 times. (What I did was, selected all primary key columns from both the tables in the inline view.) And since then, I never came across such requirements or I might have missed those opportunities.

From my statements, you can infer my experience / knowledge. Though I understood it partially, I couldn't explain to someone else in full confidence. So, here I would like to get your words in terms of Referential Integrity that exists between the tables that we use to join.

Let me try to my level:
-----------------------

Consider the statement

update ( select ... from A, B where A.id = B.id ... );

1) If there exists m to n relationship between A and B, the above statement fails.

2) If B refers to primary key or unique constraint of A, then its possible to update the columns of B and only B.

3) Vice versa of point (2) for the table A. (i.e., we can do update on Detail table but not on Master table).
-------------------------------------

And, I have another question. Can we do the view update when we select rowid as rid for a single table ?

Kindly validate my statements. I hope, your words would be useful to many Oracle beginners.

Thank you !!!

Tom

Thanks; I understood it better with your comments. And about the ROWID -> Just ignore it; I find myself it to be a question that would make no sense at all.

Hi Tom

I learned about the Key Preservation with the help of the following. Here I submit it, so that others may find it useful.

-- Set Up
col column_name format a30
CREATE TABLE m ( i INT );
CREATE TABLE d ( j INT );

CREATE VIEW md_upd AS
SELECT i,j FROM m,d WHERE i=j;

SELECT column_name, updatable
FROM user_updatable_columns
WHERE table_name = 'MD_UPD';

COLUMN_NAME UPDATABLE
------------------------------ ---------
I NO
J NO

ALTER TABLE m ADD CONSTRAINT pk_m PRIMARY KEY ( i );

ALTER VIEW md_upd COMPILE;

SELECT column_name, updatable
FROM user_updatable_columns
WHERE table_name = 'MD_UPD';

COLUMN_NAME UPDATABLE
------------------------------ ---------
I NO
J YES

ALTER TABLE d ADD CONSTRAINT pk_d PRIMARY KEY ( j );

-- ALTER TABLE d ADD CONSTRAINT fk_d_m FOREIGN KEY(j) REFERENCES m;
--
-- Its not requiring the foreign key constraint
-- but either primary key or unique key constraint
-- when we have join condt A.col_a = B.col_b
-- and if col_a is primary key, then columns of B are updatable
-- the vice versa is also true.

ALTER VIEW md_upd COMPILE;

SELECT column_name, updatable
FROM user_updatable_columns
WHERE table_name = 'MD_UPD';

COLUMN_NAME UPDATABLE
------------------------------ ---------
I YES
J YES

-- Clean Up
--
-- drop view md_upd;
-- drop table d;
-- drop table m;


Also, is this applicable for equi joins alone ? Could you explain for non-equi joins and outer joins ....

Thank you.

ops$tkyte%ORA10GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( a int primary key, b int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update ( select t1.y, t2.b from t1, t2 where t1.x = t2.a(+) )
  2  set y = b;

0 rows updated.

ops$tkyte%ORA10GR2> update ( select t1.y, t2.b from t1, t2 where t1.x = t2.a and t1.x <> t2.b )
  2  set y = b;

0 rows updated.

Hi Tom

I particularly asked about the view with Outer Join because, I encountered the following while digging something.

select * from user_updatable_columns
where ( updatable <> insertable or insertable <> deletable or deletable <> updatable );

TABLE_NAME         COLUMN_NAME UPDATABLE INSERTABL DELETABLE
-----------------  ----------- --------- --------- ---------
L1_TEST_EMP_DEPT   DEPT_NAME   YES       NO        YES

SQL> select text from user_views where view_name like 'L1_TEST_EMP_DEPT';

TEXT
-----------------------------------------------------------
select empno, ename, job, dept_name
from l1_test_emp a, l1_test_dept b
where a.deptno = b.dept_no(+)

(I'm not able to find other views with outer join. I used LIKE '%(+)%' with the LONG column to fail.)

Is the insertable, updatable, deletable differ only on such Outer Joins ?

Hi Tom

I asked about the columns of "user_updatable_columns".

SQL> desc user_updatable_columns
 Name                       Null?    Type
 -------------------------- -------- --------------
 OWNER                      NOT NULL VARCHAR2(30)
 TABLE_NAME                 NOT NULL VARCHAR2(30)
 COLUMN_NAME                NOT NULL VARCHAR2(30)
 UPDATABLE                           VARCHAR2(3)
 INSERTABLE                          VARCHAR2(3)
 DELETABLE                           VARCHAR2(3)

How the values (YES/NO) of "insertable, updatable, deletable" columns are determined? I found, for a view with Outer Join, UPDATABLE=YES, INSERTABLE=NO, DELETABLE=YES. All other views are having all three values to be either "NO" or "YES". Only view that has different values for these three columns is having the outer join. (And I am not able to find other views that have outer join). (Please see my previous review.)

ops$tkyte%ORA10GR2> create table t1 ( x int primary key, y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( a int primary key, b int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t1 add constraint t1_fk_t2 foreign key(x) references t2(a);

Table altered.

ops$tkyte%ORA10GR2> alter table t2 add constraint t2_fk_t1 foreign key(a) references t1(x);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace view vw
  2  as
  3  select *
  4    from t1, t2
  5   where t1.x = t2.a;

View created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from user_updatable_columns
  2  where table_name = 'VW';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
OPS$TKYTE                      VW                             X                              YES YES YES
OPS$TKYTE                      VW                             Y                              YES YES YES
OPS$TKYTE                      VW                             A                              YES YES YES
OPS$TKYTE                      VW                             B                              YES YES YES

Hi Tom,

create table t1(i int primary key, x varchar2(1));

create table t2(j int, y varchar2(1));

insert into t1 values(1,null);

insert into t1 values(2,null);

insert into t2 values(1,'A');

insert into t2 values(2,'B');

commit;

update (select * from t1, t2 where t1.i = t2.j)
set x = y
where i = j;


One table has Primary key and the other doesn't. When tried to run the update, got the expected error
message "cannot modify a column which maps to a non key-preserved table".

Is there any other alternative to run this update in a single statement ? Because, in my case t2 is an inline view.

Thanks,
Ravi Vedala

Thanks Tom. I was already using "Merge". I was trying to find out if there is any solution using the update statement.

Thanks,
Ravi

Thanks for the great explanation.

If the inline view does not have any joins, but it has an analytic function, for example, lead() function to get the value from next row, then the update is not allowed. Can this restriction be removed in the future version? If I use a merge statement, then there will be an expensive join between the two data sets.

hi Chris, the merge statement looks like this:

merge /*+ parallel(16) */ into my_table a
using
(
select rowid as rid,
....
last_value(... ignore nulls) over(partition by .... order by ... rows between unbounded preceding and 1 preceding) as new_value
from my_table
) b
on (a.partition_col=b.partition_col and a.rowid = b.rid)
when matched then update set ... = new_value

The table is 35gb and it is partitioned by partition_col column, I add it to the join trying to take advantage of partition-wise join, but I notice the analytic function prevents partition-wise join in the SQL plan, though the partition column is included in the "partition by" clause of the analytic function.

update t o
set col = (
  select col 
  from  t s
  where o.partition_cols = s.partition_cols
  and s.order_col < o.order_col
  order by s.order_col desc nulls last
  fetch first 1 row only
)

hi Chris,
This table has 127 million rows, which means the scalar subquery will be executed that many times. And each time it has to do a sorting. This option is not feasible for us(I did try it but had to cancel the execution after a while)

update (
  select t.*,  (
    select col 
    from  t s
    where o.partition_cols = s.partition_cols
    and s.order_col < o.order_col
    order by s.order_col desc nulls last
    fetch first 1 row only
  ) c
  from t
)
set col = c

hi Chris,
Could you tell me if unnesting happens in the below example? I don't see a join in the plan:

create table t1 as select * from all_users;

create table t2
as
select b.*
from all_users a ,
all_objects b
where a.username = b.owner;

create index t2x on t2(owner,created);

update (
select t1.*,
( select max(created)
from t2
where t2.owner = t1.username ) max_created
from t1
)
set created=max_created

Plan hash value: 1852196648

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 35 | 595 | 108 (33)| 00:00:01 |
| 1 | UPDATE | T1 | | | | |
| 2 | TABLE ACCESS FULL | T1 | 35 | 595 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | FIRST ROW | | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| T2X | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."OWNER"=:B1)

If I look at the plan for the inline view, I can see the join:

Plan hash value: 2006894985

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 4760 | 300 (13)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 35 | 4760 | 300 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 35 | 2135 | 3 (0)| 00:00:01 |
| 3 | VIEW | VW_SSQ_1 | 12 | 900 | 296 (13)| 00:00:01 |
| 4 | HASH GROUP BY | | 12 | 156 | 296 (13)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T2 | 38365 | 487K| 273 (6)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ITEM_1"(+)="T1"."USERNAME")

alter session set statistics_level = all;
set serveroutput off

update (
select t1.*,
( select max(created)
from t2
where t2.owner = t1.username ) max_created
from t1
)
set created=max_created;

select * from dbms_xplan.display_cursor( format => 'ALLSTATS LAST');

------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |      |      1 |        |      0 |00:00:00.01 |     180 |
|   1 |  UPDATE                       | T1   |      1 |        |      0 |00:00:00.01 |     180 |
|   2 |   TABLE ACCESS FULL           | T1   |      1 |     44 |     44 |00:00:00.01 |       2 |
|   3 |   SORT AGGREGATE              |      |     44 |      1 |     44 |00:00:00.01 |      88 |
|   4 |    FIRST ROW                  |      |     44 |      1 |     25 |00:00:00.01 |      88 |
|*  5 |     INDEX RANGE SCAN (MIN/MAX)| T2X  |     44 |      1 |     25 |00:00:00.01 |      88 |
------------------------------------------------------------------------------------------------