5 Replies Latest reply: May 9, 2013 8:32 AM by smilingbandit RSS

    How to copy records from a block?

    smilingbandit
      Hi All,

      I am working with Oracle Forms 6i and therefore not sure if I will get an answer but I hope one of you experts can help.

      I have a base table data block. For the purpose of explanation, let us say the block has 3 items(fields from the base table) - Name, City, Group_No.

      The block has a button called 'Next Group'.

      Assume, the user creates two records.

      Name City Group_No

      David Toronto 1
      Roger Montreal 1

      The user then clicks on 'Next Group'.

      The requirement is that the two records above that were created, should get copied to the next two records but with a Group_No of 2. So the data should look like -

      Name City Group_No

      David Toronto 1
      Roger Montreal 1
      David Toronto 2
      Roger Montreal 2

      The user can then go ahead and change the Name or City to something else.


      The question therefore is - How to copy the existing set of records to the same block after the last record.

      The number of items in the block can be different, and so a generic solution would be better.

      I guess it can be done using pl/sql tables, but I am not sure how. Sample code would indeed be helpful.

      Your help would be greatly appreciated.

      Thanks is advance.
        • 1. Re: How to copy records from a block?
          Amatu Allah Neveen Ebrahim
          Hi

          Pls use the Built in Function IN When-Create-Record or in When-New-Record-Instance
          DUPLICATE_RECORD;
          But for the group no am not sure how u would keep two records with the same sequence or the like....

          Amatu Allah
          • 2. Re: How to copy records from a block?
            kikolus
            Hi,
            You can use something like this:
            declare
             groupNo  integer; -- change data type appropriate
             no number := :system.cursor_record;
            begin
             groupNo := :block_name.group_no; --to store current group_no;
             first_record;
             while true loop --iterate through all records in the block
               if :block_name.group_no = groupNo then --only affected records;
                 create_record;
                 Duplicate_Record; 
                 :block_record.group_no := groupNo +1; --or any other value
               end if;
               exit when :system.last_record = 'TRUE';
               next_record;
             end loop;
             go_record(no);
            end;
            Hope this helps (I write from memory so it can be some syntax bugs - hope you can fix it :))
            kikolus
            • 3. Re: How to copy records from a block?
              Srikkanth.M
              Hi,

              I have developed a example form and tested, and its works as your requirement.

              i have created a table with three three fields and the same data as u mentioned in post.

              I the button just paste the code and hope it will work.

              -----------------------

              declare
                   cursor c_test is select name,city,group_no from TEST_EXAMPLE;
              begin
                   go_block('XXNEXTBLK');
              for C_test_rec in C_test
              loop
                   :XXNEXTBLK.NAME := C_test_rec.name;
                   :XXNEXTBLK.CITY := C_test_rec.city;
                   :XXNEXTBLK.GROUP_NO :=C_test_rec.group_no;
                   next_Record;
              end loop;
              FIRST_RECORD;
              --go_block('LC_REQ_BLK');
              Exception
                   when too_many_rows then
                   null;
                   when others then
                   null;
              end ;

              declare
              cursor c_test1 is select name,city,group_no from TEST_EXAMPLE;     
              begin
                   go_block('XXNEXTBLK');
              if :XXNEXTBLK.GROUP_NO =1 then
                   last_record;
                   create_record;
                        for C_test1_rec in C_test1
              loop
                   :XXNEXTBLK.NAME := C_test1_rec.name;
                   :XXNEXTBLK.CITY := C_test1_rec.city;
                   :XXNEXTBLK.GROUP_NO :=C_test1_rec.group_no+1;
                   next_Record;
              end loop;
              FIRST_RECORD;
              end if;
              end;

              -------------------------



              Check it, if any issue just send a mail to srikkanthms09@gmail.com, ill forward the fmd to you.

              Thanks & Regards
              Srikkanth.M
              • 4. Re: How to copy records from a block?
                smilingbandit
                Many many thanks to all you guys for suggesting solutions.

                My work day has just started and will let you know how they go.

                Thanks again.
                • 5. Re: How to copy records from a block?
                  smilingbandit
                  HI Srikanth,

                  I am not sure but since you have used a cursor in your solution, I am assuming that the select query is against the base table.

                  Perhaps I was not clear enough describing my problem, but the requirement is to do the copy at the block level only, since none of the records have been saved to the table yet. It is only after all the records have been created for multiple groups, that I would like to save the records to the table.

                  Thanks