elmama
​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


Outputting a customised  "no rows found" message from SQLPLUS


Below is my answer to a user who wanted a customised message to be output from SQLPLUS whenever no data was found from a particular query. So, normally if you SELECT from a  table in Oracle where no data is found you get the following:


  1  select * from scott.emp
  2* where ename = 'JACOB'
SQL> /

no rows selected

The questioner in this case wanted something like


There are no rows to select output instead of no rows selected


Here is my reply:


  1  select empno || ' ' || ename data
  2  from emp
  3* where ename = 'FORD'
SQL> /

DATA
---------------------------------------------------
7902 FORD



  1  select empno || ' ' || ename data
  2  from emp
  3* where ename = 'FORD PREFECT'
SQL> /

no rows selected


  1  select case
  2  when z.ename = chr(255) then 'There are no rows to select'
  3  else z.data end data
  4  from (
  5  select ename, ename || ' ' || empno data, rownum rn
  6  from emp
  7  where ename = 'FORD'
  8  union all
  9  select chr(255),chr(255) || ' ' || -999999 ,-999999
 10  from dual
 11  ) z
 12* where decode(rownum, 1, z.rn, rownum) = z.rn
SQL> /

DATA
---------------------------------------------------
FORD 7902


  1  select case
  2  when z.ename = chr(255) then 'There are no rows to select'
  3  else z.data end data
  4  from (
  5  select ename, ename || ' ' || empno data, rownum rn
  6  from emp
  7  where ename = 'FORD PREFECT'
  8  union all
  9  select chr(255),chr(255) || ' ' || -999999 ,-999999
 10  from dual
 11  ) z
 12* where decode(rownum, 1, z.rn, rownum) = z.rn
SQL> /

DATA
---------------------------------------------------
There are no rows to select

.