| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: PL/SQL question - number of rows inserted
At 12:13 PM 1/26/96 GMT, you wrote:
>PL/SQL question ...
>
>I have the following statement in a PL/SQL block
>
> INSERT INTO tablea
> SELECT * FROM tableb WHERE x=y;
>
>(or something very similar)
>
>This always succeeds no matter if the sub-select returns any rows or not.
>
>In my application inserting no rows is an error.
>
>How do I tell if there were any rows inserted (%ROWCOUNT is always 0,
SQLCODE is
>always 0)?
>
>I could always run a cursor like
>
> SELECT * FROM tableb WHERE x=y
> AND ROWNUM < 2;
>
>And if this returns nothing not bother with the INSERT (simply envoke somekind
> of error).
>However, what if the sub-select is
>
> SELECT x,y,z FROM bigtablea, bigtableb, bigtablec... WHERE EXISTS (SELECT
...)
> etc etc etc
>
>then the performance hit would be too great.
>
>I could always use a row insert trigger on tablea which sets a package global
> variable
>whenever a row is actually inserted into tablea and look at this after the
> insert has
>completed (remembering to clear it down before the INSERT) but this meeans
> writing a
>trigger and a package and all the maintenance and DBA bother that involves.
>
>Anybody got any better ideas?
>
>Tim Onions
>Principle Technical Consultant AT&T Istel (UK)
>
>
After the 'INSERT' statement, check SQL%NOTFOUND. In case no rows have been inserted SQL%NOTFOUND would be true. I don't know why Oracle doesn't raise NO DATA FOUND exception in this case.
HTH.
Senior Software Engineer Ph. (011-8)-562585, 562588-92 Tata Unisys Limited, Fax. (011-8)-56584Computer Consultancy Division, Tlx. 08905-211 TULN IN SDF#A/5-A/6,
 |
 |