This discussion is archived
9 Replies Latest reply: Apr 4, 2013 11:26 PM by HamidHelal RSS

Help with populating this block please

twinklin_girl Explorer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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).*

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points