ORACLE/PLSQL: AVOID "DATA NOT FOUND" ERROR IN PLSQL CODE - OracleConnections2024-03-28T09:33:07Zhttp://www.oracleconnections.com/forum/topics/oracle-plsql-avoid-data-not-found-error-in-plsql-code?commentId=6612035%3AComment%3A50162&xg_source=activity&feed=yes&xn_auth=noWell in my opinion the good i…tag:www.oracleconnections.com,2014-11-06:6612035:Comment:501622014-11-06T10:42:59.703ZImran Amjadhttp://www.oracleconnections.com/profile/ImranAmjad
Well in my opinion the good idea is to use built-in NO_DATA_FOUND exception for this purpose,<br />
for example;<br />
<br />
BEGIN<br />
select msa_code, mda_desc, zip_code_nk<br />
from sales.msa<br />
where zip_code_nk = prod_rec.zip_code_nk;<br />
EXCEPTION when no_data_found then<br />
null; -- Or code whatever you want here.<br />
END;<br />
<br />
-- The big advantage is that you are avoiding an extra count SELECT from a database<br />
-- using built-in functionality
Well in my opinion the good idea is to use built-in NO_DATA_FOUND exception for this purpose,<br />
for example;<br />
<br />
BEGIN<br />
select msa_code, mda_desc, zip_code_nk<br />
from sales.msa<br />
where zip_code_nk = prod_rec.zip_code_nk;<br />
EXCEPTION when no_data_found then<br />
null; -- Or code whatever you want here.<br />
END;<br />
<br />
-- The big advantage is that you are avoiding an extra count SELECT from a database<br />
-- using built-in functionality Thankstag:www.oracleconnections.com,2014-02-24:6612035:Comment:460392014-02-24T12:52:22.121ZKari Mennellahttp://www.oracleconnections.com/profile/KariMennella
<p>Thanks</p>
<p>Thanks</p> Nice answer Valentine.. !!tag:www.oracleconnections.com,2014-02-21:6612035:Comment:460342014-02-21T13:36:31.889ZWm Ectorhttp://www.oracleconnections.com/profile/WmEctor
<p>Nice answer Valentine.. !!</p>
<p>Nice answer Valentine.. !!</p> Jae thank you for posting tha…tag:www.oracleconnections.com,2014-01-13:6612035:Comment:455822014-01-13T12:14:10.354ZJere Kleinerhttp://www.oracleconnections.com/profile/JereKleiner
<p>Jae thank you for posting that question...<br/> and <br/>Valentine nice answer its very helpful for me. Your Query also Solved my Data Not Find <span style="font-size: 13px;">problem. :)</span></p>
<p>Jae thank you for posting that question...<br/> and <br/>Valentine nice answer its very helpful for me. Your Query also Solved my Data Not Find <span style="font-size: 13px;">problem. :)</span></p> Jae nice Question use this c…tag:www.oracleconnections.com,2014-01-13:6612035:Comment:455792014-01-13T12:09:50.345ZValentine Dambrosiahttp://www.oracleconnections.com/profile/ValentineDambrosia
<p>Jae nice Question<br/> use this code, hope it will be helpfull to you Thank you.</p>
<p></p>
<pre class="pre_color">select COUNT(1) into v_count<br/> from sales.msa<br/> where zip_code_nk = prod_rec.zip_code_nk;<br/> if v_count > 0 then<br/> select msa_code, mda_desc, zip_code_nk <br/> from sales.msa <br/> where zip_code_nk = prod_rec.zip_code_nk; <br/>end if;<br/><br/><br/></pre>
<p>Jae nice Question<br/> use this code, hope it will be helpfull to you Thank you.</p>
<p></p>
<pre class="pre_color">select COUNT(1) into v_count<br/> from sales.msa<br/> where zip_code_nk = prod_rec.zip_code_nk;<br/> if v_count > 0 then<br/> select msa_code, mda_desc, zip_code_nk <br/> from sales.msa <br/> where zip_code_nk = prod_rec.zip_code_nk; <br/>end if;<br/><br/><br/></pre> To prevent the PLSQL code fr…tag:www.oracleconnections.com,2014-01-13:6612035:Comment:455762014-01-13T12:07:23.466ZHorace Iliffhttp://www.oracleconnections.com/profile/HoraceIliff
<p><span> To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned.</span></p>
<p><span> To prevent the PLSQL code from dropping to the exception code when a record is not found, you'll have to perform a count first to determine the number of records that will be returned.</span></p>