Re: Preventing ORA-1403 in triggers

OK, send your developer back to F(ine) M. There are 2 ways:

Trap it

begin
  select description
  into v_desc
  from my_table
  where column="this don't exist" ;
exception
  when no_data_found then
    do some thing else
end

Or use a cursor

  cursor csr is
  select description
  into v_desc
  from my_table
  where column="this don't exist" ;
begin
  open csr ;
  fetch csr into v_desc ;
  if csr%NOTFOUND then
    close csr ;
    do something else ;
  end if ;
  close csr;
end ;

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
"Hit any user to continue..."

                                                                                       
         
                    Rich.Jesse_at_qt                                                      
         
                    iworld.com           To:     ORACLE-L_at_fatcity.com                  
         
                    Sent by:             cc:                                           
         
                    root_at_fatcity.        Subject:     Preventing ORA-1403 in triggers  
         
                    com                                                                
         
                                                                                       
         
                                                                                       
         
                    12/10/01                                                           
         
                    01:01 PM                                                           
         
                    Please                                                             
         
                    respond to                                                         
         
                    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: Ron Thomas
  INET: rthomas_at_hypercom.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).