This content has been marked as final. Show 7 replies
Somewhere inside "Admin" application i would put this PLSQL code, smth like this:
If you want to update all IR with a single code, mb you should try with declaring a cursor, smth like this:
DECLARE SqlQuery VARCHAR2 (2000 CHAR); BEGIN -- 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) END;
then loop through it and update client application.
DECLARE CURSOR MY_IRS IS (SELECT * FROM apex_application_page_ir where application_id = 100);
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.
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.
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
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...
Andre wrote:No, that's not correct. I have for example the following tables:
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?
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.
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.