Select count(*) or Select ‘X’ ??

One of the most confusing “stuff” I had with PLSQL coding! After spending these many years developing more than a dozen business applications, I still try to negotiate between Select count(*) and Select ‘X’ approaches while trying to find out whether a set of rows or particular information is available with a particular repository!

Select count(*)into some_local_variable from dual where1=2 would always return ‘0’, thus nullifying the scope of raising an exception unless further coding done from PL/SQL prospectives, as following example

if NOT(some_local_variable>0) then

message(‘Nothing found, raising error’);

raise form_trigger_failure;

end if;

While within a Begin Scope

Begin

Select ‘X’ into some_local_variable from dual where1=2;

Exception

when no_data_found then

message(‘Nothing found, raising error’);

raise form_trigger_failure;

End;

Would look more professional approach towards handling the situation. Finally the choice is entirely upon the developer, based on requirements, both may look most appropriate.

Hope this post was useful for few out there!

Regards,

Admin