Inserting problem

[mbrent]

I am having a problem inserting into my table. I know that all the data is there because I ran the form in debug and I can see all the data on the stack. I keep getting an ORA-00001 error. Could someone please help me figure out what I am doing wrong.

Thank you!

 

[Guest_imported]

I suppose you are running forms application.
I suggest that you press shift-f1 as soon as you get this error message to know the offending field.

 

[mbrent]

Thanks for the suggestion, but when I use the shift-f1 key, it just says "no errors encountered recently". And, yes I am running a forms application.

Thank you!

 

[lewisp]

Try putting DISPLAY_ERROR in an ON-ERROR trigger.

 

[mbrent]

Undoubtably, Forms does not recognize this as an "Error" because even the DISPLAY_ERROR says "no errors encountered recently". When I try to insert it just says FRM-40735: Pre-Update (also tried Post-update and got the same thing) trigger raised unhandled exception ORA-00001.

Thanks!

 

[Guest_imported]

Are you using sequence numbers? If yes, ensure that:
1, the seq.nextval does indeed increment to a number that does not already exist.
For instance:
do a check to determine the highest value of a sequence number, and do your increment from there.
If it turns out that sequence id is causing the problem, then write a simple trigger to increment the sequence number from the next available number.
I can help you with that if that turns out to be the problem.
One thing is for sure, your table has a unique constraint that disallows dups and your form, somehow, is attempting to do just that.

 

[mbrent]

Yes, I am using sequence numbers and nextval. I ran the form in debug and I can see that the sequence number is changing and because I only have two records so far in the table, I can easily tell that they are not duplicates.

However, I do believe the sequence number is my problem and am willing to try anything.

 

[Guest_imported]

ok, try the following steps:

1, make sure a sequence is indeed created by issuing this query from sql*plus:
select sequence_name from user_sequences;

next, if one is not create, create one.
if one is created already then go to sql*plus and determine the maximum number of sequences you have by typing:
select max(the sequence name) from your table;

next on your form, on on-insert trigger enter following code:
declare
next_seq number;
begin
select max(name of your sequence)into next_seq from your table;
next_seq := next_seq + 1;
:block.sequence_name := next_seq; --you can hide this if you want to.
insert_record;
end;

If sequence is the problem, this should solve it.
Please let us know what happened.
sam

 

[mbrent]

Thank you, I will try this first thing in the morning!

 

[freedomUSA]

Have you performed this insert from sqlplus?

 

[mbrent]

Ok, the way that I am inserting into this table (the one with the sequence - it is named REQUEST) is: If a field in another table (named INV) is changed then I want to insert a record into the REQUEST table. Basically an Audit Table. Therefore, I do not have the REQUEST table on my form. I was just going to insert the records into the REQUEST table (which I am not sure I can do this). Maybe I should paste my code........

begin
select inv.qty into old_qty from inv where inv.item = :inv.item;
if old_qty <> new_qty and new_qty <> 0 THEN
select req_id.nextval into seq from dual;
new_id := seq;
new_cust := :control.cust;
new_add1 := :control.addr1;
new_add2 := :control.addr2;
new_city := :control.city;
new_state := :control.state;
new_zip := :control.zip;
new_item_type := :inv.item_type;
new_comment := :control.req_comment;
new_item := :inv.item;

INSERT INTO REQUEST (req_id,cust, addr1, addr2, city, state, zip, item_type,item, date_req, qty, req_comment)
values (seq,new_cust, new_add1, new_add2,
new_city, new_state, new_zip, new_item_type, new_item,
sysdate, new_qty, new_comment);
end if;
end;

All the variables have been declared also. I also tried doing the maximum(my sequence name). I am getting the same error still. Maybe because I'm not sure I can use an ON-INSERT trigger.

Thank you!

 

[sem]

Do you have another unique constraints on this table?

 

[freedomUSA]

First - &quot;when a field in another table changes&quot;
do you mean &quot;when someone changes the form item&quot;?
I sincerely do not think you mean to be using an ON-INSERT trigger here.

What is sounds like, and correct me if I am wrong - is that you need a when validate item trigger so that after the item is validated you can do your insert statement there. You should test the insert statement in sqlplus to verify that it works.

 

[mbrent]

Sequence number and not null.

 

[mbrent]

I am very new to Oracle, so I am not sure what I should be using exactly.

What would I do with the When-Validate trigger?? And, I am confused about the On-Insert trigger because if I do an insert inside the On-insert trigger what will cause it to fire??

Thanks!

 

[freedomUSA]

The &quot;On&quot; Triggers, especially for beginners, should be left alone until you are more familiar with how things fire. You should put your code in a pre-insert or post-insert if you want this code to run when a record is inserted by the database. The when validate trigger fires when a value in your form item is changed. I usually put a pre-text-item trigger to store the value because the when validate still fires even if they type over the same value, so I use my global in the pre-item trigger to make sure it has actually changed. Then I validate the information (if it is a quantity of something then it would be rare to accept a negative value) after it has been validated you can do your insert statement there.
Sem is correct also about checking your other constraints and indexes - but if you run the insert statement from sqlplus you will usually get a better message. If it accepts the insert from sqlplus - you could just have something wrong in the trigger code.
I suggest you definitely read the forms help file on when things fire since this make a great deal of difference on where you place your code.

 

[Guest_imported]

Ok, try one other thing.

first in your on-insert trigger
declare
seq number;
cnt number;
begin
select req_id.nextval into seq from dual;

to determine whether a field in INV has changed, you are basically saying, if a record exists in inv but does not exist in request, then insert that record into the request table, right?
If this assumption is true, then you need to say:
select count(*) into cnt
from inv,request
where inv.key = request.key;
if cnt = 0 then
message('no new records to insert');
message('no new records to insert');
raise form_trigger_failure;
else insert into request (col1..coln)
select col1..coln
from inv
where inv.key <> request.key;
end if;
exception
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;
end;

Then the following should be in your when-button-pressed trigger:
declare
begin
commit_form;
end;

There could be more to add to this;
this is a good start for you.
Like freedomUSA suggested, try this out in sql*plus first.
good luck.

 

[mbrent]

Um, a little more explanation. This is an inventory program. Each time a user places an order, I subtract the number they ordered from the INV (INVENTORY) Table and update the remanding qty. The forms displays all the items in tabular form (so lets say there are 10 possible items they can order on the form, they can order as many as they want, in one order). When they place this order, I am supposed to update the REQUEST Table with single records for each item that they ordered (or for each INV record that is updated). So, if they ordered 1 of item1, and 2 of item 2, then I would have to insert two records into the REQUEST table. This isn't based on any key because no matter what, if there is an item ordered, I am going to insert a new record. Kinda hard to understand why we are doing it this way. But, it wasn't my decision, I just have to make it work.

Thanks!

 

[Guest_imported]

sorry, I did not see your new posts before posting mine.
But I think that mbrent's problem lies more with understanding his requirements.
As far as on-insert trigger goes, i have used it extensively.
It should serve certain purposes here for him, one of which is the sequence number issue.
the validation should probably be done in post-insert trigger.
But understanding the requirement will be the key here.
One other question I have is do you have two blocks on one canvas with same key?
Normally, when you have an error such as the one you have and shift-f1 does not produce any error, your insert may be going into more than one table and one those tables already has the value you are trying to insert.
keep us posted.

 

[mbrent]

Ok, I'm not sure what was meant by not understanding my requirements. But, I think I just need help figuring out how to get what I want. Thanks to everyone who has tried to help me.

There is only one table on the form, but there are also additonal fields that I am using to get more information. I am taking data from the one table, and the data from the other fields and trying to insert this into the REQUEST Table - which is not on the form. I think I said earlier that I am not sure if I can do this.


Thank you!