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

Why is data selected into `v_is_null` but `IF` condition is on `l_nullable` ? Could be that `l_nullable` is local variable declared, but initialized. — Vijayakumar Udupa, Jan 20 '17 at 11:20
I am very sorry, it was a typo again in my post...That was unfortunately not the mistake, but thx very much for the hint — NoName123, Jan 20 '17 at 11:28
Are you executing query and PL SQL block from different users? In case user id is different, data in USER_TAB_COLUMNS change. Best way to debug is to put a dbms_output after each line in your code to understand origin of exception. — Vijayakumar Udupa, Jan 20 '17 at 11:36
You have no other option left, but to add debug statements and also execute show error after executing pl sql block. To debug, you can also add count(*) from table with given criteria. Most likely it is some typo or old version of procedure still being picked up. — Vijayakumar Udupa, Jan 20 '17 at 11:50
[Invoker rights vs definer rights](http://dba.stackexchange.com/q/59887/6260) ? — user272735, Jan 20 '17 at 13:04
You have an extra space in your subprogram call parameter: `'COLUMN_NAME '` — user272735, Jan 20 '17 at 14:13

score 4 · Accepted Answer · edited Jan 20 '17 at 14:43

4

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.

edited Jan 20 '17 at 14:43

APC

144,005
19
170
281

answered Jan 20 '17 at 13:34

ASz

119
3

score 1 · Answer 2 · answered Jan 20 '17 at 10:26

1

You are passing v_defaultValue param to column name.

Change procedure to

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

answered Jan 20 '17 at 10:26

hkutluay

6,794
2
33
53

J. Chomel · Answer 3 · 2017-01-20T14:42:02.150

Before your SELECT .... INTO, you have to make sure that there is something to select. Because depending on what user you are, and what parameters you give, there may be no data in your table.

A simple way would be to have a COUNT at the beginning before the SELECT:

 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);
      v_count number;
   BEGIN
      -- added select count
      SELECT count(1) INTO v_count FROM USER_TAB_COLUMNS WHERE TABLE_NAME = trim(v_tbName) AND COLUMN_NAME  = trim(v_cName);
      -- added if v_count=1
      if v_count = 1 then
        SELECT nullable INTO v_is_null FROM USER_TAB_COLUMNS WHERE TABLE_NAME = trim(v_tbName) AND COLUMN_NAME  = trim(v_cName);

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

Share and enjoy

@APC, yes right, my mistake. I tested, but wrong ':/ ... I reverted to my first answer. — J. Chomel, Jan 20 '17 at 14:42

TheName · Answer 4 · 2017-01-20T14:46:47.577

-1

stay classy :)

create or replace procedure modify_not_null(v_tbName       in varchar2,
                                            v_cName        in varchar2,
                                            v_defaultValue in varchar2) is
  cursor c_tbl(cp_tbname in varchar2, 
               cp_cname in varchar2) is
    select nullable
      from user_tab_columns
     where table_name  = upper(cp_tbname)
       and column_name = upper(cp_cname);

  l_tbl c_tbl%rowtype;
begin

  open c_tbl(cp_tbname => v_tbName,
             cp_cname => v_cName);
  fetch c_tbl into l_tbl;
  close c_tbl;

  if l_tbl.nullable = 'Y' then
    execute immediate 'alter table ' || v_tbName || ' modify (' || v_cName ||
                      ' default ' || v_defaultValue  || '  not null )';
  end if;
exception
  when others then
    raise_application_error(-20000, dbms_utility.format_error_stack);
end modify_not_null;

edited Jan 20 '17 at 14:46

answered Jan 20 '17 at 14:44

TheName

697
1
7
18

It looks like the OP's error is due to a trailing space in an argument, and all your extra code doesn't actually fix that. – APC Jan 20 '17 at 14:46
How does this fix the OP's problem? If the original code raises an ORA-01403, this code will suffer from the exact same problem (apart from hiding the real place where the exception occurred). – Frank Schmitt Jan 20 '17 at 14:46
whats is the OP problem? it will not raise any errors if the table is not found. – TheName Jan 20 '17 at 14:50
It will not suffer from ORA-01403, try it before saying unwise things. – TheName Jan 20 '17 at 14:58
1

Suppressing the error message is not the same as fixing the problem. – APC Jan 21 '17 at 07:32

ORA-01403: No Data found WHY?

4 Answers4