Home » Developer & Programmer » Forms » Data update problem (oracle 10g, forms 6i, windows)
Data update problem [message #478715] Tue, 12 October 2010 03:15 Go to next message
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 1032 times)

[Updated on: Tue, 12 October 2010 03:21]

Report message to a moderator

Re: Data update problem [message #478721 is a reply to message #478715] Tue, 12 October 2010 04:07 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously,
select 'x', a.gd_gm_id, ...
into v_check, vid, ...
from store.grn_detl a
...
returns nothing or too many rows, NO-DATA-FOUND or TOO-MANY-ROWS exception is raised and code is inserting new record(s) once again. So - which exception is it? You used MESSAGE built-in so I guess you *know* what failed.
Re: Data update problem [message #478729 is a reply to message #478721] Tue, 12 October 2010 05:10 Go to previous messageGo to next message
snsiddiqui
Messages: 172
Registered: December 2008
Senior Member
it goes into WHEN NO_DATA_FOUND and inserting new record
Re: Data update problem [message #478733 is a reply to message #478729] Tue, 12 October 2010 05:32 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK. Now you have to check why there's nothing in the table. You said that, when that code is executed for the first time, it inserts records into a table. So, either you are wrong (i.e. nothing is inserted), or WHERE clause is wrong (i.e. it prevents records to be fetched).
Re: Data update problem [message #478742 is a reply to message #478733] Tue, 12 October 2010 05:47 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: PDE-PLI011 ERROR
Next Topic: Key press in text item.
Goto Forum:
  


Current Time: Thu Sep 19 17:18:06 CDT 2024