Subscribe - It's FREE!!

Stay Connected Here

Stay Updated With Us Here



Google

How to handle “no data found” error in Oracle PL/SQL


Share with WhatsApp


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.



If you enjoyed this post take 5 seconds to share it! Be Socialable. :-)

Share with WhatsApp

Posts To Read Next

Typewriter - Automatic TypeScript Template generation from C# classes in Visual Studio

Typewriter, an extension for visual studio that generates TypeScript files from C# code files using TypeScript templates. Convert your C# model classes to TypeScript automatically.


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

Visual Studio Code is a lightweight but powerful source code editor which runs on your desktop and is available for Windows, OS X and Linux. It comes with built-in support for JavaScript, TypeScript and Node.js and has a rich ecosystem of extensions for other languages (C++, C#, Python, PHP) and runtimes.


Assembla - Free and private repository to manage your source code online with SVN subversion hosting

With Assembla you can share source code with others online. Free & Private source code repository with SVN Subversion, Git & Perforce Hosting.


Top 10 Visual Studio things which can boost developers coding speed

Visual Studio 2012 provides some coding features by which you can code faster if use them properly. This post will cover top 10 things among them to boost your development speed.


Your opinion is valuable for us! Comments, suggetions are welcome.


Submit your Email Id to stay updated with us and get notified with our new posts. It's FREE!
Vu 32'' TV Vu 24'' TV
We know this popup is disturbing you!
But We would greatly appreciate if you share us with your friends below!

It will not take more than 2 seconds but will motivate us greatly to write more,share more!

x