7 Replies Latest reply: Nov 14, 2013 8:55 AM by CraigB RSS

    Create block from many tables

    NZK

      Dear All

       

      I want to create a table using many tables. (ie. Like sales summary by salesperson). To do that I created a Data Block using the view;It works fine only if it is used by only one user. If it is used by couple of user the form hangs because each user give different parameters (ie Date Range or Salespersons). The same issue comes even if I try to insert into a table.


      To overcome this issue i tried it using From Clause Query in the Data Block. When I run the Form using From Clause query it needs a Primary Key.

      How to overcome this issue?


      Thank you in advance.


      NZK

        • 1. Re: Create block from many tables
          indra budiantho

          I still do not understand, what is From Clause?

          • 2. Re: Create block from many tables
            NZK

            Dear indrabudiantho

             

            In the Data block property, Under Database, Query Data Source type, I selected FROM clause query instead of Table.

             

            Thanks

             

            Nzk

            • 3. Re: Create block from many tables
              CraigB

              NZK wrote:

               

              I want to create a table using many tables. (ie. Like sales summary by salesperson). To do that I created a Data Block using the view;It works fine only if it is used by only one user. If it is used by couple of user the form hangs because each user give different parameters (ie Date Range or Salespersons). The same issue comes even if I try to insert into a table.

               

              To overcome this issue i tried it using From Clause Query in the Data Block. When I run the Form using From Clause query it needs a Primary Key.

              How to overcome this issue?

              Odd that the View gave you problems.  I've built numerous Forms based on views and have not had this issue.

               

              Given the fact that you need to be able to update the underlying tables, I recommend you build your Data Block based on a Stored Procedure instead of a From Clause query.  Views and From Clause query blocks are non-updatable so you would have to write On-Insert/Update/Delete triggers in your form to actually perform the DML.  By creating  Stored Procedure (package) in the database you ecapsulate all of this logic in the package and the Form will treat it as a table.  There are numerous examples around the Internet for building a block based on a procedure and Oracle has published a few documents on the topic as well.  Here's one to get you started - Form based on stored procedure.

               

              Craig...

              • 4. Re: Create block from many tables
                NZK

                Dear Craig

                 

                Thank you for your reply.

                 

                I want to create a form where user can view data (View Sales figures) according to the date range they provide. As these sales figures are coming from many table, what I used to do is,

                 

                1. User selects the date range and press execute button

                2. Data selected and inserted into a table (where there is a Data Block created linking this table)

                3. And display the data in the Form.

                 

                This method is working fine only if 1 user uses the form But if it is used by many users i face the following issues.

                 

                1. User cannot rely on it as many user enter different date range and those are inserted into the same Table and displayed in the form.

                 

                Is there anyway to overcome this issue. I don't want to update the underlying tables. Only display purpose.

                 

                Hope you understand my question.

                 

                Thanks

                 

                NZK

                • 5. Re: Create block from many tables
                  O.Developer

                  HI

                   

                  First you develop  your  script in SQL .

                  Then Create view based on this  SQL.

                  Develop from Using this  VIEW.

                   

                  You should be able to get your  goal.

                  • 6. Re: Create block from many tables
                    NZK

                    Thanks O.Developer

                     

                    I will check

                     

                    NZK

                    • 7. Re: Create block from many tables
                      CraigB

                      1. User selects the date range and press execute button

                      2. Data selected and inserted into a table (where there is a Data Block created linking this table)

                      3. And display the data in the Form.

                       

                      I agree with O.Developer, this sounds like it can be best solved using a database View.  As recommended, write a SQL statement that selects all of the data you want displayed in your form. Use this SQL statement to create a view. Now, create a data block in your form using this view.  To enable your users to select a date range, then create a Control Block (non-base table block) with your date range fields.  Finally, in your "execute" button, modify the data block's WHERE clause using the SET_BLOCK_PROPERTY('BLOCK_NAME',DEFAULT_WHERE, 'YOUR DATE RANGE...') built-in.  Your code would look something like the following:

                       

                       

                      
                      -- Sample When-Button-Pressed trigger 
                      -- Code is untested...                
                      DECLARE
                        v_where   VARCHAR2(500);
                      BEGIN
                        GO_BLOCK('MY_VIEW_BLOCK');
                        -- Check to make sure a date range was entered...
                        IF ( :MY_CTRL_BLOCK.START_DATE IS NOT NULL 
                             AND :MY_CONTROL_BLOCK.END_DATE IS NOT NULL ) THEN 
                          v_where := 'MY_VIEW.SOME_DATE BETWEEN :MY_CTRL_BLOCK.START_DATE '
                        ||' AND :MY_CTRL_BLOCK.END_DATE';
                          Set_Block_Property('MY_VIEW_BLOCK',DEFAULT_WHERE, v_where);
                        ELSE
                          -- No date range was given, clear any WHERE clause and query all records.
                          Set_Block_Property('MY_VIEW_BLOCK',DEFAULT_WHERE, ' '); 
                        END IF;
                          
                        Execute_Query;    
                      END;
                      
                      

                       

                      Hope this helps.


                      Craig...