ORA-01403: no data found, how do you solve this?

Hi all,

I'm trying do a 'INSERT' into a table and I received this message...

ORA-01403: no data found

What does it mean, and how do you solve it? Thanks.

I got this from Oracle Book.

I don't know if this will help you.

ORA-01403 no data found

Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

Action: Terminate processing for the SELECT statement.

Thanks. I also checked the documentation and yield exact the same content as what you got here, but it still doesn't solve my problem.

Can you post your insert statement with Table Defination?

Actually, the problem laid on the trigger that associate with the table, the part starting with

select nvl(keypart1_use,'nothing').....
if .......
If I comment out that section there is no problem, just that primary key platformid is not generated. So I'm not too sure if there is something wrong with it.


I was trying to do insert as:

INSERT INTO Platforms (Platform, DefAssignedToType, KeyPart1_Use, KeyPart2_Use, KeyPart3_Use, DistributedSystem, AllowNoCharge, SupportFac, VendorID)
VALUES ('Test Platform', 'n/a','User ID','Password',null,0, 0,0,560);

**************************

The trigger codes are:
create or replace trigger tri_platforms
before insert on platforms
for each row
declare
stoo_selcnt integer;
stoo_error integer;
stoo_rowcnt integer := 1;
stoo_crowcnt integer := 0;
stoo_fetchstatus integer := 0;
stoo_errmsg varchar2(255);
stoo_sqlstatus integer;
num_of_rows number(10,0);
noruntrg number(1);
v_next_id integer;
v_keypart1_use varchar(16);
v_keypart1indic integer;
v_keypart2_use varchar(16);
v_keypart2indic integer;
v_keypart3_use varchar(16);
v_keypart3indic integer;

begin
num_of_rows := stoo_rowcnt ;

if num_of_rows = 0 then
return ;
end if ;


begin
noruntrg := checkruntrigger ;
if (noruntrg = 1) then
begin
dbms_output.put_line('trigger is bypassed') ;
return ;
end;
end if ;
end ;



select count(*) into num_of_rows
from mdsvendors v
where :new.vendorid=v.vendorid;

if num_of_rows=0 then
begin
RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
ROLLBACK ;
raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
return;
end;
end if;


select next_id into v_next_id
from next_id
where table_name='Platforms';

:new.platformid:=v_next_id+1;

update next_id
set next_id=next_id+1
where table_name='Platforms';

select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
into v_keypart1_use,v_keypart2_use, v_keypart3_use
from platforms
where platform=:new.platform;
dbms_output.put_line('The values are'||v_keypart1_use||','||v_keypart2_use||','||v_keypart3_use);
/ if (v_keypart1_use = 'nothing') then
v_keypart1indic := 0;
else
v_keypart1indic := 1;
end if;

if (v_keypart2_use = 'nothing') then
v_keypart2indic := 0;
else
v_keypart2indic := 1;
end if;

if (v_keypart3_use = 'nothing') then
v_keypart3indic := 0;
else
v_keypart3indic := 1;
end if;

insert into platformmapkeys( platformid, keypart1indic, keypart2indic, keypart3indic )
select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
from platforms
where platform=:new.platform;


end tri_platforms;

The error means:

you have a trigger on the table and the trigger has a select ... into
and the select into is finding no data.

Just got it from someone.

Oh my...

First off, the NO_DATA_FOUND error can only come from a SELECT...INTO that returns no data. Therefore, one of the SELECT...INTOs that you have is not returning any data, hence your error.

Now, for the laundry list of issues with this trigger, in no particular order:

- IF..END IF does not require a BEGIN..END within it

- You twice use num_of_rows to check for the simple existence of rows. In the first usage, you set it equal to another variable, which I don't get, but the second usage is an issue. If you want to check for the simple existence of data, don't waste time *counting the entire table*. Add AND ROWNUM = 1 to the SELECT COUNT(*) to make it run much faster!

- The rollback will not be performed
---RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
---ROLLBACK ;
---raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
This may be intentional.

- This will *not* work in a multi-user environment:

---select next_id into v_next_id
---from next_id
---where table_name='Platforms';

---:new.platformid:=v_next_id+1;

---update next_id
---set next_id=next_id+1
---where table_name='Platforms';

This truly needs to be implemented via a trigger. Trust me.

- This is probably where your NO_DATA_FOUND error is coming from:

---select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
---into v_keypart1_use,v_keypart2_use, v_keypart3_use
---from platforms
---where platform=:new.platform;

The NVL call will not do anything when the statement returns no data. Do this instead:

---BEGIN
------select keypart1_use, keypart2_use, keypart3_use
------into v_keypart1_use,v_keypart2_use, v_keypart3_use
------from platforms
------where platform=:new.platform;
---EXCEPTION
------WHEN NO_DATA_FOUND THEN
---------keypart1_use := 'nothing';
---------keypart2_use := 'nothing';
---------keypart3_use := 'nothing';
------WHEN OTHERS THEN
---------RAISE;
---END;

- Why are you going to the platforms table twice?

---select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
---into v_keypart1_use,v_keypart2_use, v_keypart3_use
---from platforms
---where platform=:new.platform;

---select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
---from platforms
---where platform=:new.platform;

Get everything you need in one statement.

- Another point for the 2 SELECT..INTO statements you have. It looks like you are inserting into the same table you are selecting from. This means that you can have more than one row with the same platform value, no? If that is the case, then your SELECT..INTOs will blow up on these as well. SELECT..INTO *requires* 1 and only 1 row be returned. Any other number of rows will generate an error. You need to re-think what you are doing here.

----------------------------------------------------

You must remember that you are executing *all* this code for every single record you ever insert into this table. Triggers are wonderful things when used responsibly. You need to seriously consider everything you do inside a trigger as it can have a significant impact on the performance of your database.

Finally, I'm honestly not trying to bust on you or anything, but this is a lot of issues for a single trigger. I hope my critiques help more than they hurt.

- Chris

Chris,
Thanks, your critiques is very helpful.
A lot of codes are generated by migration workbench during conversion, I haven't got time to clean it up. Right now, I'm only trying to make it work.
I will definitely spend sometime to read your comment again.

Also would you please help me looking at this trigger, I changed to:

declare
stoo_selcnt integer;
stoo_error integer;
stoo_rowcnt integer := 1;
stoo_crowcnt integer := 0;
stoo_fetchstatus integer := 0;
stoo_errmsg varchar2(255);
stoo_sqlstatus integer;
num_of_rows number(10,0);
noruntrg number(1);
v_next_id integer;
v_keypart1_use varchar(16);
v_keypart1indic integer;
v_keypart2_use varchar(16);
v_keypart2indic integer;
v_keypart3_use varchar(16);
v_keypart3indic integer;

begin
num_of_rows := stoo_rowcnt ;

if num_of_rows = 0 then
return ;
end if ;


begin
noruntrg := checkruntrigger ;
if (noruntrg = 1) then
begin
dbms_output.put_line('trigger is bypassed') ;
return ;
end;
end if ;
end ;



select count(*) into num_of_rows
from mdsvendors v
where :new.vendorid=v.vendorid;

if num_of_rows=0 then
begin
RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
ROLLBACK ;
raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
return;
end;
end if;


select next_id into v_next_id
from next_id
where table_name='Platforms';

:new.platformid:=v_next_id+1;

update next_id
set next_id=next_id+1
where table_name='Platforms';

if (nvl(:new.keypart1_use,'nothing') = 'nothing') then
v_keypart1indic := 0;
else
v_keypart1indic := 1;
end if;

if (nvl(:new.keypart2_use,'nothing') = 'nothing') then
v_keypart2indic := 0;
else
v_keypart2indic := 1;
end if;

if (nvl(:new.keypart3_use,'nothing') = 'nothing') then
v_keypart3indic := 0;
else
v_keypart3indic := 1;
end if;

insert into platformmapkeys( platformid, keypart1indic, keypart2indic, keypart3indic )
select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
from platforms
where platform=:new.platform;


end tri_platforms;


It works when I try to insert into table Platforms, the Platformid is inserted through this trigger. However, the last "insert into platformmapkeys" was not successful, ie. no record is inserted nor any error message.

I suspect that whether the table 'Platforms' table is mutating since a record is just inserted and at the same time it is performing insert into 'platformmapkeys' using 'platformid'? Please advise.

Well, the first thing to realize is that the insert in question is not based on the *current row you are inserting*. It is assuming that *another* row with the same platform column value already exists in the platforms table. If this is the first record being inserted into the platform table with, say, a value of 5 in the platform column, then that last insert will *not* insert anything.

Did that make sense?

- Chris

Now it make sense.
But what should I do if I'd like to insert the newly created
'platformid' into 'platformmapkeys' table?

Try:

insert into platformmapkeys( platformid, keypart1indic, keypart2indic, keypart3indic )
VALUES ( :new.platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic );


- Chris

Chris, yes, I tried it, but I received this:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FITS.ADDNEWPLATFORM", line 34
ORA-20500: Trigger forces a rollback
ORA-06512: at "FITS.TRI_PLATFORMMAPKEYS", line 39
ORA-04088: error during execution of trigger 'FITS.TRI_PLATFORMMAPKEYS'
ORA-06512: at "FITS.TRI_PLATFORMS", line 90
ORA-04088: error during execution of trigger 'FITS.TRI_PLATFORMS'
ORA-06512: at line 1

The problem have been solved.
The error occur because there is another trigger associated with the table 'platformmapkeys' that checks the existance of the platformid value just generated from platform table, since the transaction it's end yet so it will always fail.