TOraSession.MessageTable questions

[rguillen]

Hi Marco,

I'm working with a system that implement the errors message with TOracleSession.MessageTable. I create the following schemas:

Table messages:

CREATE TABLE BARD.DD_MESSAGES
LANGUAJE VARCHAR2 2 NOT NULL,
CONSTRAINT_NAME VARCHAR2 30 NOT NULL,
ACTIONS VARCHAR2 3 NOT NULL,
PARENT_CHILD VARCHAR2 1 NOT NULL,
ERROR_MESSAGE VARCHAR2 2000,
CONSTRAINT pk_ddmessages PRIMARY KEY LANGUAJE, CONSTRAINT_NAME, ACTIONS, PARENT_CHILD USING INDEX
TABLESPACE INDEXU_TS STORAGE INITIAL 16K NEXT 16K PCTINCREASE 0
TABLESPACE UNCHANGE_TS STORAGE INITIAL 16K NEXT 16K PCTINCREASE 0;

Views for languages:
CREATE OR REPLACE VIEW "BARD"."EV_MESSAGES" "CONSTRAINT_NAME", "ACTIONS",
"PARENT_CHILD", "ERROR_MESSAGE" AS
SELECT CONSTRAINT_NAME, ACTIONS, PARENT_CHILD, ERROR_MESSAGE FROM DD_MESSAGES
WHERE LANGUAJE = 'EN' WITH READ ONLY;

CREATE OR REPLACE VIEW "BARD"."SV_MESSAGES" "CONSTRAINT_NAME", "ACTIONS",
"PARENT_CHILD", "ERROR_MESSAGE" AS
SELECT CONSTRAINT_NAME, ACTIONS, PARENT_CHILD, ERROR_MESSAGE FROM DD_MESSAGES
WHERE LANGUAJE = 'SP' WITH READ ONLY;

Table users with view assigned:
CREATE TABLE BARD.DD_USUARIOS
LANGUAJE CHAR 2 DEFAULT 'SP',
USUARIO VARCHAR2 30
CONSTRAINT cnn_usuario_ddusuarios NOT NULL,
IDPASSWORD VARCHAR2 30,
NOMBRE VARCHAR2 40,
EMAIL VARCHAR2 40,
PROFILE_NAME VARCHAR2 30
CONSTRAINT c_nn_profilename_ddusuarios NOT NULL,
SUBPASSWORD VARCHAR2 30 DEFAULT 'INVALID',
TIPO NUMBER 1 DEFAULT 0
CONSTRAINT c_ch_tipo_ddusuarios CHECK TIPO >= 0 AND TIPO

 

[Marco Kalter]

Did you enable TOracleDataSet.OracleDictionary.EnforceConstraints?

 

[rguillen]

I sorry forgot said that I assing TOracleDataSet.OracleDictionary.EnforceConstraints:= True and TOracleDataSet.OracleDictionary.UseMessageTable:= True;

Richard.

 

[Marco Kalter]

Can you set TOracleSession.Debug to True to verify if the correct queries are fired against the message table?

 

[rguillen]

Ok,
But where I can see the query?

 

[rguillen]

Marco,

I set TOracleDataSet.Debug to True and showed following:

begin
if not :NUMERO IS NOT NULL then
:error:= 2290;
else
:error:= 0;
end if;
end;

:NUMERO:= 17414;
:ERROR:= Null

After

select 'x' fom BARD.B_EMPLEADOS where NUMERO = :NUMERO

:NUMERO:= 17414

and after

ORA-00001:unique constraint(BARD.PK_EMPLEADOS) violated.

How I need interpret this sentences?

Richard.

 

[Marco Kalter]

My apologies, you can't see the MessageTable query by using the Debug property. You will have to use the Oracle Monitor. Can you try this?

 

[rguillen]

Marco,

I again. I turn on the oracle monitor and I get the followed sql:

begin
if not :NUMERO IS NOT NULL then
:error := 2290;
else
:error := 0;
end if;
end;

NUMERO = 17414
ERROR = Null

select 'x' from BARD.B_EMPLEADOS
where NUMERO = :NUMERO

Variable NUMERO = 17414

select actions, parent_child, error_message from BARD.SV_MESSAGES
where constraint_name = 'PK_EMPLEADOS'
order by actions

When I run it in SQL Plus, it return une record:
|IU|P|NUMERO DE EMPLEADO DUPLICADO|

As I can see all is correct, but I don't know where is the problem.

Richard.

 

[Marco Kalter]

I think the problem is fixed if you update this message and set the PARENT_CHILD column to '*'.

Parent_Child - Indicates if the message is to be displayed when the constraint is violated at the parent ('P') or child ('C') side. This column is only useful for foreign key constraints, use a '*' for all other constraints.

 

[rguillen]

Hi Marco,

Great, it now work ok.

Thank you for your valuable help!...

Richard.