Home » Developer & Programmer » Forms » FRM-40508 : ORACLE error: unable to INSERT record.
FRM-40508 : ORACLE error: unable to INSERT record. [message #574616] Mon, 14 January 2013 04:14 Go to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
Hi,
I have an interface composed of five elements:
1.display_item (primary key)
2-list_item (foreign key) / / dynamic list :this is the cause of the error
3-lis_item (foreign key) / / dynamic list
4-text_item
5-button (insert commit)

when I click the button, an error is displayed:
FRM-40508: ORACLE error: unable to INSERT record.
in detail: ora-02291 integrity constraint Violated - parent key not found

Note:
-the elements of two lists already exist in the parent table!!
-I use a block based.
-button code:
commit_form();

[Updated on: Mon, 14 January 2013 04:17]

Report message to a moderator

Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574623 is a reply to message #574616] Mon, 14 January 2013 06:14 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
Oracle doesn't lie. So the value used in the list item doesn't match the value in the parent table.
You do realise that list items have a display value and a stored value. Display value is what you see on the screen, stored value is what is sent to the database.

So how are populating the list item?
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574632 is a reply to message #574623] Mon, 14 January 2013 07:44 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
Note -the elements of two lists already exist in the parent table!!
the list is filled with a group of record using a query
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574635 is a reply to message #574623] Mon, 14 January 2013 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can read. Which is why I said:
cookiemonster wrote on Mon, 14 January 2013 12:14
Oracle doesn't lie. So the value used in the list item doesn't match the value in the parent table.

You may think it does exist in the parent, you're wrong.

Which is why I asked my question.
What query?
What's the parent table?
What columns are in the fk?
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574636 is a reply to message #574635] Mon, 14 January 2013 08:16 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
Here is the code to create the list:
PROCEDURE pop_list IS





/*
** Built-in: CREATE_GROUP_FROM_QUERY
**Example: Create a record group from a query, and populate it.
*/




list_id ITEM;
list_name VARCHAR2(40) := 'emp.id';
rg_name VARCHAR2(40) := 'abc';
rg_id RecordGroup;
errcode NUMBER;
outcome NUMBER;
BEGIN
  





-- Make sure group doesn't already exist

list_id :=Find_Item(list_name);
rg_id := Find_Group( rg_name );

-- If it does not exist, create it and add the two
-- necessary columns to it.

IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM grade',FORM_SCOPE,200);
END IF;
IF Not Id_Null(rg_id) THEN
delete_group(rg_id);
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM emp',FORM_SCOPE,200);

END IF;

-- Populate the record group

errcode := Populate_Group_with_query( rg_id,'SELECT to_char(id),to_char(id) FROM emp' );
IF errcode = 0 THEN
outcome := GET_GROUP_ROW_COUNT(rg_id);
--Message(outcome);
END IF;


Clear_List(list_id);
Populate_List(list_id,rg_id);





END;


the column 's : id(foreign key)
in the parent table (dept), there are these values:
123
77
and in the list, there are also the same values:
123
77

i have 2 FK:
Id(from the table parent dept)
mgr_no(from the table parent manager)
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574643 is a reply to message #574636] Mon, 14 January 2013 11:04 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
if I give a value to the list, the insertion will be made !!!
why?

Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574656 is a reply to message #574643] Mon, 14 January 2013 13:13 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) why are you to_charing id? If it's a number in the parent tables then it should be a number in the one your block is based on.
2) Why are do you have a query against grade when you're not using it (the result is immediately overwritten)?
3) Why do you talk about dept and manager when there's no reference to them in the sample code?
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574660 is a reply to message #574656] Mon, 14 January 2013 13:33 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
1) the id is "varchar2"
2)emp not "grade" //typo
3)i must to use item_list for the 2 columns
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574665 is a reply to message #574660] Mon, 14 January 2013 14:09 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) if the parent table column is number then the child column should also be number. If the parent column is varchar2 then the to_char is silly. Either way it should not be there.
2) So you're running the same query twice to populate the same group? That makes even less sense. Or are there more typos? Try copying and pasting the code.
3) That doesn't answer my question at all. List all the tables involved. State which one is parent and child for each fk. State which one the block is based on.
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574666 is a reply to message #574665] Mon, 14 January 2013 14:17 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
1) the patent table column is varchar2
2)the filling is not done 2 times! and is carried out according to a condition
3)i don't understand ...

NOTE:
when I keep the code in when-new-form-instance and adding to the end of the code this instruction:
:emp.id :=Get_List_Element_Label( list_id, 1 );
The insertion will be made without error
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574708 is a reply to message #574666] Tue, 15 January 2013 06:30 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) So why are you to_charing a char?
2)
Your code:
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM grade',FORM_SCOPE,200);
END IF;
IF Not Id_Null(rg_id) THEN
delete_group(rg_id);
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM emp',FORM_SCOPE,200);

You've got 2 create_group_from_query for the same record group with 2 different queries. Why?

3) You don't understand a list of simple questions? What exactly do you not understand?
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574716 is a reply to message #574708] Tue, 15 January 2013 07:34 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
1)you're right Wink
2)depending on the condition, the group will be created
3)nothing ,thnx
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574717 is a reply to message #574716] Tue, 15 January 2013 07:45 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
2) Again - why are you using two different selects for the same list item? Why is there a condition at all?
3) You don't understand "list all tables involved"? You don't understand "State which one the block is based on"?
Really?
You have a form that is giving a foreign key error. I want you to list all the tables that are involved - the one the datablock is based on and the ones referenced by the foreign keys. You've given bits of the list so far. I want the full list. With a clear explanation of the relationships between the tables.

How do you expect us to be able to tell what the problem is without that information?
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574721 is a reply to message #574717] Tue, 15 January 2013 07:59 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
2)you're right, I committed a syntax error "emp" instead of "grade", but in my code I have put "emp"
3)I said, I understand the list, thank you
Finally with this instruction:(when I put it at the end of code)
:emp.id :=Get_List_Element_Label( list_id, 1 );

, the problem will be solved

[Updated on: Tue, 15 January 2013 08:03]

Report message to a moderator

Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574722 is a reply to message #574721] Tue, 15 January 2013 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
2) So does that mean both queries are identical? Post the correct code.
3) That wasn't at all clear from what you posted. If you understand my questions, answer them.
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574725 is a reply to message #574722] Tue, 15 January 2013 08:12 Go to previous messageGo to next message
goldray
Messages: 104
Registered: December 2012
Senior Member
2)
i remplaced this part :
IF Id_Null(rg_id) THEN
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM emp',FORM_SCOPE,200);
END IF;
IF Not Id_Null(rg_id) THEN
delete_group(rg_id);
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM emp',FORM_SCOPE,200);

by this:
rg_id := Create_Group_From_Query( rg_name,'SELECT to_char(id),to_char(id) FROM emp',FORM_SCOPE,200);

Now the list is populated Wink
the problem 's solved ! thank you very much Sir Smile

[Updated on: Tue, 15 January 2013 08:40]

Report message to a moderator

Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574730 is a reply to message #574725] Tue, 15 January 2013 09:09 Go to previous messageGo to next message
cookiemonster
Messages: 10930
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're still to_charing a char.
Re: FRM-40508 : ORACLE error: unable to INSERT record. [message #574735 is a reply to message #574730] Tue, 15 January 2013 09:19 Go to previous message
goldray
Messages: 104
Registered: December 2012
Senior Member
rg_id := Create_Group_From_Query( rg_name,'SELECT id,id FROM emp',FORM_SCOPE,200);

Wink

[Updated on: Tue, 15 January 2013 09:19]

Report message to a moderator

Previous Topic: How to run Direct printing in forms 10g
Next Topic: How To call a java class into oracle forms11g
Goto Forum:
  


Current Time: Tue Sep 02 18:56:34 CDT 2014

Total time taken to generate the page: 0.09925 seconds