Oracle NO_DATA_FOUND exception for SELECT INTO

When a SELECT INTO does not return anything the NO_DATA_FOUND exception is raised. The exception is not raised if a aggregate function like SUM or AVG is used, but we cannot use that for a varchar column. So, I am now catching the NO_DATA_FOUND exception in my procedure.
 
SELECT INTO VARIABLE_NAME
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
             VARIABLE_NAME :=0; –DEFAULT value of VARIABLE_NAME
             DBMS_OUTPUT.PUT_LINE(”);
     WHEN OTHERS THEN
      ROLLBACK;
      MESSAGE :=
            MESSAGE
         || ‘SQLCODE: ‘
         || SQLCODE
         || ‘ SQLERRM:’
         || SUBSTR (SQLERRM, 1, 100);

      DBMS_OUTPUT.put_line (‘ERROR: ‘ || MESSAGE);

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