Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Select INTO and Forms 5.0 question
On Sun, 18 Jan 1998 19:25:08 GMT, "Sandy Thrasher" <sandra.thrasher_at_MCI2000.com> wrote:
>
>-----Original Message-----
>From: Karl E. Jørgensen <kjorg_at_msn*DOT*com>
>Newsgroups: comp.databases.oracle.server
>Date: Friday, January 16, 1998 3:56 PM
>Subject: Re: help with select into
>
>
>>It doesn't make much sense to reference SQL%NOTFOUND after a SELECT ...
INTO
>>... FROM... etc. For example:
>>
[snip]
>>
>>The reason is that Oracle treats it as an error if your query does not
>>return any rows. Thus the select...into... WILL raise an exception
>>(no_data_found). Without this, you would not know whether you got a value
>>back or not !
>>
>
>Oracle does not treat it as an error my version of Oracle Forms (4.5). If
>you use
>SQL%NOTOFOUND after a select, an exception is not raised. I've used it and
>seen it used quite often in Forms (but not in Stored procedures). Is this
>no longer true in the new version of Forms (5.0)? Does it not work in
>Stored Procedures or SQL*PLUS? I'm planning on upgrading to Forms 5.0 and
>I'd like to know what I'm up against.....
>
Can you give us an example? I'm using Forms [32 Bit] Version 4.5.8.2.0 (Production) on NT and when I code a WHEN-NEW-FORM-INSTANCE trigger with:
declare
l_dummy varchar2(255);
begin
select dummy
into l_dummy
from dual
where 1 = 0;
if sql%notfound then
Message( 'It was not found and we are OK' );
end if;
end;
I get as a message when the form starts:
FRM-40735: WHEN-NEW-FORM-INSTANCE trigger raised unhandled exception NO_DATA_FOUND Which is exactly what it is supposed to do (and as far as I know has always done). the only way to handle a SELECT .. INTO .. that may not return a row (or may return >1 row) is to use an execption block. the SQL%NOTFOUND doesn't work that way in any environment with SELECT .. INTO .. You cannot catch the not found condition the way you describe.
>Thanks,
>
>Sandy
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jan 18 1998 - 00:00:00 CST