9 Replies Latest reply: Apr 5, 2013 1:26 AM by HamidHelal RSS

    Help with populating this block please

    twinklin_girl
      Hello , I need to design a form as follows :

      http://i1059.photobucket.com/albums/t423/enchanted38/form.jpg

      The data will be stored in the one table called employees . There are fields Name1_A,Name1_B,Name1_C,Name1_D,Address1_A,Address1_B,Address1_C,Address1_D .

      I want to create a non-db block & write a procedure to populate the records . Can anyone tell me how to proceed please ? So that for A , record will be filled with Name1_A & Address1_A etc.

      Thanks.
        • 1. Re: Help with populating this block please
          HamidHelal
          twinklin_girl wrote:
          Hello , I need to design a form as follows :

          http://i1059.photobucket.com/albums/t423/enchanted38/form.jpg

          The data will be stored in the one table called employees . There are fields Name1_A,Name1_B,Name1_C,Name1_D,Address1_A,Address1_B,Address1_C,Address1_D .

          I want to create a non-db block & write a procedure to populate the records . Can anyone tell me how to proceed please ? So that for A , record will be filled with Name1_A & Address1_A etc.

          Thanks.
          Hi twinklin_girl,
          Oracle forum should have option to post image. It's very helpful.
          Based on the database table, form creation is easy and more powerful and effective. I don't know why you think otherwise. Please re-consider it.

          But as you want,
          If you want to write all the code in a procedure and call it in a Button, the easy way seems to me is using cursor. For example
          PROCEDURE POPULATE_BLOCK IS
          
               CURSOR C IS
               SELECT Name1,address1
                  FROM employees
                  order by name;
          BEGIN
           go_block('non_db');
           first_reocrd;
           for r1 in c loop
                :name:=r1.name1;
                :address:=r1.address1;
                next_record;
                
           end loop;
           
          
          END;
          Hope this helps


          Hamid

          Mark correct/helpful to help others to get right answer(s).*
          • 2. Re: Help with populating this block please
            CraigB
            Will you need to modify the data displayed? If so, I recommend you create a database package that encapsulates all of the DML (Insert/Update/Delete/Query) and base your data block on the package. Take a look at How to base a data block on a procedure for a demo on how to do this.

            Craig...
            • 3. Re: Help with populating this block please
              twinklin_girl
              Hi HamidHelal , thanks for your help , but what about fields Name2_A,Name2_B which should go be populated in row 2 ??? I have many fields Name1_A,Name1_B etc but I want to keep them in only one db block hence the complexity...
              • 4. Re: Help with populating this block please
                twinklin_girl
                Hi CraigB, yes, some users will get have the right to modify some of the data displayed , will have a look at the package..
                • 5. Re: Help with populating this block please
                  twinklin_girl
                  Will something like this work ??


                  PROCEDURE POPULATE_BLOCK IS

                       CURSOR C IS
                       SELECT Name1,address1,name2,address2
                  FROM employees
                  order by name;
                  BEGIN
                  go_block('non_db');
                  first_reocrd;
                  for r1 in c loop
                       :name:=r1.name1;
                       :address:=r1.address1;
                       next_record;
                  :name:=r1.name2;
                       :address:=r1.address2;
                  next_record;
                  :name:=r1.name3;
                       :address:=r1.address3;


                       
                  end loop;


                  END;
                  • 6. Re: Help with populating this block please
                    Amatu Allah Neveen Ebrahim
                    Hi
                    no need to handel each record 1 ,2,3 assignment inside a loop but next record built in Will handel this inside a loop so do it the way hamid mentioned .

                    Amatu Allah
                    • 7. Re: Help with populating this block please
                      twinklin_girl
                      Next_record will not handle this as in the second row , I need to assign name2 & address2 ,hence the need to re assign.
                      • 8. Re: Help with populating this block please
                        Amatu Allah Neveen Ebrahim
                        Hi
                        create a data block based on ur table u want to populated tabular form
                        Remove the property that indicate it is based on that table as setting db to no ,etc.
                        try the code in when new block instance trigger.

                        regarding the reassign i am really confused may u give us a sample of data the way u want to populate?!

                        Amatu Allah
                        • 9. Re: Help with populating this block please
                          HamidHelal
                          twinklin_girl wrote:
                          Will something like this work ??


                          PROCEDURE POPULATE_BLOCK IS

                               CURSOR C IS
                               SELECT Name1,address1,name2,address2
                          FROM employees
                          order by name;
                          BEGIN
                          go_block('non_db');
                          first_reocrd;
                          for r1 in c loop
                               :name:=r1.name1;
                               :address:=r1.address1;
                               next_record;
                          >
                          >
                               
                          end loop;


                          END;
                          Your code is wrong!!
                          remove the following code
                          :name:=r1.name2;
                               :address:=r1.address2;
                          next_record;
                          :name:=r1.name3;
                               :address:=r1.address3;
                          Next_record will not handle this as in the second row , I need to assign name2 & address2 ,hence the need to re assign.
                          Change Block Navigation Style to CHANGE RECORD

                          Hope this helps


                          Hamid


                          Mark correct/helpful to help others to get right answer(s).*