No Data Found with insert [message #137775] |
Fri, 16 September 2005 13:31  |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
Hi everyone,
We have a problem with one of our programs...
It executes a certain number of simple inserts like:
"insert into BLAH (BLAH_ID, BLAH_VAL, BLAH_STATUS) values (123456, 'testing', 10)"
All goes well, but sometimes we get the error "ORA-1403 NO DATA FOUND".
The application accesses an Oracle database, of version 8.05, through a client, also of version 8.05.
Before the program used the Borland Database Engine (Delphi 5) to access the database, but recently we converted to ODAC (Oracle Data Access Components) in Delphi 7. We are planning an upgrade of the database, and we wanted to test the ODAC first. All parameters of the ODAC components are filled in correctly (as been done in numerous other projects).
I understand from the Oracle documentation that this error is generated in case of select statements, not with inserts.
That's why I started to look if there were any triggers with "select into" statements on the specific table and all related tables, but there are none.
Can anyone give some advise on this? Or can anyone tell me if Oracle has a way of showing what generated the error (the exact query, or the database object like trigger, table or whatever)?
Thanks!
|
|
|
Re: No Data Found with insert [message #137781 is a reply to message #137775] |
Fri, 16 September 2005 14:47   |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
I have to admit, this sounds like trigger behavior to me.
Are there any triggers whatever on this table that you've called "BLAH"? What are the results of this query?SELECT owner
, trigger_name
FROM sys.dba_triggers
WHERE table_owner = USER
AND table_name = 'BLAH'
AND triggering_event LIKE '%INSERT%'
AND status = 'ENABLED'
/
|
|
|
Re: No Data Found with insert [message #137830 is a reply to message #137775] |
Sat, 17 September 2005 04:53   |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
There are no triggers on this table. I made sure of that...
Is there any way to see what object causes the error? E.g. by tracing or logging? I admit that I do not know how I could get this type of information...
Regards,
Gert
|
|
|
Re: No Data Found with insert [message #138002 is a reply to message #137830] |
Mon, 19 September 2005 06:15   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
I can think of using alter session set sql_trace=true;
before running the program. Then do tkprof to see what sql's were executed.
However I think there can be other better alternatives as well.
|
|
|
Re: No Data Found with insert [message #138193 is a reply to message #138002] |
Tue, 20 September 2005 12:34   |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
How does SQL-trace log the data? Does it log information on the object that generated the error? Or does it only log the executed SQLs?
I know what SQL our program executes, as we log any errors to a logfile. We include the erroneous SQL statement in this logging.
Point is that I'm not sure if the error is generated by this specific SQL statement, or by another SQL statement that is executed (Oracle trigger...).
I'm looking at AUDIT right now, but I'm not familiar with it and I don't know if it suits my needs...
Regards,
Gert
|
|
|
Re: No Data Found with insert [message #138354 is a reply to message #137775] |
Wed, 21 September 2005 09:04   |
askanier
Messages: 5 Registered: September 2005 Location: Germany
|
Junior Member |
|
|
Do you use stored procedures? Try this to check if you have
code including "SELECT INTO" in your user objects:
select type,name,text from user_source
where upper(text) like '%SELECT%'
and upper(text) like '%INTO%';
|
|
|
Re: No Data Found with insert [message #139304 is a reply to message #137775] |
Tue, 27 September 2005 06:01   |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
Still not wiser...
I activated auditing with the following statement:
audit select table, insert table, update table, delete table by userX by access whenever not successful;
I tested and all errors seemed to be logged correctly to the sys.AUD$ table. So then started the waiting for the ORA-01403 error.
This morning we got this error, and I checked the audit tables and views, but there is not a single trace of returncode 1403.
Can anyone explain this?
Regards,
Gert
|
|
|
Re: No Data Found with insert [message #139326 is a reply to message #139304] |
Tue, 27 September 2005 08:30   |
askanier
Messages: 5 Registered: September 2005 Location: Germany
|
Junior Member |
|
|
Hi,
I tested this behaviour and it seems that ora-01403 is a
typical PL/SQL error. The SQL statement behind works well,
but delivers no rows and so it will not generate an error.
The PL/SQL statement "SELECT <x> INTO" in fact really needs
one value - so it generates an error.
Otherwise typical referential constraint violations on
tables generate entries in sys.aud$ (2291).
I suppose auditing will not help in this case.
Unforetunately you still need a solution for your problem.
Did you test the upper statement to test on occurances
of "SELECT INTO"? Do you have the chance to encapsulate
any occurance of "SELECT INTO" by a "begin-exception-end"-block?
That means: can you modify the source code by yourself?
|
|
|
Re: No Data Found with insert [message #139389 is a reply to message #137775] |
Tue, 27 September 2005 15:30   |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
I have no select into statements whatsoever.
The database is rather simple, with about 15 tables. Only one table has a trigger, but this trigger doesn't include a select into statement. And the table isn't linked to the problematic program in any way.
All code is implemented in Delphi. We use no code on the database itself (no stored procedures or functions).
I'll deactivate the auditing, as apparently it won't give me any answers for this problem.
Any other suggestions are still welcome...
Regards,
Gert
|
|
|
|
Re: No Data Found with insert [message #139479 is a reply to message #139389] |
Wed, 28 September 2005 04:17   |
askanier
Messages: 5 Registered: September 2005 Location: Germany
|
Junior Member |
|
|
Hi,
you get 1403 in the following cases:
1. You executed a SELECT INTO statement and no rows were returned.
2. You referenced an uninitialized row in a table.
3. You read past the end of file with the UTL_FILE package.
If we can forget point 1 then I would ask if you use
UTL_FILE package to read files form the file system into the
db - probably not.
Point 2 I suppose that this meens if you use a plsql table
where you use statements like 'column(i)' and this row is
undefined because i is too high.
I can't remember if it is possible to force this error simply
by using a tricky SQL statement without using PL/SQL.
For example: You use a DML statement with a subquery:
...where a = ( select b from tb where c = d )
In this case it could be that the subselect delivers more than
one row. This will generate error ORA-01427.
Unforetunately this statement not generates an 1403 when the
subselect delivers no rows.
Finally I would say you have to add some debug information to
your application to identify the compromising statement.
Sorry, no more ideas.
|
|
|
Re: No Data Found with insert [message #139497 is a reply to message #139479] |
Wed, 28 September 2005 05:49   |
askanier
Messages: 5 Registered: September 2005 Location: Germany
|
Junior Member |
|
|
Hi,
yes good idea! I've tested it. Take a look on the following output:
----------------------------------
Timestamp: 12:44:00.014
update tb_a set a=( select fk from tb_b where b=7 )
where pk=4
----------------------------------
Timestamp: 12:44:26.936
select * from tb_a where a=( select fk from tb_b where b=6)
Oracle error occurred: 1427 (ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile)
Could be that this works. Good luck!
|
|
|
Re: No Data Found with insert [message #139526 is a reply to message #139497] |
Wed, 28 September 2005 08:31   |
Gert Willemsens
Messages: 15 Registered: September 2004 Location: Belgium
|
Junior Member |
|
|
Our application is full of debug information. We use a special debug object that queues and sends debug information over a named pipe. This way, we can track program execution to the line level. This is a proven technology, but helps us no further as the ORA-01403 is comming from within the execution of the SQL-statement (which is done through the Oracle Call Interface when using ODAC).
So, as an example, our code looks like this:
begin
adddebugtext('info','starting insert');
try
OraQuery1.SQL := 'insert into BLAH (BLAH_ID, BLAH_VAL, BLAH_STATUS) values (123456, 'testing', 10)';
OraQuery1.Execute;
except
on E: Exception do
begin
//This is where the error is catched
adddebugtext('error','Error inserting into BLAH'+E.Message);
exit;
end;
end;
adddebugtext('info','insert completed successfuly');
end;
This code works fine, and yields a lot of debug information records. Whenever we generate an error on purpose (for example a ORA-02291 Parent key not found), this is logged as intended.
But apparently the ORA-1403 error is generated on another level (PL/SQL, not Oracle DB). I can't get it... 
Regards,
Gert
|
|
|
Re: No Data Found with insert [message #426478 is a reply to message #137775] |
Thu, 15 October 2009 13:17   |
cheneyr
Messages: 1 Registered: October 2009 Location: Lansing MI
|
Junior Member |
|
|
I know this is old but I just had the problem and solved it in my case.
I was getting 1403 when inserting from my form, but could plug the values into my statement in SQL+ and it worked fine. Turns out that I had 2 variables in the insert statement switched in order, so they wern't being inserted into the proper fields in the table. Since these were foreign keys, the values were not found in the parent tables... hence 'data not found'.
|
|
|
|