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