RE: Preventing ORA-1403 in triggers

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 10 Dec 2001 12:36:37 -0800
Message-ID: <F001.003D938E.20011210122334@fatcity.com>

Rich,

Well, just what would you like to do if the record does not exist? You could easily trap the error and move on:

begin

      notfound := 'N';
        SELECT description INTO v_desc
        FROM    mytable
        WHERE   indexcol1 = :v_col1
        AND indexcol2 = :v_col2
         on exception
            when no_data_found then
                 notfound := 'Y'

end;

if notfound = 'Y' then

do something...
end if;

end;

You basically can handle all exceptions once you know what it is you want to do if the exception occurs.

Hope this helps

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, December 10, 2001 3:02 PM
To: Multiple recipients of list ORACLE-L

Hi,

So a developer asks me "How do I prevent ORA-1403 in my SELECT statement in my trigger?" I thought this would be simple, but my brain's just not all here.

The statement is simple:

        SELECT description INTO v_desc
        FROM    mytable
        WHERE   indexcol1 = :v_col1
                AND indexcol2 = :v_col2

The problem is that we don't know if the user-entered "indexcol"s exist. I could preface this statement with a "SELECT count(*) INTO mycount..." to see if there are existing rows, but I was hoping to do this all in one SQL statement if possible, especially if/when this scenarios will happen with non-indexed columns (don't ask).

Anyone?

TIA!

Rich Jesse                          System/Database Administrator
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------