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

From: kn <keldn_at_kill.spam>
Date: Wed, 5 Feb 2003 02:04:37 +0100
Message-ID: <3e406354$0$224$edfadb0f@dread16.news.tele.dk>

"FC" <flavio_at_tin.it> wrote in message
news:Q%X%9.182761$AA2.7180217_at_news2.tin.it...
>
> "kn" <keldn_at_kill.spam> wrote in message
> news:3e4040f0$0$184$edfadb0f_at_dread16.news.tele.dk...
> > <snip>
> > >
> > > I agree, both are perfectly legimitate.
> > >
> > > However,
> > >
> > > Select salary from emp where 0=1;
> > >
> > > is also perfectly legimitate, yet it throws a 'no data found'
> > > exception at runtime if you add the 'into':
> > >
> > > Select salary into sal from emp where 0=1;
> > >
> > > I would have thought that a 'returning...into' would behave the same
> > > as 'select...into' does. It may be normal, but it doesn't seem
> > > particularly intuitive or consistent.
> > >
> > then an update returning of say 3 rows. should raise to_many_rows ?
> >
>
>
> 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) ?

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;

  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 ?

END; courtesy Tom Kyte (asktom.oracle.com) Received on Tue Feb 04 2003 - 19:04:37 CST