Re: Bug or Misunderstanding ? Delete...Returning into does not work as I expected

"kn" <keldnielsen<nope>@<nope>image.dk> wrote:
> "FC" <flavio_at_tin.it> wrote in message

> > Exactly, because that is what happens if you perform a SELECT ... INTO,
> > so why not ?
> > It'd perfectly acceptable.
> >
> > Flavio
> >
>
> no, it would not be acceptable because
>
> if an exception was raised how many rows were you then able
> to determine that were succesfully updated (or not) ?

It would work the same way any other exception would work.

> DECLARE
> TYPE idlist IS TABLE OF liga.gangraekkefoelge.id%TYPE;
> TYPE datolist IS TABLE OF liga.gangraekkefoelge.fra_dato%TYPE;
> id_array idlist;
> dato_array datolist;
> sql_stmt VARCHAR2(200);
> BEGIN
> sql_stmt := 'UPDATE gangraekkefoelge SET pending = '||
> '''N'''||' WHERE id = :1 RETURNING fra_dato INTO :2';
> id_array := idlist(3210,3211,3212,3213,3214);
> FORALL i IN 1..id_array.COUNT
> EXECUTE IMMEDIATE sql_stmt -- not that I'm especially fond of exe
> imme
> USING id_array(i)
> RETURNING BULK COLLECT INTO dato_array;

It should be fairly obvious that a RETURNING BULK COLLECT INTO is a different critter than a RETURNING INTO, in regards to this discussion. Even if not, your objection is still baseless.

> FOR i IN 1..dato_array.COUNT LOOP
> dbms_output.put_line(TO_CHAR(dato_array(i),'DD-MM-YYYY'));
> END LOOP;
> ----------------------exception
> ----------------------hmm, then what ?

Any exception (even other than too_many_rows), then what, hmmm, hmmm?

> END;
The Update is atomic. Even with dynamic SQL inside a forall loop, it still is appears to be atomic. How many rows were updated if the exception gets thrown? Zero. If it were otherwise, you'd be no more screwed with a too_many_rows exception than you would be with any other exception.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB