CODEWITHSUNDEEP
oracle-databaseplsqloracleforms
Hatsin
Hatsin

Reputation: 13

Oracle form builder , pl/sql

I'm new to Oracle Forms and I would really appreciate your help!

I have two multiple record blocks in my Oracle Forms Builder. Let's name them block1 and block2. These blocks have the same three columns - Sequence, Name, Date.

My goal is - upon pressing 'Update' button I need to update block2 based on changes from block1.

Code should compare two blocks. If name or date changed in block1 (Sequence cannot change) then update it in block2.

If record is completely gone from block1 then do not change anything in block2, just go to the next record.

Please let me know if this is possible.

Thanks!

Upvotes: 1

Views: 1027

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

Here's a nested loop option.

As I don't have your tables (and didn't feel like creating ones), I'm using Scott's DEPT table (source for the first block, Dept 1) and its copy created as create table dept2 as select * From dept (which is source for the second block, Dept 2).

This is the layout; screenshot is taken after I pushed the "Update" button:

  • I queried both blocks
  • then I updated first block:
    • for deptno = 10, I set dname to xxx
    • for deptnon = 30, I set loc to yyy
  • then I pushed the button which copied xxx and yyy to the second block

Code that did that looks like this; read comments within the code.

-- go to the second block; navigate to the first record
go_block('dept2');
first_record;

loop
  -- this variable contains current cursor position. It will be used later
  -- with GO_RECORD. Why? To avoid indefinite loop
  :global.rec := :system.cursor_record;

  -- go to the first block and - in a loop - scroll through all its rows
  go_block('dept1'); 
  first_record;
  loop
    -- if DEPTNO columns match, then update second block's values to first block's.
    -- I don't really care whether they are different or not - I perform update unconditionally
    if :dept2.deptno = :dept1.deptno then
         :dept2.dname := :dept1.dname;
         :dept2.loc   := :dept1.loc;
    end if;
    exit when :system.last_record = 'TRUE';
    next_record;
  end loop;

  -- I've done scrolling through the first block. Go to the next row in the second block
  go_block('dept2');
  go_record(:global.rec);
  exit when :system.last_record = 'TRUE';
  next_record;
end loop;

Upvotes: 2

Related Questions