Home » Developer & Programmer » Forms » update error (oracle 10g forms windows XP)
update error [message #308682] Tue, 25 March 2008 03:40 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
PROCEDURE on_update3 IS
BEGIN
update details_applicant set
name =:details_applicant.name,fathers_or_husbands_name =:details_applicant.fathers_or_husbands_name,
category =:details_applicant.category,religion =:details_applicant.religion,applicant_id =:details_applicant.applicant_id,app_no=:details_applicant.app_no,
dob =:details_applicant.dob,email_id =:details_applicant.email_id,csir_emp =:details_applicant.csir_emp,
phy_handi =:details_applicant.phy_handi,exser_man =:details_applicant.exser_man,
nationality =:details_applicant.nationality,present_address =:details_applicant.present_address,
permanent_address =:details_applicant.permanent_address,dd_no =:details_applicant.dd_no,
bank =:details_applicant.bank,branch =:details_applicant.branch,
dd_date =:details_applicant.dd_date,phoneno_1 =:details_applicant.phoneno_1,
pincode_1 =:details_applicant.pincode_1,phoneno_2 =:details_applicant.phoneno_2,
pincode_2 =:details_applicant.pincode_2,amount =:details_applicant.amount,gender =:details_applicant.gender
where applicant_id=:details_applicant.applicant_id;
if(sql%notfound)then
raise no_data_found;
end if;

END;


update record
above the coding is update the record.i wrote the coding in program units.and i placed a push button in form.when click the button that record update so in button click i wrote follWing program

execute_query;

this program compiled successfully.but in the runtime a error displayed 'UNABLE TO UPDATE THE RECORD'UNIQUE CONSTRAINT VIOLATED

send solution please very urgent?

[Updated on: Tue, 25 March 2008 03:41]

Report message to a moderator

Re: update error [message #308698 is a reply to message #308682] Tue, 25 March 2008 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't update primary/unique key constraint columns in a way that new values violate this constraint. In other words: this question has nothing to do with Forms; you are trying to do something that schema designer doesn't want you to.

Besides, you could have learnt by now that code should be properly formatted and avoid words like "urgent".
Re: update error [message #308728 is a reply to message #308698] Tue, 25 March 2008 05:24 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
not update primary/unique colums.i dont modify that values.
i changed the othe colums.same error display.

Re: update error [message #308763 is a reply to message #308728] Tue, 25 March 2008 06:49 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So you found a bug in Oracle database (NOT!).

Environment:
SQL> create table test
  2  (id number primary key,
  3   name varchar2(20));

Table created.

SQL> insert into test (id, name) values (1, 'Littlefoot');

1 row created.

SQL> insert into test (id, name) values (2, 'Bigfoot');

1 row created.


Proof:
SQL> update test set
  2    id = 1
  3    where id = 2;
update test set
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0071331) violated


SQL> update test set
  2    id = 3
  3    where id = 2;

1 row updated.

SQL> update test set
  2    name = 'Check it once again!'        --> Do it!
  3    where id = 1;

1 row updated.

SQL>
Re: update error [message #311274 is a reply to message #308763] Thu, 03 April 2008 21:06 Go to previous messageGo to next message
mksenthil
Messages: 15
Registered: February 2008
Location: chennai
Junior Member
Hi Littlefoot,

We know how to update in sql. but this same code in forms when we wrote in when-button-pressed trigger in update buttons its showing error...

its showing as unable to insert.....

i dont know why its inserting rather than updating..

can tell me the solution please..

Re: update error [message #311308 is a reply to message #311274] Fri, 04 April 2008 00:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suspect that this is a database block; someone has entered some values in there and - during the COMMIT - code written in the WHEN-BUTTON-PRESSED trigger is executed (it updates some values), but - at the same time - records from the data block are being written into the table.

If that's not the situation, well, you provided too few information (for me, that is) to be able to give another opinion.
Re: update error [message #311549 is a reply to message #308682] Fri, 04 April 2008 14:30 Go to previous messageGo to next message
nitinkumar_007
Messages: 18
Registered: January 2008
Location: PUNE
Junior Member
Hi,

i am agree with the reply ,

Try this ,just make your block " database block : No "
then try your update

I am sure it will work , if you are not violating unique or primary key constraint.

If your update work , then check your block .

Regards
Nitin

[Updated on: Fri, 04 April 2008 14:36]

Report message to a moderator

Re: update error [message #311639 is a reply to message #311549] Sat, 05 April 2008 05:30 Go to previous messageGo to next message
mksenthil
Messages: 15
Registered: February 2008
Location: chennai
Junior Member
hi all,

thanks for replying my doubts....

i solved the update problem Laughing

i have more problem, is follow

1. i wrote the query
Quote:
update_record

in master block on-update trigger.

2. and call the on-update trigger in master block update button thru when-button-pressed using this code
Quote:
execute_trigger('ON-UPDATE');
Message('Successfully Updated');
commit;



3. the following code is for retrieve the records using when-button-pressed trigger
Quote:
begin
select emp_no,emp_name,emp_gender,emp_father_name,emp_husband_name,emp_nationality,emp_category,
emp_subcaste,emp_reserved,emp_desig,emp_dob,emp_doj,emp_dop,emp_doc,emp_bp,emp_incmonth,emp_scalecode,
emp_activecode,emp_residentcode,emp_height,emp_division,emp_section,emp_id_mark1,emp_id_mark2,emp_add1,
emp_add2,emp_add3,emp_city,emp_pincode,emp_state,emp_mailid,emp_phone_no,emp_mobile_no,emp_bloodgrp into
:emp_master.emp_no,:emp_master.emp_name,:emp_master.emp_gender,:emp_master.emp_father_name,:emp_master.emp_husband_name,
:emp_master.emp_nationality,:emp_master.emp_category,:emp_master.emp_subcaste,:emp_master.emp_reserved,:emp_master.emp_desig,
:emp_master.emp_dob,:emp_master.emp_doj,:emp_master.emp_dop,:emp_master.emp_doc,:emp_master.emp_bp,:emp_master.emp_incmonth,
:emp_master.emp_scalecode,:emp_master.emp_activecode,:emp_master.emp_residentcode,:emp_master.emp_height,:emp_master.emp_division,
:emp_master.emp_section,:emp_master.emp_id_mark1,:emp_master.emp_id_mark2,:emp_master.emp_add1,:emp_master.emp_add2,:emp_master.emp_a dd3,
:emp_master.emp_city,:emp_master.emp_pincode,:emp_master.emp_state,:emp_master.emp_mailid,:emp_master.emp_phone_no,:emp_master.emp_mo bile_no,:emp_master.emp_bloodgrp
from emp_master where emp_no = :emp_master.emp_no;
-- EXECUTE_QUERY(no_validate);
execute_query;
exception
when no_data_found then
message('Invalid Emp Number');
Raise form_trigger_failure;
end;


the problem in the retrieve button code execute_query(no_validate) its update records correctly but while retrieve using emp_no this will fetch some other record from the table if i gave with out parameters like execute_query; only fetch the correct record and its show a pop up message like Do you want to save the chages you have made? if click cancel its showing fetched record and when click update button its shows ORA-00001: unique constraint (SENTHIL.EMP_PKEY) violated error, same error when clicking the yes button and when clicking no buttom it fetchs some other record from the table and its able update

how to remove this pop up and retrieve the correct record...
./fa/4105/0/
  • Attachment: popup.gif
    (Size: 31.36KB, Downloaded 221 times)
Re: update error [message #311693 is a reply to message #311639] Sat, 05 April 2008 15:10 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you should research use of the DEFAULT WHERE clause (it is one of the block properties). You can set it programatically (i.e. during runtime) and fetch records that satisfy this WHERE condition(s).

Regarding your example, you'd use "where emp_no = :emp_master.emp_no" in the DEFAULT WHERE.
Re: update error [message #311870 is a reply to message #311693] Mon, 07 April 2008 03:39 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
i dont understand your reply sir
please send solution with code
Re: update error [message #311882 is a reply to message #311870] Mon, 07 April 2008 04:30 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
send solution with code

Sorry, I can't do that. But you may research use of DEFAULT_WHERE, how it is set and what it does.
Previous Topic: how a user do not put date greater than today in date field
Next Topic: type letters on different line using sql
Goto Forum:
  


Current Time: Sun Dec 11 00:10:46 CST 2016

Total time taken to generate the page: 0.12063 seconds