Sure, it looks very much the same:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      type array is table of varchar2(20);
  3  
  4      l_deptno array := array( 100,10,20,30 );
  5      l_return_deptno array;
  6      l_dname  array := array( 'dept1', 'dept2', 'dept3', 'dept4' );
  7  begin
  8      forall i in 1 .. l_deptno.count
  9        UPDATE DEPT
 10           SET DNAME = l_dname(i)
 11         WHERE DEPTNO = l_deptno(i)
 12     RETURNING DEPTNO BULK COLLECT INTO l_return_deptno;
 13  
 14      for i in 1 .. l_return_deptno.count
 15      loop
 16          dbms_output.put_line( 'updated deptno = ' || l_return_deptno(i) );
 17      end loop;
 18  end;
 19  /
updated deptno = 10
updated deptno = 20
updated deptno = 30

PL/SQL procedure successfully completed.

 

Always something more ;)   

Ok, here is a quickie example in PLSQL that shows one alternative method:

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table dept;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table dept as select * from scott.dept;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      type array is table of varchar2(20);
  3  
  4      l_deptno array := array( 99,10,20,30 );
  5      l_return_deptno array;
  6      l_dname  array := array( 'dept1', 'dept2', 'dept3', 'dept4' );
  7  begin
  8      forall i in 1 .. l_deptno.count
  9        UPDATE DEPT
 10           SET DNAME = l_dname(i)
 11         WHERE DEPTNO = l_deptno(i)
 12     RETURNING DEPTNO BULK COLLECT INTO l_return_deptno;
 13  
 14     forall i in 1 .. l_deptno.count
 15        insert into dept ( deptno, dname )
 16        select l_deptno(i), l_dname(i)
 17          from dual
 18         where NOT EXISTS (select null from dept where deptno = l_deptno(i));
 19  
 20      for i in 1 .. l_return_deptno.count
 21      loop
 22          dbms_output.put_line( 'updated deptno = ' || l_return_deptno(i) );
 23      end loop;
 24  end;
 25  /
updated deptno = 10
updated deptno = 20
updated deptno = 30

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 dept2          NEW YORK
        20 dept3          DALLAS
        30 dept4          CHICAGO
        40 OPERATIONS     BOSTON
        99 dept1


and if you have 9i, you can do it in one statment:

ops$tkyte@ORA9I.WORLD> drop table dept;

Table dropped.

ops$tkyte@ORA9I.WORLD> create table dept as select * from scott.dept;

Table created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> declare
  2      type array is table of varchar2(20);
  3  
  4      l_deptno array := array( 99,10,20,30 );
  5      l_return_deptno array;
  6      l_dname  array := array( 'dept1', 'dept2', 'dept3', 'dept4' );
  7  begin
  8      forall i in 1 .. l_deptno.count
  9        merge into dept
 10        using ( select l_deptno(i) deptno, l_dname(i) dname from dual ) d
 11        on ( dept.deptno = d.deptno )
 12        when matched then
 13             update set dept.dname = d.dname
 14        when NOT matched then
 15             insert ( deptno, dname ) values ( d.deptno, d.dname );
 16  end;
 17  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 dept2          NEW YORK
        20 dept3          DALLAS
        30 dept4          CHICAGO
        40 OPERATIONS     BOSTON
        99 dept1

 

You tell ME what is acceptable. Me, I would reject the batch (easiest to code, never understood the benefit of a partial batch). In that case -- just ROLLBACK when you get the error and all is UNDONE. You could go a step further and use savepoints:

begin
savepoint foo;
insert
update
merge
whatever.....
exception
when others then rollback to savepoint; RAISE;
end;

You have to retry the insert, passing array+5 as the host variable (to skip over the rows loaded and the bad row).

Even SQLLDR does this.

the update suceeded.

hence, it returned the value -- NULL (unknown).

No but, desired, expected behaviour.

If not what you want, code:

update ... returning into some_temp_variable;
if (sql%rowcount > 0 ) then
v_total_amount := some_temp_variable;
end if;

"no rows found" is not an error, no problem, not an issue with an update. It is perfectly normal in fact.

savepoints are internal to your logic (hopefully, you would never need query them as transaction control is so vital, so important -- that you would know exactly where they are)


When you commit -- this information is "not relevant". It is not that it is lost, it quite simply isn't relevant.


A savepoint is just the ability for you to rollback a subset of your transaction -- it affects commits not at all, not even a little bit.

it does not appear to be a supported construct -- it only works with "values", not select (which seems backwards since values can only insert a row -- bulk collect doesn't make sense)

nope.

short of

a) bulk collect them
b) forall i insert them

instead of insert into as select -- not really.

you cannot, they are "merged" -- period.

some are inserted, some are updated -- all are "merged"

No, there is not (for both questions)


statements are always atomic - they either happen 100% sucessfully, or they do not happen at all.

"no matching record found" is NOT AN ERROR in any way shape or form!

(i never understand this point of confusion really -- select * from t where 1=0 is not an error, update t set x = 55 where 1=0 is NOT and error and so on)


If you update by primary key -- and you bulk collect back the primary keys (and you don't update the primary keys), you'll know what rows got updated and which did not.

deptno would be the primary key here.

if I send in 4 deptnos -- how many rows will I update? 4.......

1) ora error
2) sqlca.sqlerrd[2] tells you the number of rows affected by DML


static void die(void)
{
char errmsg[513];
int length;
int real_length = sizeof(errmsg);

exec sql whenever sqlerror continue;
sqlglm( errmsg, &real_length, &length );
errmsg[length] = 0;
fprintf( stderr, "\n%s\n", errmsg );
fprintf( stderr, "sqlcode = %d\n", sqlca.sqlcode );
exit(1);
}



void process(void)
{
exec sql begin declare section;
int i;
int empnos[10];
exec sql end declare section;

exec sql whenever sqlerror continue;
exec sql drop table emp;

exec sql whenever sqlerror do die();
exec sql create table emp as select * from scott.emp;

exec sql delete from emp where rownum <= 5
returning empno into :empnos;

printf( "%d rows affected\n", sqlca.sqlerrd[2] );
exec sql rollback;

exec sql delete from emp returning empno into :empnos;
}


will print out:


[tkyte@xtkyte-pc t]$ ./t
connecting
connected
5 rows affected

ORA-24343: user defined callback error

sqlcode = -24343
[tkyte@xtkyte-pc t]$


so, 5 rows affected the first time and ora-24343 the second.

It would not necessarily insert 10 rows with 5 zeros.

it would insert 10 rows (assuming the data passed constraints) with 5 of the rows having "garbage" in them since you never initialized the array.

So, you use the "for :array_size" to control the number of rows to insert. It is the only correct way to do it.

how do you know it updated only N rows? what does the code look like

reproduces in 9i, but not 10g....

Write consistency, they messed up, I would definitely consider this a product issue.  Do you have support?  I have a really small test case for you to use:

drop table t;
set echo on
                                                                                                                    
                                                                                                                    
create table t ( x int, y int );
insert into t values ( 1, 1 );
insert into t values ( 2, 2 );
                                                                                                                    
create or replace trigger t_trigger
before update on t for each row
begin
    dbms_output.put_line( 'old: ' || :old.x || ', ' || :old.y );
    dbms_output.put_line( 'new: ' || :new.x || ', ' || :new.y );
end;
/
                                                                                                                    
update t set y = 42 where x = 2;
set echo off
prompt in another window:
prompt
prompt declare
prompt     type array is table of rowid;;
prompt     l_data array;;
prompt begin
prompt     update t
prompt        set y = 55
prompt  returning rowid bulk collect into l_data;;
prompt  dbms_output.put_line( sql%rowcount );;
prompt  dbms_output.put_line( l_data.count );;
prompt  for i in 1 .. l_data.count loop
prompt     dbms_output.put_line( l_data(i) );;
prompt  end loop;;
prompt end;;
prompt /
set echo on
pause
commit;


the other session will show:

ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
 
  1  declare
  2  type array is table of rowid;
  3  l_data array;
  4  begin
  5  update t
  6  set y = 55
  7  returning rowid bulk collect into l_data;
  8  dbms_output.put_line( sql%rowcount );
  9  dbms_output.put_line( l_data.count );
 10  for i in 1 .. l_data.count loop
 11  dbms_output.put_line( l_data(i) );
 12  end loop;
 13* end;
ops$tkyte@ORA9IR2> /
old: 1, 1
new: 1, 55
old: 2, 2
new: 2, 55
old: 1, 1
new: 1, 55
old: 2, 42
new: 2, 55
2
3
AAAK5CAAGAAAABIAAA
AAAK5CAAGAAAABIAAA
AAAK5CAAGAAAABIAAB


If write consistency doesn't mean anything to you -- it is OK, I made it up.  This describes it however:

http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html http://asktom.oracle.com/Misc/part-ii-seeing-restart.html http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

I didn't anticipate that happening - in fact I almost hit submit on an answer with "beats me..." and just before I hit it, I thought "i wonder" and sure enough - it was the cause. 

this is going to be problematic on many fronts.

First, you'll get an implementation restriction trying to access hrs_tab(i).customer_id in the forall.

Second, you would have to use a SET operation on a collection to distinct the collection for insertion - that would preclude the use of bulk collect on the insert.

Third, you would need an "where not exists" or "where not in" in the set operation as well - since customer_id = 100 could be in the first set of fetched rows AND the second set of fetched rows AND the third set of fetched rows.


Begs the question - why not just insert into select distinct in the first place and not even consider doing this procedurally?

the implementation restriction is that it won't work.



ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> declare
  2          cursor c is select * from emp;
  3          type array is table of c%rowtype index by binary_integer;
  4          l_data array;
  5  begin
  6          open c;
  7          fetch c bulk collect into l_data;
  8          close c;
  9          forall i in 1 .. l_data.count
 10             insert into t (x) values ( l_data(i).empno );
 11  end;
 12  /
           insert into t (x) values ( l_data(i).empno );
                                      *
ERROR at line 10:
ORA-06550: line 10, column 32:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND
table of records
 

you would have to read that out via a select before doing the update.

if you have 15,000 records to return - and you use returning - well, you would have to have an array that large.

if you stick with select for update, and then bulk update N records, you would of course limit your exposure of rows to N at a time

ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level l from dual connect by level <= 10)
  4  select album_id, substr(flag,l,1) v
  5    from my_music, data
  6   where length(flag) >= l
  7   order by album_id, v
  8  /

  ALBUM_ID V
---------- -
         1 A
         1 B
         2 C
         2 K
         2 M
         2 S
         3 E
         3 R
         3 S
         3 T
         3 W

11 rows selected.

                   exception
                   when others then
                   dbms_output.put_line('EXCEPTION' || sqlcode || sqlerrm);
                   dbms_output.put_line('EXCEPTION' || v_client_id.count);
                   dbms_output.put_line('EXCEPTION' || v_count);
                   end;

gag, sputter, cough, cry, so sad. I'll never ever understand WHY anyone does that. Never. Not in a billion years ever.


But, anyway, it is working as designed. SQL statements are by design atomic - they either entirely succeed or entirely fail. The delete either deletes perfectly every row - or not.


You might be interested in DML error logging in this case, you can have errors silently logged to a TABLE that you can query afterwards to see what went wrong.

http://asktom.oracle.com/Misc/how-cool-is-this.html

the statement failed - did not happen. The outputs from it are not 'sensible', cannot be used.

It is as if the statement never happened - the things that would be set upon successful completion of the statement - not set, they don't mean anything.

why has it lost it's charm?

You hit an exception
variables being processed in the block of code the exception comes from are necessarily "unstable" - you were in the middle of processing them. The statement that raised the exception didn't happen for all intents and purposes - so any side effects it would have had (had it worked) cannot be used.

... RETURNING CLAUSE should be UNDEFINED ...

You should consider them as "undefined" - the numbers, the values you see - garbage, not define, not usable, not useful, not finished, not done, not fully baked - undefined, as if they were uninitialized and had garbage left in them.

I fail to see how this makes exceptions "lose their charm"???? You hit and error, you were teleported to the exception block - the variables that were in the PROCESS of being set are "undefined", their value is "not defined, not reliable, not meaningful, not set". I don't see why you would even consider peeking at them? They are obviously "nothing of use to you" at that point.