SQLTeam.com | Weblogs | Forums

Error message when no rows returned


I need a error message to be displayed when there are no rows returned

eg:suppose the expected entry is

emp_no emp_name
100 paddy

select * from employees where emp_no=100 and emp_name='paddy';
doesnt return a row
I need something like

emp_no emp_name error
100 paddy no matching rows

if yes then
emp_no emp_name error
100 paddy matching rows

If possible can I display the error message as red in color.

am using oracle sql developer


This is a Microsoft SQL Server forum, so there would be few people, if any, who can advise you on Oracle.

In SQL Server, formatting and presenting the data is deferred to the presentation layer entities such as SQL Reporting Services, or other front-end code/applications. The SQL Server engine has no features that allows displaying the query results in a different color etc.


Thanks James could you help without the Color then..just the logic on how to frame it


In MS SQL, you could use @@ROWCOUNT to see how many rows were returned by the last query. Then, if it's 0, you can display a message saying so. For instance:

select *
from employees

if @@ROWCOUNT = 0
select 'No rows selected.'

I just googled it, and it looks like SQL%ROWCOUNT is the Oracle version of that variable, but I'm no Oracle expert.