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

From: <ctcgag_at_hotmail.com>
Date: 04 Feb 2003 20:48:52 GMT
Message-ID: <20030204154852.052$ws@newsreader.com>

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote:
> "FC" <flavio_at_tin.it> wrote in message
> news:CBT%9.180453$AA2.7097288_at_news2.tin.it...
> > Hello folks,
> > I just ask you whether I misunderstood the documentation or it's a
> > PL/SQL bug.
> > Facts:
> > Oracle 8.1.7.0.0 EE, Windows 2000 Pro.
> >
> > ...
> > Delete from configurations
> > where cfg = num_cfg -- this
> > is
> a
> > PK on table
> > returning cfg_dsc into var_cfg_description;
> > ...
> >
> > Now, I would expect an exception like NO_DATA_FOUND to be raised if
> num_cfg
> > does not match any existing record, because the returning clause cannot
> > return any value, but this doesn't happen.
> >
> > Of course there are simple workarounds if one needs to raise an error
> > but
> I
> > didn't find any comment about this behaviour in the PLSQL user's guide
> > and reference, is this normal?
> >
> > Bye,
> > Flavio
> >
> >
> Absolutely normal, IMHO.

>

> Delete from emp where 'true' = 'false';
> Update emp set salary = salary * 2 where 0 = 1;

>

> These are both perfectly legitimate. It just so happens that no rows meet
> the criteria.

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.

Xho

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

Received on Tue Feb 04 2003 - 14:48:52 CST