dotConnect for Oracle does not raise ORA-01403

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
matthiasramp
Posts: 12
Joined: Fri 22 Jan 2010 15:39

dotConnect for Oracle does not raise ORA-01403

Post by matthiasramp » Fri 22 Jan 2010 15:58

Hi there,

I have a strange behaviour with dotConnect for Oracle. (v5.35)
I have a trigger with is executed bafore an update on a column occurs. The trigger fetches a text from another table. The trigger uses the new value of the column of table 1 to lookup the text in table 2. If I pass an invalid value (e.g. 0) I will get an "ORA-01403: no data found" when I use a tool like TOAD.
If I use dotConnect (ExecuteNonQuery), I get no error. Nothing happens which indicates an error. No rows were altered. Does dotConnect lose the error?
Does anyone know what the problem is :?:


Here is a script to create the tables, the trigger and insert some basic data:

Code: Select all

ALTER TABLE LOOKUP
 DROP PRIMARY KEY CASCADE;
DROP TABLE LOOKUP CASCADE CONSTRAINTS;

CREATE TABLE LOOKUP
(
  CODE  NUMBER(10)                              NOT NULL,
  TEXT  VARCHAR2(50 CHAR)
);


ALTER TABLE DATA
 DROP PRIMARY KEY CASCADE;
DROP TABLE DATA CASCADE CONSTRAINTS;

CREATE TABLE DATA
(
  ID      NUMBER(10)                            NOT NULL,
  CODE    NUMBER(10),
  LOOKUP  VARCHAR2(100 CHAR)
);


SET DEFINE OFF;
Insert into DATA
   (ID, CODE, LOOKUP)
 Values
   (1, 2, 'only a testonly a test');
Insert into LOOKUP
   (CODE, TEXT)
 Values
   (1, 'CODE1 - some text');
Insert into LOOKUP
   (CODE, TEXT)
 Values
   (2, 'CODE 2 - only a test');
COMMIT;


CREATE OR REPLACE TRIGGER lookup_text
BEFORE INSERT OR UPDATE
OF CODE
ON DATA 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar VARCHAR2(20);
BEGIN

   SELECT l.text INTO tmpVar 
   FROM lookup l
   WHERE l.code = :NEW.code;

   :NEW.lookup := tmpVar || tmpVar;

--This raises ORA-01403, but it does not raise an error when using devart
--ORA-01403: Keine Daten gefunden
--ORA-06512: in "MR.LOOKUP_TEXT", Zeile 14
--ORA-04088: Fehler bei der Ausführung von Trigger 'MR.LOOKUP_TEXT'
END lookup_text;
/

This is my VB.net code: Result is 0 if I pass the invalid value d.code=0. Although an ORA-01403 should the raised.

Code: Select all

        Try
            MyOracleConnection.ConnectionString = "user id=MR; password=MR"
            MyOracleConnection.Open()

            Dim cmd As New OracleCommand("UPDATE mr.data d set d.code = 0 WHERE d.ID = 1", MyOracleConnection)
            Dim result As Integer = cmd.ExecuteNonQuery()

            TextBox1.Text = result.ToString
        Catch ex As Exception
            TextBox1.Text = ex.Message
        Finally
        End Try

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 25 Jan 2010 13:28

ORA-01403 is an informational message and not actually an error, hence it generates no exception.

We will add the functionality to catch ORA-01403 in one of the nearest builds. This message will call an OracleConnection.InfoMessage event with OracleInfoMessageEventArgs arguments containing the code, i.e. 1403.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 29 Jan 2010 09:27

We investigated the situation. The ORA-01403 message is purely informational, it does not correspond to any error or problem, thus it would be redundant to generate an event on ORA-01403. Please use the ExecuteNonQuery method return, which is the number of rows affected by the command. In the sample, the return should be 0 as no data were updated.

matthiasramp
Posts: 12
Joined: Fri 22 Jan 2010 15:39

Post by matthiasramp » Fri 29 Jan 2010 15:18

Stanislav, you are right, return value is 0. But it gives you no clue why it was 0.

Either
1) there were no rows found which matched my filters OR
2) one or multiple rows have been found, but they could not be altered because of the trigger which found no data.

In the given case, the trigger found no data, and altered no rows. If I did not run the same statement with an SQL-Tool, I would have never seen the ORA-01403 - and would still ask me, why no rows have been found.

Of course, it is my fault, if I enter erroneous data in my update statement, but it would love to find out, what my error is.
If you pass the informal message to the application, that would be of great help.
Thanks,
Matthias

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 29 Jan 2010 16:35

We will investigate the possibility of adding such functionality and inform you about the results.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

+1 This functionality is really needed, please hurry up!!!

Post by maxcpr » Tue 29 Jun 2010 14:04

Indeed all other providers handle this situation by throwing exception. You may handle this as you wish, but please handle!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 01 Jul 2010 10:31

We do not plan to add such functionality, as the ORA-01403 message may be returned in regular situations, like at the end of data fetch. Generating an event each time ORA-01403 is received will cause a large amount of redundant events.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Hi

Post by maxcpr » Thu 15 Jul 2010 08:01

Hi!

Guys we are still really needed in this. OK i could understand that ORA-01403 message was raised by inderect signs as you described but still i needed in determine in which line it was raised.

And you wrote that - Generating an event each time ORA-01403 is received will cause a large amount of redundant events.

Nevertheless you would generate this event only if there are some subscribers on one.

Thanks!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 15 Jul 2010 14:08

We are investigating the possibility of handling this informational message. Please note that if this functionality is implemented you will need to filter redundant messages on your own.

Also, it would be helpful if you specify the exact scenario in which you need to handle the ORA-01403 message; if possible, please send us the PL/SQL block in which the message fires and the scripts needed to create the database objects used.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Example

Post by maxcpr » Thu 22 Jul 2010 08:19

Hi Stanislav
There is example how this exception could be rised

create or replace function test
return number
as nn number;
begin
select 1 into nn from dual where 0 = 1;
return nn;
end;

Please notice:
Our data base developers consider ORA-01403 message as explicit error thus i should handle one each time when it arised.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 22 Jul 2010 16:18

I meant, could you please describe the situation in which handling ORA-01403 is necessary and how exactly you are using this message? Such information would help us greatly.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Fri 23 Jul 2010 06:54

Hi Stanislav.
The situation is quite simply: i invoke some procedure in my code to store some data, when the procedure makes some data validation and other neccessary things before update the record while it is processing it could be some data inconsistent or other bugs in data, specifically ORA-01403 no data found.

For examle :

Code: Select all


Table [CLIENT]

ID      LABEL
------------------------------------
0        CLIENT1
1        CLIENT2


Table [CLIENTCODE]

ID  CLIENT    LABEL
-----------------------------------
0        0         CLIENTCODE1

Please notice: There is no FK constraints on CLIENCODE table for CLIENT column.
Our db developers claim that FK constraints, in some cases, could slow down system significantly. 


procedure InsClientCode(int CLIENT, varchar(10) CLIENTCODELABEL)
begin

var ccl = CLIENTCODELABEL;

if (ccl is NULL)
--Make up CLIENTCODE LABEL by CLIENT LABEL
 select ccl := LABEL from CLIENT where ID = CLIENT
end if;

........
........

end

--------------------------------------------------
>>exec InsClientCode(3, 'CLIENTCODE2')

error : ORA-01403 
---------------------------------------------------

It's just the simplest case that describes the situation.
Generally speaking all the time when a procedure doesn't handle no-data-found message by itself, a client should be able to recognize this exception (message).

By the way, on what basis did you say that ORA-01403 is purely inforamtional. Please provide me with a related link on an ORACLE document, as for as our db developers don't agree with that claim.

Thanks,
Putilov Maxim

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 23 Jul 2010 17:28

Sorry for 'purely informational'. It was meant that ORA-01403 can appear in common situations, like at the fetch end, i.e. when no actual problem occurred. Hence it would be error-prone to generate an exception whenever ORA-01403 occurs.

However, we will consider the possibility of handling ORA-01403 with an event.

Thank you for your assistance, we will inform you when our investigation is completed.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Mon 26 Jul 2010 06:23

Thanks.

Looking forward to hear you.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Hi there!

Post by maxcpr » Tue 17 Aug 2010 12:52

Hi there! How things are going? We are still waiting for solution.

Post Reply