plsql - Performing select operations with a cursor inside a caught exception -

i'm trying write stored procedure takes input of id searches 3 different tables item using different criteria. 1 solution have perform select statement on each table 1 one, , catching no_data_found exception if nothing found via select.

in pseudocode:

select item from first table     if no data found, throw exception.         handle exception selecting data second table             if no data found, throw exception.                  handle exception selecting data third table (if data not present in row should return 0 rows) 

here's have:

open request   select req_type, req_type_status  tableone   tableone.req_id = requestid   , (req_status = 'd' or req_status = 'a');   exception when no_data_found             begin                   open request                         select req_type, '-' req_type_status tabletwo                         tabletwo.req_id = requestid;                         exception when no_data_found                                   begin                                       open request                                       select req_type, '-' req_type_status thirdtable                                       thirdtable.req_id = requestid;                                   end;             end;   

if item found in tableone, returns data it. however, select operations performed inside caught exceptions don't seem run the stored procedure doesn't return rows.

i have separately verified data i'm searching exists in tabletwo and/or tablethree.

the syntax valid compiles, it's doesn't return rows if item doesn't exist in tableone (but exists in either tabletwo or tablethree).

any ideas?

credit @franek this.

the referenced linked article explains following:

cursor loop smart. won't raise no-data-found is; if there's nothing fetched, exit loop , terminate execution successfully. therefore, if meant handle exception - can not.

what attempting apparently not possible. therefore, have implemented way instead:

pseudocode:

search item in first table     if item found         select details of first table     if item not found         search item in second table             if item found                 select details of second table             if item not found                 select item third table (returning blank if it's not found here neither) 

pl/sql implementation:

-- search request id in first table select count(req_id) requestfound firsttable firsttable.req_id = requestid , (req_status = 'd' or req_status = 'a');  if(requestfound > 0)         -- select request details         open request         select req_id, req_type_status firsttable         firsttable.req_id = requestid         , (req_status = 'd' or req_status = 'a'); else         -- search request second table         select count(req_id) requestfound secondtable         secondtable.req_id = requestid;          if(requestfound > 0)                   -- select request details second table                   open request                   select req_type, '-' req_type_status secondtable                   secondtable.req_id = requestid;           else                   -- request third table (will return blank if nothing found)                   open request                   select req_type, '-' req_type_status  thirdtable                   thirdtable.req_id = requestid;           end if; end if;