13 Replies Latest reply on Jul 11, 2018 2:27 PM by Hilary Farrell-Oracle

    Dynamic Action not working in Data Reporter

    Aakash Sinha

      Hi,

       

      I am using a packaged application in APEX called Data Reporter. The application has a page to create data sources.

       

      CreateDataSource.JPG

       

      There is a dynamic action in the page set on item 'Table/View name'. As per the action, the SQL query of the table or view should populate in the 'SQL Query' text field. But that is not working.

       

      CreateDataSource_PAGE6.JPG

       

      CreateDataSource_PAGE6_action.JPG

       

      The value of the item P6_QUERY is derived from the PL/SQL function:

       

      declare

        q            varchar2(32767);

        i            pls_integer := 0;

        l_schema     varchar2(255);

        l_table_name varchar2(255);

      begin

        for c1 in (select owner

                   from apex_applications

                   where application_id = :APP_ID)  -- APP_ID is 100

        loop

            if :P6_SCHEMA != c1.owner then

                l_schema := lower(:P6_SCHEMA); -- P6_SCHEMA is 'AP'

            end if;

        end loop;

       

        l_table_name := lower(:P6_TABLE_NAME);

       

        q := 'select ';

        for c1 in (select lower(column_name) column_name

                   from all_tab_columns

                   where table_name = :P6_TABLE_NAME   --  P6_TABLE_NAME is 'AP_INVOICES_ALL'

                   and owner = :P6_SCHEMA

                   order by column_id)

        loop

          i := i + 1;

          if i > 1 then

            q := q||','||chr(10)||'       ';

          end if;

          q := q||c1.column_name;

        end loop;

           q := q||chr(10);

           q := q||'from '||l_schema||(case when l_schema is not null then '.' end)||l_table_name;

      return q;

      end;

       

      Am I missing something due to which the dynamic action is not working?

       

      Thanks,

      Aakash.

        • 1. Re: Dynamic Action not working in Data Reporter
          fac586

          Aakash Sinha wrote:

           

          I am using a packaged application in APEX called Data Reporter. The application has a page to create data sources.

           

          There is a dynamic action in the page set on item 'Table/View name'. As per the action, the SQL query of the table or view should populate in the 'SQL Query' text field. But that is not working.

           

           

          The value of the item P6_QUERY is derived from the PL/SQL function:

           

          declare

          q varchar2(32767);

          i pls_integer := 0;

          l_schema varchar2(255);

          l_table_name varchar2(255);

          begin

          for c1 in (select owner

          from apex_applications

          where application_id = :APP_ID) -- APP_ID is 100

          loop

          if :P6_SCHEMA != c1.owner then

          l_schema := lower(:P6_SCHEMA); -- P6_SCHEMA is 'AP'

          end if;

          end loop;

           

          l_table_name := lower(:P6_TABLE_NAME);

           

          q := 'select ';

          for c1 in (select lower(column_name) column_name

          from all_tab_columns

          where table_name = :P6_TABLE_NAME -- P6_TABLE_NAME is 'AP_INVOICES_ALL'

          and owner = :P6_SCHEMA

          order by column_id)

          loop

          i := i + 1;

          if i > 1 then

          q := q||','||chr(10)||' ';

          end if;

          q := q||c1.column_name;

          end loop;

          q := q||chr(10);

          q := q||'from '||l_schema||(case when l_schema is not null then '.' end)||l_table_name;

          return q;

          end;

           

          Am I missing something due to which the dynamic action is not working?

          P6_SCHEMA and P6_TABLE_NAME in the Items to Submit property of the dynamic action.

          • 2. Re: Dynamic Action not working in Data Reporter
            Aakash Sinha

            Still not working

             

            Capture1.JPG

            • 3. Re: Dynamic Action not working in Data Reporter
              Hilary Farrell-Oracle

              Hi Aakash,

               

              In viewing your screenshots, you appear to have the 'Server-side Condition of the 'Load Query'  dynamic action set to 'Never' i.e. it won't run!  I'm going to assume that when you unlocked the Data Reporter packaged app that you've made some changes to the app, and this appears to be one of those changes as that dynamic action should not have that conditional setting.

               

               

              Regards,

              Hilary

              • 4. Re: Dynamic Action not working in Data Reporter
                Aakash Sinha

                Hi Hilary,

                 

                I have added the Server-side condition still, the dynamic action is not working.

                 

                Capture2.JPG

                • 5. Re: Dynamic Action not working in Data Reporter
                  Hilary Farrell-Oracle

                  Hi Aakash,

                   

                  There was no server-side condition set in the locked version of the Packaged App, I believe.  Can I ask why you changed the page in the first place, after you unlocked the packaged app?  Was the page not working as it should be prior to unlocking it?

                   

                  Regards,

                  Hilary

                  • 6. Re: Dynamic Action not working in Data Reporter
                    Aakash Sinha

                    Hi Hilary,

                     

                    The page is not working as expected in the unlocked application as well. I found below defects:

                    1) When I create a data source based on a Table/View, it should pick the columns of that Table/View, but it is not.

                    2) Say there is a Datasource based on table 'A', when I create a report based on that data source, it does not pick up columns of the table 'A'. The pop-up window asks to provide the SQL query anyway and it is mandatory. It does not make any sense. If I have created a data source with table A, the same table columns should be used while creating the report.

                     

                    In the unlocked application, the only change I made to make the SQL Query visible while creating Data source so that I can see if the query is populated based on dynamic action.

                     

                    Thanks,

                    Aakash

                    • 7. Re: Dynamic Action not working in Data Reporter
                      Hilary Farrell-Oracle

                      Hi Aakash,

                       

                      Lets go back to basics here - what version of APEX are you using, and what version of Data Reporter are you using?

                       

                      Regards,

                      Hilary

                      • 8. Re: Dynamic Action not working in Data Reporter
                        Aakash Sinha

                        Hi Hilary,

                         

                        I am using Application Express 5.1.4.00.08. Data Reporter version 2.2.7.

                         

                        Thank you,

                        Aakash.

                        • 9. Re: Dynamic Action not working in Data Reporter
                          Hilary Farrell-Oracle

                          Hi Aakash,

                           

                          Ok that seems a little strange - I've checked our code for 5.1.4, and that release shipped with Data Reporter version 2.2.8, while release 5.1.3 shipped with Data Reporter 2.2.6.  So I'm not entirely sure how you've ended up with a 2.2.7 version.  Given that I'm not sure what version of code you're actually running against, I can only advise that you reinstall the locked version shipped with APEX 5.1.4 - note that you'll need to remove your unlocked version first.  Once you're running against the locked version of code, please try to reproduce the error you initially reported hitting.  If it's still reproducible,  I'll raise this with my colleague on the Packaged Apps dev team.

                           

                          Regards,

                          Hilary

                          • 10. Re: Dynamic Action not working in Data Reporter
                            Aakash Sinha

                            Hi Hilary,

                             

                            I even found the same issue demo workspace on apex.oracle.com.

                            Data Reporter Version is 2.2.6 and Apex version is 18.1.0.00.45.

                             

                            I created a new data source based on a demo table and did not get the SQL query for the table.

                             

                            Create_DS_TABLE.JPG

                             

                            Thanks,

                            Aakash

                            • 11. Re: Dynamic Action not working in Data Reporter
                              Hilary Farrell-Oracle

                              Hi Aakash,

                               

                              Sorry for the delay in following up on this post.  I didn't have the opportunity to properly check out Data Reporter before now.  So in testing the behaviour of the app on apex.oracle.com, which is indeed running 2.2.7, I do see the blank 'SQL Query' display only item on the final page of the 'Create Data Source' wizard.  Having viewed the underlying code, it appears that the problem here is the visibility of that item which is confusing things. As you have selected a table, I don't believe that 'SQL Query' item should be visible at all.  The P37_SQL item on pg 37 of the app is set on pg 6, based upon the selection of 'Derive Query from' being set to 'SQL Query'.  I'll need to get verification from one of the team members who worked on this app, but I think the visibility of that 'SQL Query' item on pg 37 should be based upon the selection for 'Derive Query From'.  I've mailed my colleague requesting some background info on the wizard behaviour.

                               

                              Just in relation to your very first screenshot - what's interesting is that you appear to have 'Derive Query From' set to 'Table', but you're still able to see the 'SQL Query' text area i.e. on pg 6 of the wizard.  The visibility of that text area is based upon a dynamic action, which doesn't appear to be working in your case.  Are you seeing any errors reported in the browser console when you run that page?  Can you compare that page in your app against the version that you see when using your workspace on apex.oracle.com, and identify any differences between the two?

                               

                              Regards,

                              Hilary

                              • 12. Re: Dynamic Action not working in Data Reporter
                                Aakash Sinha

                                Hi Hilary,

                                 

                                As mentioned by you in the previous post, setting sever side condition to Null has resolved the issue. I misunderstood it and set to 'Item not null' instead of Null.

                                By default, it was set as 'Never' so it did not trigger the dynamic action. The SQL query for table type datasource is coming now after setting it to Null (Select).

                                 

                                Thank you for your help on this .

                                • 13. Re: Dynamic Action not working in Data Reporter
                                  Hilary Farrell-Oracle

                                  No worries, Aakash,

                                   

                                  It was really Allan Sitterson on our team that verified the issue for you, via your Support ticket.  Allan verified what I figured was incorrect, which is that the DA shouldn't have a conditional setting of 'Never'.  We'll be logging a bug for this, and endeavour to resolve in for our next release.  Thanks for bringing this to our attention.

                                   

                                  Regards,

                                  Hilary