- Posted On 22 October 2014
- In Programming
This simple and short post covers information about how you can handle “No data found” error in Oracle PL/SQL.
I am very much comfortable with Microsoft SQL Server but as a requirement of one of my project I recently started to work on ORACLE and at very early stage I faced an error “No data found” which occurs when you do select into some variable with some conditions in where clause.
In SQL, it gets automatically handled when there is no data or output of particular select and you tries to do select into but in oracle it throws due to which you need to do double-check just to ensure that data exists.
Suppose there is one table called ‘employee’ which has records for employee id 1 to 10 and if you wrote a query like below.
Select emp_name into v_emp_name from employee where emp_id = 11;
In above case it will throw an error no data found as table employee doesn’t gold record for employee whose id is 11.
So to handle above case we have two choices. One is to check the count before select into statement like below.
declare v_emp_count number;v_emp_name varchar2(20); begin select count(*) into v_emp_count from employee where emp_id = 11; if (v_emp_count > 0) then select emp_name into v_emp_name from employee where emp_id = 11; else v_emp_name := null; end if; end;
Another way is handle exception like below.
declare v_emp_count number;v_emp_name varchar2(20); begin select emp_name into v_emp_name from employee where emp_id = 11; EXCEPTION when no_data_found then v_emp_name := null; end;
Hope you have found this post useful. If you any other better alternative do share in comment section below. Thanks.
- Tags :
Swagger - Powerful Opensource Framework for RESTful API
With a Swagger-enabled API, you get interactive documentation, client SDK generation and discoverability. Swagger Editor allows you to generate code, documentation with ease.
Visual Studio Code - Lighweight but powerful source code editor available for Windows, OS X and Linux
How to post JSON string as a string in jQuery AJAX
Know how to post JSON String as string to the action method of controller in MVC.
How to call click or any event only once in jQuery
Know how to execute an click event or any event only once for any element in jQuery. Perform action only once and even not required to unbind event.