messagetable doesn't work with "not null" constraints

[remq]

Hello,
can anybody help me with messagetable problem in TOracleDataset?
I use DOA version 3.4.6.4 with Oracle 10.
In my Delphi5 application I use message table to translate Oracle messages to user.
Everything works fine if foreign key or unique index constraints are used, the messages from messagetable are shown correctly. But it doesn't work with "not null" constraints. My database has named null constraints, as required by TOracleDataSet.
I tried setting TField.Required, TOracleDataSet.EnforceConstraints and other properties, but no positive result.

Is it the matter of old DOA version, or anything else? What should I do to handle messages from not null constraints whereas other constraints work fine?

Please help,
Greetings
remg

 

[Marco Kalter]

Can you send me the DDL for this table, including the not null constraints?

 

[remq]

Originally posted by Marco Kalter:
Can you send me the DDL for this table, including the not null constraints?

Hello,
here is an example of DDL script for one of the tables:

Create table privilleges (
id Number Constraint privillege_id_nn NOT NULL ,
code Varchar2(20) Constraint privillege_code_nn NOT NULL ,
name Varchar2(50) Constraint privillege_name_nn NOT NULL ,
description Varchar2(50),
Constraint pk_privilleges primary key (id)
);
Create UNIQUE Index uq_privillegecode ON privilleges (code);

It works fine for unique index constraint, (and for foreign key constraints which are not included here).
But doesn't work for not null constraints(privillege_name_nn,privillege_code_nn).
All constraints are in the messagetable on Oracle with flags Actions=*,Parent_child=*.

Greetings,
remq

 

[Marco Kalter]

I tested this, and when a record with a NULL value for the NAME column is inserted, I get:

ORA-01400: cannot insert NULL into ("SCOTT"."PRIVILLEGES"."NAME")

As you can see, the constraint name is not returned. The solution is to make the column optional, and to explicitly create a check constraint. For example:

alter table PRIVILLEGES
add constraint PRIVILLEGE_NAME_NN
check ("NAME" is not null);

Now the column will seem optional to Oracle, and the appropriate check constraint violation will be raised when a NULL is inserted. This will lead to a corresponding message.

 

[remq]

Originally posted by Marco Kalter:
I tested this, and when a record with a NULL value for the NAME column is inserted, I get:

ORA-01400: cannot insert NULL into ("SCOTT"."PRIVILLEGES"."NAME")

As you can see, the constraint name is not returned. The solution is to make the column optional, and to explicitly create a check constraint. For example:

alter table PRIVILLEGES
add constraint PRIVILLEGE_NAME_NN
check ("NAME" is not null);

Now the column will seem optional to Oracle, and the appropriate check constraint violation will be raised when a NULL is inserted. This will lead to a corresponding message.

Thanks a lot for help!!