13 Replies Latest reply: Jul 8, 2014 12:24 PM by CraigB RSS

    Populate a datablock where sources can be from multiple tables

    951614

      Probem statement

      We are oracle forms 10g version

       

      Have a single block form with 5 fields. There is a LOV called Feed.

      Values in the LOV are IN, OUT, ALL

      When LOV is IN , data should be fetched from table a

      When LOV  is OUT, data should be fetched from table b

      When LOV is ALL, data should be fetched from table a and B using a UNION

       

      Need inputs

      1) Database view "V" on table a and table b and

      2) Use view to populate the data

      3)

      If yes, what are the other form triggers we have to take care of while updating or deleting the data from the form --> pre,post or on triggers or anything else. We are thinking htat it would be

      post update, post delete. Inside the post update and post delete, based on feed value we can modify the existing tables a and b. Would this approach work from a design point of view.

       

      If this doesn't work, what are the other alternatives we have

       

      Thanks

      Regards

        • 1. Re: Populate a datablock where sources can be from multiple tables
          salute-Salem

          how could you do dml operations on a complex view !!!

          • 2. Re: Populate a datablock where sources can be from multiple tables
            Zlatko Sirotic

            Using INSTEAD OF database triggers.

            Of course, updateable join view can work without INSTEAD OF triggers.

             

             

            Regards

            • 3. Re: Populate a datablock where sources can be from multiple tables
              Marwim

              I would use Instead-Of- Triggers on the view. Then you can use the default behaviour in your module and you can do update/delete on the view outside forms, e.g. when you need to change data by scripts.

              • 4. Re: Populate a datablock where sources can be from multiple tables
                Andreas Weiden

                I'm also with the INSTEAD-OF-trigger. You might need to add a ON-LOCK-trigger and do manual locking.

                • 5. Re: Populate a datablock where sources can be from multiple tables
                  CraigB

                  This sounds like a perfect use of a From Clause Query based block.  This would enable you to programatically change the data source based on which IN, OUT, ALL parameter is selected. 

                  You could use a Key-ListVal trigger to display your LOV and then act on the value selected in the LOV to set the QUERY_DATA_SOURCE_NAME property of your block.  You would then use the On-Insert, On-Update and On-Delete triggers to handle the DML.

                   

                  Another option would be to create a database package and then use a Procedure based data block.  Your database package will have you insert, update and delete procedures as well as the locking procedure.  THere are numerous examples on the internet of Procedure Based blocks.

                   

                  Craig...

                  • 6. Re: Populate a datablock where sources can be from multiple tables
                    951614

                    Hi

                     

                    We are getting this error when we are including the following code in KEY-listval trigger

                    Error is  - Cannot set the blocks query data source

                    Action being performed - selecting one value among IN, OUT and ALL

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

                    Block 'BLK_ERRORS' properties are

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

                    database data block Yes

                    Enforce Primary Key No

                    Query Allowed Yes

                    Query Data Source Type From clause query

                    Query data source name "View"

                    Query Data Source Columns Null

                    Insert allowed, Update allowed Yes

                    Key Mode Automatic

                     

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

                    Code in KEY-listval trigger

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

                    DECLARE

                      lv_seql :=

                        'SELECT columns

                         FROM view

                        WHERE ='||:block.text_feed;

                    begin


                    l_show_lov := show_lov('LOV_FEED'); --This LOV contains values called IN, OUT AND ALL --

                    --This LOC will populate the :block.text_feed

                     

                    GO_BLOCK('BLK_ERRORS');

                    set_block_property('BLK_ERRORS',QUERY_DATA_SOURCE_NAME,lv_sql);
                    clear_block(no_validate);
                    execute_query;

                     

                    Please let us know how to fox this or any URL pointers which could be helpful. Found many links but they were not giving desired results

                     

                    Thanks

                    • 7. Re: Populate a datablock where sources can be from multiple tables
                      CraigB

                      One of the keys to using a From Clause Query based data block is that you must have a corresponding Block.ITEM defined for each column returned by your Data Source query.  This can be a little tricky if query modifications in your code changes the number of columns returned.  If your code does change the number of columns, then you will want ensure your query always returns the same number of columns, but if certain columns are not needed, just alias them as NULL (EG: SELECT NULL AS your_column_name).

                       

                      Another key to remember with From Clause Query based blocks is to remember that the query is parsed by the Database and not in your Form.  Therefore, you can't reference Form Items directly in the query, but rather you have to reference the "Value" of the Form Item.  You must also include the Opening and Closing parenthesis in the SQL String.  With this in mind, your query:

                        lv_seql :=

                          'SELECT columns

                           FROM view

                          WHERE ='||:block.text_feed;

                       

                      Will need to be rewritten as:

                       

                      lv_seql := ' ( SELECT columns
                                           FROM view
                                           WHERE '||:BLOCK.text_feed ||' ) ';
                      
                      

                       

                      This change assumes, of course, that your :BLOCK.TEXT_FEED column includes propertly formated WHERE clause.  Also, notice that I added Opening and Closing parenthesis to the SQL String.  Remember, this is a FROM CLAUSE Query so the underlying query that is executed on the database looks like this:

                       

                      SELECT <List of Columns>
                       FROM <( Your Sql Statement Here )>
                      
                      

                      Forms does not include the "(" ")" by default so your code has to ensure it includes the opening and closing parenthesis.

                       

                      Hope this helps.

                       

                      Craig...

                      • 8. Re: Populate a datablock where sources can be from multiple tables
                        951614

                        HI,

                         

                        We are able to retrieve the data based on the selection of IN,OUT or ALL after following the above steps.

                        In our case, there are 10 columns which will be displayed in the form. If user wants to select a specific row, he will query it with a value in a column and ctrl+F11 should give the corresponding row. How can we achieve this?.

                         

                         

                        Thanks.

                        • 9. Re: Populate a datablock where sources can be from multiple tables
                          CraigB

                          951614 wrote:

                           

                          ...In our case, there are 10 columns which will be displayed in the form. If user wants to select a specific row, he will query it with a value in a column and ctrl+F11 should give the corresponding row. How can we achieve this?...

                           

                          To achieve this, you have to over-ride the default Pre-Query or Execute-Query triggers so you can include the user's "Query Value" in your From Clause Query (FCQ) sql statement.  Since you reference using the CTRL+F11 to execute the query, I will assume that your user also uses the F11 key to put the form into Query Mode.  Based on this assumption, I recommend using the Pre-Query trigger to modify your FCQ SQL statement.  Your code woud look something like the following.  Keep in mind that you have to account for all 10 rows the user can potentially query from in your code.

                           

                           

                          /* Sample Pre-Query trigger */
                           /* Code is not tested. This code also assumes that all queried items are */
                           /* character fields. */
                           DECLARE
                                v_fcq_sql     VARCHAR2(2000);
                           BEGIN
                                v_fcq_sql := < Your Main Query here...>
                           
                                -- Now, lets check to see which columns the user has entered a search criteria in...
                                IF ( :YOUR_BLOCK.ITEM1 IS NOT NULL ) THEN 
                                     v_fcq_sql := v_fcq_sql || ' AND column1 = '' ' || :YOUR_BLOCK.ITEM1 ||' '' ';
                                END IF;
                                IF ( :YOUR_BLOCK.ITEM2 IS NOT NULL ) THEN  
                                    v_fcq_sql := v_fcq_sql || ' AND column2 = '' ' || :YOUR_BLOCK.ITEM2 ||' '' ';
                                END IF;
                            -- Do this same construct for Items 3 - 10
                                IF ( :YOUR_BLOCK.ITEM10 IS NOT NULL ) THEN  
                                    v_fcq_sql := v_fcq_sql || ' AND column10 = '' ' || :YOUR_BLOCK.ITEM10 ||' '' ';
                                END IF;
                                -- Lasly, add the closing Paranethisis to the SQL Statement...
                                v_fcq_sql := v_fcq_sql || ')';
                          
                                -- Finally, Assign the FCQ Sql to the Query Data Source propert of your Block.
                                Set_Block_Property('YOUR_BLOCK',QUERY_DATE_SOURCE_NAME, v_fcq_sql);
                           END;
                          

                           

                          As I mentioned above in the code sample, this is untested - it simply demonstrates the how to use Default Query functionality on a From Clause Query block.

                           

                          Craig...

                          • 10. Re: Populate a datablock where sources can be from multiple tables
                            951614

                            Hi,

                             

                            1. We have used WHEN-LIST-CHANGED trigger to populate details based on the selection of LIST ITEM 'X' (IN,OUT,ALL) and is working fine.

                               when we used KEY-LISTVAL trigger ,it was not working properly. So we have used WHEN-LIST-CHANGED trigger.

                            2. Regarding PRE-QUERY trigger, we have included the code mentioned by you in Pre-query trigger at block level and we are able to query the records based on value in any column. But in this case, WHEN-LIST-CHANGED trigger is not getting fired. i.e., for any value X selected, it is displaying ALL records only.

                             

                            How can we fix this?

                             

                            Thanks.


                            • 11. Re: Populate a datablock where sources can be from multiple tables
                              CraigB

                              I was not aware you were using the When-List-Changed trigger to populate your details block.  This is why it is important to explain what you are doing when you post a question.   Remove the code from your Pre-Query and just put everything in your When-List-Changed trigger.  After modifying the Query Data Source property simply call the Execute_Query(); built-in.

                               

                              Craig...

                              • 12. Re: Populate a datablock where sources can be from multiple tables
                                951614

                                Hi

                                 

                                When we put every thing in When-List-Changed trigger, during query mode we observed that when-list-changed trigger is not firing and hence the functionality is not working.

                                Please let us know if we missed anything.

                                 

                                Thanks.

                                • 13. Re: Populate a datablock where sources can be from multiple tables
                                  CraigB

                                  OK. Take a look at the properties of your When-List-Changed trigger, specifically the Fire in Enter-Query Mode property.  If this is "Yes" then the trigger should fire.  If it is "No" then it won't.


                                  Craig...