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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: