NO DATA FOUND when selecting from temporary table

Question

The following code block will sometimes error with "ORA-01403: NO DATA FOUND". In the first query, I retrieve data stored in a table on another database, and I store that data into a temporary table. In the second query, I select the data out of the temporary table INTO a local variable. I have to do this because one of the fields is a CLOB and it is not possible to select a CLOB into a local variable across a database link.

-- insert clob from remote database into temp table across database link
-- it will be deleted upon implicit commit when the report ends;
INSERT INTO  tmp_xml_result
SELECT add_id, site_cd, result_txt
FROM vw_add_result@ADifferentServer
WHERE add_id = p_add_id
    AND site = p_site;

-- now that clob is local, we can select it into a variable
SELECT xml
INTO v_xml
FROM tmp_xml_result
WHERE id = p_add_id
    AND site = p_site;

This block does not always error. When it does error, the second query throws NO DATA FOUND.

How can I adjust this code block so that it never results in a "ORA-01403: NO DATA FOUND" error? I know I can wrap the block in a BEGIN / EXCEPTION WHEN NO_DATA_FOUND... / END block, but the ideal solution will return the contents of the temporary table once they are available.

My environments use "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production".

"but the ideal solution will return the contents of the temporary table once they are available." - Could you please elaborate on this? I think using an EXCEPTION block to handle no_data_found would be a better idea. — Kaushik Nayak
Why does your code occasionally hurl NO_DATA_FOUND? Could it be that sometimes you're querying your remote view with criteria that don't return any rows? If so, wouldn't you want to know that? — APC

dandarc dandarc · Accepted Answer · 2018-09-04T22:40:59

Check sql%Rowcount after the insert - if it is > 0, then proceed, otherwise do something else.

Or run a count(*) query after the insert - if it is 0, don't run your select . . into.

Or add an exception block to catch the ORA-01403.

NO DATA FOUND when selecting from temporary table

1 Answers