Home » Developer & Programmer » Forms » Data update problem (oracle 10g, forms 6i, windows)
Data update problem [message #478715] |
Tue, 12 October 2010 03:15 |
snsiddiqui
Messages: 172 Registered: December 2008
|
Senior Member |
|
|
Please check my query and correct it basically I want to insert/update data from one user to other therefore I write this coding at my form button, when user press button first time its insert data successfully but if user press button again then it should update because data have been inserted in first step.
Actually it is detail table so it can have more then one record against any master. My query fails in updation, it inserts a new record instead of update.
Please find the attached file for checking QUERY.
-
Attachment: Problem.txt
(Size: 2.81KB, Downloaded 1033 times)
[Updated on: Tue, 12 October 2010 03:21] Report message to a moderator
|
|
|
|
|
|
Re: Data update problem [message #478742 is a reply to message #478733] |
Tue, 12 October 2010 05:47 |
snsiddiqui
Messages: 172 Registered: December 2008
|
Senior Member |
|
|
after pressing button at first time then I checked in database, records insters successfully and if I check my select query in SQL it shows correct result mean data found and matched.
As you can see the messages which I am using to show the values at runtime and all values are fatching correctly therefore I am unable to understand, why it is not giving me correct resutl?
Furthermore, would you please tell me that may I used MERGE statement in my forms PL/SQL?
|
|
|
Re: Data update problem [message #478807 is a reply to message #478742] |
Tue, 12 October 2010 12:35 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
declare
cursor c1 is
select gd_pom_id, gd_po_date, gd_challan_no, gd_challan_dt, gd_cim_icode,
gd_cuom_ucode, gd_qty, gd_pom_no, gd_status, gd_serno, gd_req_serno,
gd_gm_id
from factory_data.grn_detl
where nvl(gd_status,'O') = 'O'
order by gd_gm_id, gd_serno;
rec c1%rowtype;
vid factory_data.grn_detl.gd_gm_id%type;
vsno factory_data.grn_detl.gd_serno%type;
vcode factory_data.grn_detl.gd_cim_icode%type;
begin
for rec in c1 loop
begin
select a.gd_gm_id, a.gd_serno, a.gd_cim_icode
into vid, vsno, vcode
from store.grn_detl a
where a.gd_gm_id = rec.gd_gm_id
and a.gd_serno = rec.gd_serno
and a.gd_cim_icode = rec.gd_cim_icode;
update store.grn_detl
set
GD_POM_ID = rec.gd_pom_id,
GD_PO_DATE = rec.gd_po_date,
GD_CHALLAN_NO = rec.gd_challan_no,
GD_CHALLAN_DT = rec.gd_challan_dt,
GD_CIM_ICODE = rec.gd_cim_icode,
GD_CUOM_UCODE = rec.gd_cuom_ucode,
GD_QTY = rec.gd_qty,
GD_POM_NO = rec.gd_pom_no,
GD_STATUS = rec.gd_status,
GD_SERNO = rec.gd_serno,
GD_REQ_SERNO = rec.gd_req_serno
where gd_gm_id = rec.gd_gm_id;
exception
when no_data_found then
insert into store.grn_detl
(gd_gm_id, gd_pom_id, gd_po_date, gd_challan_no, gd_challan_dt, gd_cim_icode,
gd_cuom_ucode, gd_qty, gd_pom_no, gd_status, gd_serno, gd_req_serno)
values
(rec.gd_gm_id, rec.gd_pom_id, rec.gd_po_date, rec.gd_challan_no, rec.gd_challan_dt, rec.gd_cim_icode,
rec.gd_cuom_ucode, rec.gd_qty, rec.gd_pom_no, rec.gd_status, rec.gd_serno, rec.gd_req_serno);
when too_many_rows then
insert into store.grn_detl
(gd_gm_id, gd_pom_id, gd_po_date, gd_challan_no, gd_challan_dt, gd_cim_icode,
gd_cuom_ucode, gd_qty, gd_pom_no, gd_status, gd_serno, gd_req_serno)
values
(rec.gd_gm_id, rec.gd_pom_id, rec.gd_po_date, rec.gd_challan_no, rec.gd_challan_dt, rec.gd_cim_icode,
rec.gd_cuom_ucode, rec.gd_qty, rec.gd_pom_no, rec.gd_status, rec.gd_serno, rec.gd_req_serno);
end;
end loop;
end;
This is your code, rewritten so that some missing parts are included (such as column list in cursor declaration - ALWAYS name all columns, NEVER SELECT *).
Superfluous parts are omitted (such as variable you don't really need as well as IF-THEN-ELSE because if SELECT returns nothing, NO-DATA-FOUND is raised and IF is never executed (the same goes for TOO-MANY-ROWS). Furthermore, if SELECT returns a record, UPDATE will be executed.
If only INSERT part of the code works and UPDATE does not, it means that SELECT fails. You said that it is NO-DATA-FOUND.
As there are two schemas you are working with, are you sure you checked data in the correct schema? Did you, perhaps, insert into one schema and select from another?
I can't tell much more - you have the data, you know what is going on. Unfortunately, Forms 6i doesn't have built-in debugger so you have to use MESSAGE to trace form execution ...
As of using MERGE in Forms' PL/SQL: can't tell for sure, but I'd put my bet to "no". MERGE was introduced with Oracle 9i (I think so; don't remember it existed in 8i) and - usually - Forms PL/SQL engine is at least a step behind database server's PL/SQL engine. You might try it (create a simple statement, no need to do anything complex for testing). It'll most probably fail.
However, if possible, create a stored procedure (which will be able to use MERGE) and call it from a form.
[Updated on: Tue, 12 October 2010 12:38] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Sep 20 02:16:06 CDT 2024
|