This discussion is archived
7 Replies Latest reply: May 7, 2013 5:23 AM by 1005774 RSS

ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM

1005774 Newbie
Currently Being Moderated
I have a form and I built an LOV on a particular field. The LOV returns the values, I select a value, it goes into the field, but what I want is to have the form retrieve all the information from the database and display (only) all the data. But when I put a value in the field from the LOV, nothing happens. i have tried to have the form go into "enter_query" mode, then select from the LOV, then end the "when button pressed" with "excute_query". But it will ask if i want to save, i say no, then it just displays the first record in the database. what am i doing wrong?

thank you,

Steven
  • 1. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,
    You could use:
    Set_Block_Property( ..., DEFAULT_WHERE, 'table_column=:block.item');
    execute_query;
    Where :block.item is the item populated by the LOV

    Francois
  • 2. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    InoL Guru
    Currently Being Moderated
    This is how Forms works (if your block is based on a table):
    - Go to enter query mode (depending on your key setting this is often F7 or by default F11).
    - Go to your LOV item and open the LOV
    - Select a value from the LOV. This value is shown in your field.
    - Click on Execute Query.
  • 3. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    1005774 Newbie
    Currently Being Moderated
    within the "button pressed" i started the query with "enter_query" then the list values, then "excute_query", but that doesn't work properly. However, if i select the 'enter_query' from the menu bar, then my LOV, all works just fine. I don't want to have my customers to have to do this. I want the block to be in query mode, select from the LOV, then execute each and every time. suggestions?
  • 4. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    1005774 Newbie
    Currently Being Moderated
    here is my complete code on this button: when i press the button, it keeps asking me if i want to save it (if i say yes, it adds it to the table in the database, which i do not want). however, if i first select "enter query" from the menu bar, and then click on my button, everything works as expected.

    set_item_property('SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE',queryable,property_true);
    --Set_Block_Property( 'SL_3_ITEMS_HEADER', DEFAULT_WHERE, 'parker.SL_3_SHORT_TITLE=:sl_3_short_title');

    declare
         SL3 varchar2(12);
    BEGIN
         
         go_item('SL_3_SHORT_TITLE');
    list_values;
    SL3 := :SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE;
         if SL3 is not null then
              select SL_3_SHORT_TITLE into :SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE
              FROM parker.SL_3_ITEMS_HEADER WHERE SL_3_short_title = SL3;
         ELSE
              message ('You need to specify an SL-3 to be able to search');
         end if;
         exception
              when no_data_found then
              message('An SL-3 with that name does not exist.');
              clear_form;
         end;
         
         execute_query;
  • 5. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    InoL Guru
    Currently Being Moderated
    You have to set the block in enter query mode first. What you can do is create a when-new-block-instance trigger on your block:
    enter_query;
    It will probably never really work the way you want it. This works when you first open the form, but will not work when you click the button a 2nd time. This just does not work in a when-button-pressed:
    enter_query;
    list_values;
    execute_query;
    IMHO, it's better to have users get to know how Forms works than trying to get Forms to work the way your users want it to work.
    There are workarounds with a search item in control block instead of the base table block, but is is just so much easier to use standard Forms functionality.
  • 6. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    1005774 Newbie
    Currently Being Moderated
    Thank you for all the suggestions, but below is how i actually got it to work. Created a new control block that was non-database related.

    clear_form;
    go_item('text_item60'); --control block, non-database related item.
    list_values; LOV created from the database.

    set_item_property('SL_3_ITEMS_HEADER.SL_3_SHORT_TITLE',queryable,property_true);
    set_block_property ('SL_3_ITEMS_HEADER', DEFAULT_WHERE, 'SL_3_SHORT_TITLE like :BLOCK59.text_item60'); --copy from control_block into actual field to be used.

    go_item('SL_3_SHORT_TITLE'); -- go to the actual field and execute. works everytime.
    EXECUTE_QUERY;

    Edited by: 1002771 on May 6, 2013 11:27 AM
  • 7. Re: ORACLE DEVELOPER 10G FORMS, LOV AND QUERYING THE FORM
    1005774 Newbie
    Currently Being Moderated
    thank you everyone.

Legend

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