Home » SQL & PL/SQL » SQL & PL/SQL » No Data Found with insert
icon5.gif  No Data Found with insert [message #137775] Fri, 16 September 2005 13:31 Go to next message
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 Go to previous messageGo to next message
Art Metzer
Messages: 2478
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #139476 is a reply to message #139389] Wed, 28 September 2005 04:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hmm, you could see what statements are actually launched from your client, using something like Quest SQL Monitor. A freeware version can be found at toadsoft's website if I'm not mistaken.

MHE
Re: No Data Found with insert [message #139479 is a reply to message #139389] Wed, 28 September 2005 04:17 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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... Question

Regards,

Gert
Re: No Data Found with insert [message #426478 is a reply to message #137775] Thu, 15 October 2009 13:17 Go to previous messageGo to next message
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'.
Re: No Data Found with insert [message #426482 is a reply to message #426478] Thu, 15 October 2009 15:35 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Nice to know. Thanks.
Previous Topic: join function
Next Topic: UTL_SMTP issue with Canadian French lanaguage
Goto Forum:
  


Current Time: Wed Dec 07 04:37:51 CST 2016

Total time taken to generate the page: 0.07866 seconds