APEX

Hi,

I'm always having troubles using tabular forms, so I avoid to use them. But in some cases I just have to... Here I have a case with a Master-Detail relationship between two tables. On my page the master is in a Form Region and the details are shown in a Tabular Form. Its a table with types (the master) and a table with type values (the details).

The code for createing the two tables is like:

/*The Master table TYP*/
CREATE TABLE TYP
(ID NUMBER(15) NOT NULL,
 NAME VARCHAR2(25) NOT NULL,
 DESCR VARCHAR2(100)
);

/*TYP's PK*/
CREATE UNIQUE INDEX TYP_PK ON TYP
(ID);

ALTER TABLE TYP ADD
( CONSTRAINT TYP_PK
  PRIMARY KEY
  (ID)
  USING INDEX TYP_PK);

/*I've created a sequence for createing a unique number for the PK. 
the PK is filled by a trigger*/
CREATE SEQUENCE TYP_SEQ
START WITH 1
MAXVALUE 999999999999999;

CREATE OR REPLACE TRIGGER TYP_BIR
BEFORE INSERT ON TYP REFERENCING NEW AS New OLD as Old
FOR EACH ROW
declare
 v_id number :=0;
begin
 select TYP_SEQ.nextval into v_id form dual;
 :New.id := v_id;
end TYP_BIR;

/* The detail table with type values (TYP_VAL)*/
CREATE TABLE TYP_VAL
( ID NUMBER(15) NOT NULL,
  TYP_ID NUMBER(15) NOT NULL,
  NUM NUMBER(15) NOT NULL,
  VAL VARCHAR2(25) 
);

/*The value's PK*/
CREATE UNIQUE INDEX TYP_VAL_PK ON TYP_VAL
(ID);

ALTER TABLE TYP_VAL ADD
( CONSTRAINT TYP_VAL_PK
  PRIMARY KEY
  (ID)
  USING INDEX TYP_VAL_PK);

/* an unique key, combination of TYP_ID and NUM must be unique*/
CREATE UNIQUE INDEX TYP_VAL_UK_NUM ON TYP_VAL
(TYP_ID, NUM);

ALTER TABLE TYP_VAL ADD
( CONSTRAINT TYP_VAL_UK_NUM
  UNIQUE(TYP_ID, NUM)
  USING INDEX TYP_VAL_UK_NUM);

/*The FK reference between TYP  and TYP_VAL*/
ALTER TABLE TYP_VAL ADD
( CONSTRAINT TYP_TYP_VAL_FK
  FOREIGN KEY (TYP_ID)
  REFERENCES TYP(ID));

/* For filling the ID of the value I also created a sequence and a trigger*/
CREATE SEQUENCE TYP_VAL_SEQ
START WITH 1
MAXVALUE 999999999999999;

CREATE OR REPLACE TRIGGER TYP_VAL_BIR
BEFOR INSERT ON TYP_VAL REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
declare
 v_id number :=0;
begin
 select TYP_VAL_SEQ.nextval into v_id from dual;
 :New.id = v_id;
end TYP_VAL_BIR;

/* trigger before update: only the field 'VAL' may be updated*/
CREATE OR REPLACE TRIGGER TYP_VAL_BUR
BEFORE UPDATE ON TYP_VAL REFERENCING NEW AS New OLD AS Old
for each row
declare
 v_errnum number :=0;
 v_errmsg varchar(4000) := null;
 e_err exception;
begin
	if :New.TYP_ID <> :Old.TYP_ID
		or :New.NUM <> Old.NUM 
	then
		raise e_err;
	end if;
	
	exception
		when e_err then
			select num, msg into v_errnum, v_errmsg
			from MY_ERROR_TABLE
			where cod = 'e_err';
			raise_application_error(v_errnum, v_errmsg);
		when others then
			raise;
end TYP_VAL_BUR;

So in APEX I created a form with a report on the TYP table. On the same page I've added a Tabular Form on the TYP_VAL table. In the where clause of the reportquery, I added

where TYP_ID = :P1300_ID

The item P1300_ID holds the ID of the selected type (table TYP).

In the tabular form, the ID is a display only field, which does not save state, the TYP_ID field is a display only field, which does save state.
The default value of the field TYP_ID, is the value of item P1300_ID. the tabular form is only visible when P1300_ID contains a value.

When I add a new row, and save it, I get the error: Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01403: No Data Found., insert into TYP_VAL
When I update an existing row (I've added one row directly in the table), I get the same error.
When I add a seccond row, and save it, I get the error: Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-00001: Unique constraint violated (TYP_VAL_UK_NUM)., insert into "TYP_VAL" ( "ID", "TYP_ID", "NUM", "VAL") values ( :b1, :b2, :b3, :b4)
But I'm sure the combination of TYP_ID and NUM is unique!
When I delete a row, I get no errors, but the row isn't deleted afterwards....

I've created a simmular tabular form in a simmular situation, and that one works fine. So it seems I can't reproduce the problem, but I can't fix it eighter. I have deleted the tabular form and the processes comming along with it, and than added it again, but it didn't help...
Who can help me out??

I'm using APEX 4.0.2

Edited by: Mir on 20-jun-2011 10:54
( replaced :New.id := v_tmp; with :New.id := v_id; )