ORA-01403: No Data found WHY?

Question

I have declared the following procedure:

 CREATE OR REPLACE PROCEDURE MODIFY_NOT_NULL(
      v_tbName       IN VARCHAR2,
      v_cName        IN VARCHAR2,
      v_defaultValue IN VARCHAR2 )
   IS
      v_is_null VARCHAR2(1);
   BEGIN

      SELECT nullable INTO v_is_null 
      FROM USER_TAB_COLUMNS 
      WHERE TABLE_NAME = v_tbName 
      AND COLUMN_NAME  = v_cName;

      IF  v_is_null   = 'Y' THEN
          EXECUTE IMMEDIATE ('ALTER TABLE ' || v_tbName 
               || ' MODIFY (' || v_cName 
               || ' DEFAULT ' || v_defaultValue 
               || '  NOT NULL )');
      END IF;
   END;

However when I execute my code:

BEGIN
   modify_not_null('TABLE_NAME', 'COLUMN_NAME ' ,'0');
END;
/ 

I am getting a

"ORA-01403: No Data Found"

This exception will be usually thrown if the "SELECT INTO" statement does not return any value, however I will always get a value when I execute this:

Select nullable 
from USER_TAB_COLUMNS 
WHERE table_name = 'TABLE_NAME' 
AND column_name  = 'COLUMN_NAME';

When I execute the code above, I get "N" or "Y" as a result. So I always get a result. I don't know why this exception is thrown

Answer 1

Your call contains a trailing space:

modify_not_null('TABLE_NAME', 'COLUMN_NAME ' ,'0');
                                          ^

So proc throws no data found because 'COLUMN_NAME ' != 'COLUMN_NAME'

Use upper(trim(v_cName)) to prevent typos causing errors. Apply on all parameters.