7 Replies Latest reply on Nov 19, 2012 4:05 PM by ups_mike

    Refresh Interactive Report Definition via PLSQL


      I am currently working on set of generic database-apex applications.

      That means I have two applications. The first is the admin apex application used to define columns and tables.
      The second application is the one for the end users, where they can view and edit the values.

      My problem now is: Every time the admin adds or removes a column I need to edit the user-apex application, open the region definition of all interactive reports and switch to the report attributes to add/remove the columns of the report.
      I would like to automate this step via plsql. How can I achieve this?

      I guess the first step would be to load a list of all IRs...

      select * from apex_application_page_ir where application_id = 133;

      But I don't know how to reload the columns...

      Can anyone help?
      Thanks in advance!
      BR Mike
        • 1. Re: Refresh Interactive Report Definition via PLSQL

          Somewhere inside "Admin" application i would put this PLSQL code, smth like this:
                  SqlQuery VARCHAR2 (2000 CHAR);
                  -- You select sql query of IR from your admin application ! 
                  SELECT sql_query
                  INTO SqlQuery
                  FROM apex_application_page_ir
                  WHERE page_id = 10 and application_id = 100; -- For example IR in your admin application (page - 10, application 100)  !
                  -- dbms_output.put_line(SqlQuery); -- Check if everything is ok inside Apex sql workshop !
                  -- Now you update sql query of IR in your client application !
                  UPDATE apex_application_page_ir
                  SET sql_query = SqlQuery
                  WHERE page_id = 15 and application_id = 105; -- For example IR in your client application (page - 15, application 105)
          If you want to update all IR with a single code, mb you should try with declaring a cursor, smth like this:
              DECLARE CURSOR MY_IRS IS (SELECT * FROM apex_application_page_ir where application_id = 100);
          then loop through it and update client application.

          Also keep in mind that inside this table there are columns like SHOW_SELECT_COLUMNS and SHOW_SAVE and that you can set them from No to Yes enabling users to choose there own IR settings.

          And in the end probably the most important question why do you have separated applications for admin and client users?

          Hope this helps you.
          • 2. Re: Refresh Interactive Report Definition via PLSQL
            Vite DBA
            Hi Mike,

            it seems to me that you are reinventing the wheel.

            One of the big selling features of Apex is that it provides a development environment that is simple enough for reasonably savvy end users to use to build and maintain there own applications. The first application you describe, the admin application, sounds like a reduced functionality SQL Workshop. Perhaps you should try letting at least some of your more savvy users access into Apex as developers and allow them to create their own applications.

            Also, the way you are working seems a little different to what I would expect. Normally I would try and control the database schema tightly so that users don't break anything too important and then allow them to create reports and forms to their hearts content.

            Another option you could look at is to use Apex websheets. These are more limited in terms of general application design, but are more flexible for defining data structures.

            Another issue is that you are basically asking how to hack the Apex metadata, which could lead to unpredictable and unsupported results.

            • 3. Re: Refresh Interactive Report Definition via PLSQL
              Hello Andre, Hello Mimi!

              Thank you for your messages.
              First to your idea Mimi, i think you brought me on the right track. Using your update statement doesn't works for me.
              There are some missing permissions I couldn't grant and in addition I think this would hardly work because apex_application_page_ir is a complex view.

              But I am currently working on a solution using the wwv_flow_worksheet_standard.synch_report_columns function. Code like this:

              l_flow_id NUMBER (20);
              l_region_id NUMBER (20);
              l_query VARCHAR2 (4000);
              l_result VARCHAR2 (4000);

              SELECT application_id, region_id, sql_query
              INTO l_flow_id, l_region_id, l_query
              FROM apex_application_page_ir
              WHERE page_id = 9 AND application_id = 133;

              APEX_040100.wwv_flow_worksheet_standard.synch_report_columns (p_flow_id => l_flow_id,
              p_region_id => l_region_id,
              p_query => l_query,
              p_add_new_cols_to_default_rpt => 'Y');

              Unfortunately it doesn't work. When executing the synch_report_columns procedure the following error raises.

              ORA-01003: no statement parsed
              ORA-06512: at "SYS.DBMS_SYS_SQL", line 1685
              ORA-06512: at "SYS.DBMS_SQL", line 629
              ORA-06512: at "APEX_040100.WWV_FLOW_WIZARD_API", line 840
              ORA-06512: at "APEX_040100.WWV_FLOW_WORKSHEET_STANDARD", line 2735
              ORA-06512: at line 23

              Maybe you or someone else can reproduce the error?
              Therefor you simply need an interactive report on a page, based on a view. Remove some column of the view definition-the IR will display an error like this:

              ORA-20001: get_dbms_sql_cursor error ORA-00904: "DUV_FOCUS_OFFSET": invalid identifier

              Then execute the code above (use your page and app_id).
              After the execution the IR should work again. In my opinion...

              @Andre: The application I am working on is a critical pice of software used in a highly sophisticated semiconducter fab.
              It is simply impossible to train some of the shift leaders or operators on apex, in fact it is a great thread if someone else can change the application and produces a downtime.

              So for me it is necessary to script everything as far as possible. I finished nearly everything, simply the synch of the report columns is missing...

              BR Mike
              • 4. Re: Refresh Interactive Report Definition via PLSQL
                Vite DBA
                Hi Mike,

                interesting situation.

                So are you saying that the end users have tha ability to change the underlying database schema through the admin application by adding and dropping columns from tables?

                • 5. Re: Refresh Interactive Report Definition via PLSQL
                  Hello Andre!

                  Andre wrote:

                  So are you saying that the end users have tha ability to change the underlying database schema through the admin application by adding and dropping columns from tables?
                  No, that's not correct. I have for example the following tables:

                  The user is only allowed to add, modify and delete records in this table. The apex application builds the GUI based on the rows of these tables.

                  This is the best solution for me because typically the GENERIC_TABLES and GENERIC_COLUMNS records will change at least every six months. Every time the definition changes the apex gui needs to be modified. This is already implemented. Simply the last step of refreshing the interactive reports columns definition is missing.
                  Currently I need to edit and save all reports in the apex development gui.

                  br Mike
                  • 6. Re: Refresh Interactive Report Definition via PLSQL
                    Vite DBA
                    Hi Mike,

                    It's an interesting architecture to build your own metadata and storage structure on top of the Oracle database. I've seen it done before (see Pega Systems) and I've used something like it myself in limited implementations. If it works for you and gives you the performance and integrity you require, then go for it, but it's not a path I would recommend lightly.

                    As far as being able to refresh IR's once changes have been made to your metadata repository, I can't find any published API which accomplishes this. This means that in order to do this, you will have to hack the Apex engine, which is obviously unsupported, undocumented and subject to change without notice. For hints on how this might be done, I would look at the files generated by the Apex application export tool. The export tool produces what are basically SQL files which are basically calls to the Apex internal API for creating an application and its components.

                    The only other option I can think of that might work would be to create a classic report based on a query returned by a function and specify heading names based on column names. This will give you a generic report based on the results of the query returned by the function with the headings determined by the column names (or aliases). Obvioulsy you will loose all the nice built in features of an IR and have to recreate any that you require.

                    I hope this helps.

                    1 person found this helpful