oracle commit kills

Question

hi

I got an oracle db 10g, here a table as an example

create table Dienstplan
(
Montag Number(2),
Dienstag Number(2),
Mittwoch Number (2),
Donnerstag Number (2),
Freitag Number (2),
Samstag Number (2),
Sonntag Number (2),
gueltigAb DATE default SYSDATE NOT NULL,
PersonalNr Number(10) references Mitarbeiter(PersonalNr) INITIALLY DEFERRED DEFERRABLE,
PRIMARY KEY (PersonalNr, gueltigAb),
check (Montag <= 24),
check (Dienstag <= 24),
check (Mittwoch <= 24),
check (Donnerstag <= 24),
check (Freitag <= 24),
check (Samstag <= 24),
check (Sonntag <= 24)
);
/

now the problem is that whenever I insert a row (not exclusive to this table) that contains a foreign key (the reference is valid so its not that) it inserts dutifully and as soon as I commit the whole mess it disappears again.

INSERT INTO Dienstplan (Montag, Dienstag, Mittwoch, Donnerstag, Freitag, Samstag,   Sonntag, PersonalNr) values ('1', '2','3','4','5','6','7','1');

the real kicker is that the manual insert in sqldeveloper (insert row - dialog & commit) works like a charm, which does not help me at all...

any help is appreciated cheer hoax

Answer 1

My guess is that your UI is not handling exceptions properly and the insert is failing for some reason but you aren't being told.

Answer 2

Make sure you are not hiding any exceptions from either the insert or the commit. (Since the FK constraint is deferred, you would get an exception from the commit on an FK violation.)

As a more general debugging suggestion, I would say use SQL Trace so you can see exactly what's happening.

Answer 3

Another idea:

If you think the IDE is masking/hiding/not showing the proper exception, why don't you add the insert and the commit into a Begin... End (anonymous PL/SQL block) and see what pops? Even more, a dbms_output in the exception block will do the trick too.

But certainly the SQL Trace mentioned above will show you what's going on behind the scenes, and as far as I understand the SQL trace will work only for PL/SQL statements, if this is correct you'll have to put the insert and commit in a PL/SQL block.