Automatically Generating APEX Pages – 2

My method involved the following steps :

STEP 1

Create  an APEX report page as normal using the designer which would have all the features for all future report pages.  Here is an example report.

NB.  There are additional regions that are implemented on a global page but which also use the metadata tables.

 

 

 

 

 

STEP 2

Export the page and examine the calls to wwv_flow_api – match them to the components on the page.

STEP 3

Add tables and columns to my meta data model which will hold as many parameters as necessary for the calls to wwv_flow_api.  The diagram shows a very simplified schematic of my metadata and how it relates to either the generator, the actual report at runtime (or both).

STEP 4

Create a “generator”  package in pl/sql that will call the necessary wwv_flow_api procedures with my metadata providing the parameter values.  If I later I need to add any components on the page, I adjust my package code and rerun the generator (for one or more pages).  If I change the values in the metadata – I just rerun the generator for the page that is affected.

In the generator I start with querying the apex tables for the relevant ID values, for example, I need to get the ID values for :

  • templates (page, report, item) – these will be constant for the application
  • authorization schemes
  • components – next available IDs for  region, report, item, dynamic action etc.

I also construct various parameter values from my metadata tables – such as the report source, LOVs for the relevant options on each page, format masks and column lists.

The package then constructs and issues calls to the wwv_flow_api passing in the above ID values and other parameters.  In my generator I needed to use the following :

  • Delete page : wwv_flow_api.remove_page  (if re-generating an existing page)
  • Create page :  wwv_flow_api.create_page
  • Region : wwv_flow_api.create_page_plug
  • Report : wwv_flow_api.create_worksheet
  • Report columns : wwv_flow_api.create_worksheet_column.  My report page always uses a view as the basis of the report region so I can loop through the relevant rows in oracle data dictionary user_tab_columns to get the column information.
  • Saved IR reports :wwv_flow_api.create_worksheet_rpt
  • Saved IR reports : wwv_flow_api.create_worksheet_condition
  • Saved IR reports : wwv_flow_api.create_worksheet_group_by
  • Page items : wwv_flow_api.create_page_item
  • Dynamic Action Events : wwv_flow_api.create_page_da_event
  • Dynamic Actions : wwv_flow_api.create_page_da_action
  • Processes :wwv_flow_api.create_page_process
  • Buttons : wwv_flow_api.create_page_button

STEP 5

Create another APEX application to manage my metadata tables and to call the generator.

17 Comments

  • MARCUS AURELIUS MINERVINO JUNIOR

    Very Nice!

    Reply
  • Mark Stewart

    Great idea! I am faced with an issue where I want to do a mass update of several pages, to add a simple text only region; I would like to call

    apex_050100.wwv_flow_api.create_page_plug

    but the first parameter

    (p_id=>

    is likely generated from a sequence when Apex creates a page, and I’m afraid to guess at some value in case Oracle Apex tries to use that ID later…

    Any ideas? Sounds like your magic package may just reuse IDs from your template page; implying that the ID range just needs to be unique within a given Apex page. Am I right?

    Reply
  • admin

    Hi Mark

    A sequence isn’t used but the ID range must be unique for the object (region, item, report, page etc.) across all applications.

    In my generator I just found the largest value used already by querying max(id) from and incremented by 1.

    I created plenty of manual pages/regions/items etc. after this and there was no conflict.

    There’s always the caveat that this is not recommended by Oracle and I only used it in APEX versions from 4.2 to 18.

    Kathryn

    Reply
    • Mark Stewart

      Perfect! I did wrap the p_id=> parameter in a

      apex_050100.wwv_flow_api.id(12782643491504869916)

      call, and looks like that call, when invoked that way, gets Oracle’s version of the next usable number, because I added 3,000 to the max id for that item, and Oracle used a lower number, but higher than the prior max. If I invoke that wwv_flow_api.id function by itself, it just returns the parameter provided; so it must be context sensitive.

      Thanks for your insight.

      Reply
  • Stefaan

    Can you provide us the export of this application or the export of your package you us?

    Reply
    • admin

      Sorry, the implementation I did was for a company where I no longer work so unfortunately I don’t have access to the code (nor ownership of it).

      This post is just to give you some ideas that you can work up for your specific circumstances.

      Kathryn

      Reply
      • Stefaan

        Thanks for your answer, I already had a lot from you blog.
        But can you please help me out a bit how to setup the package? (for example create region), if I would have this then I can add all the rest like page items, reports, …

    • Stefaan

      Thanks for your answer, I already had a lot from you blog.
      But can you please help me out a bit how to setup the package? (for example create region), if I would have this then I can add all the rest like page items, reports, …

      Reply
      • admin

        At its simplest, you need to create a page first and then a region.
        You will also need to get IDs to use for page components (e.g. authorization scheme, templates) so you can pass those to the wwv_flow_api procedures.

        You can put the relevant steps below into a stored procedure or a packaged procedure or run from a pl/sql block.

        — query the APEX data dictionary to get ID values for
        – interface
        – next page ID to use
        – page template
        – authorization scheme
        – region id to use

        — use your own values ( preferably from metadata) for
        – page name
        – region name
        – report source
        – region icon
        etc.

        All these depend on the type of region you are going to create.

        create page
        create region (s)

        create report ( for report region you need region and report )
        create buttons
        create items
        create dynamic actions
        ( see post for information)

        To create a page, use (for example)
        wwv_flow_api.create_page(
        p_id =>
        ,p_flow_id => p_app_id
        ,p_user_interface_id => wwv_flow_api.id ( )
        ,p_name => p_page_name
        ,p_alias => p_page_alias
        ,p_page_mode => ‘NORMAL’
        ,p_step_title => p_page_name
        ,p_step_sub_title => p_page_name
        … etc
        )

        To create a region, use (for example this is for an interactive report region)
        wwv_flow_api.create_page_plug(
        p_id =>wwv_flow_api.id( )
        ,p_flow_id => p_app_id
        ,p_page_id =>
        ,p_plug_name => region_name_to_use
        ,p_icon_css_classes =>
        ,p_region_template_options =>
        ,p_plug_template =>
        ,p_plug_display_sequence =>
        ,p_include_in_reg_disp_sel_yn =>’N’
        ,p_plug_display_point =>’BODY’
        ,p_plug_source =>
        ,P_AJAX_ITEMS_TO_SUBMIT =>
        ,p_plug_source_type =>’NATIVE_IR’
        ,p_plug_query_row_template =>1
        ,p_plug_query_options =>’DERIVED_REPORT_COLUMNS’
        .. etc
        )

        Where I have used <...> above is where I would calculate or generate those values by querying the APEX dictionary.
        My p_values are extracted from my metadata table.

        This is just a quick summary.
        If you examine the file exported from one of your existing pages you can get the structure of the pl/sql that you need and the relevant values for the parameters to apply.

        Kathryn

  • Stefaan Vanderkerken

    Hello Kathryn,

    I’m creating the package now, but in your reply you write ‘Where I have used above is where I would calculate or generate those values by querying the APEX dictionary.’ but in the text above I don’t see the

    * p_id = I guess this is a unique number, so if I query the previous id +1, I get a unique ID
    * p_flow_id = ?? (I don’t find this in my exports…)
    * p_page_id = I found this only on create_menu_option but not in the create_page_plug
    * p_plug_template = I don’t know how to get this ID, I thought this would be unique on the type (standard, card,…) but I get different id’s on the same type, so could you explain what this ID is?, is also with create_page_button, create_page_item that I don’t understand how to get this ID.

    Thanks for all the help, much appreciated!

    Reply
    • admin

      Stefan
      Flow id is the application id.
      Page id is the page number.
      Template id is the ideal of the relevant template for the component ( page, region, button etc.)
      If you are creating a new object you need to give it a unique id. That object might refer to a containing object ( so a region will need a page id for example) or the id of a component that it uses ( a button needs a template for example).

      As you say the blog has removed my variables but no matter, the blanks just show where you need to get values from the dictionary or from your meta data tables.
      Kathryn

      Reply
    • admin

      Stefan
      The plug is the region.
      So the p_plug_template is for the region. This is not the same as a report.

      Please make sure you backup your database before experimenting with the wwv_flow_api package. It’s very powerful and you have the potential to break your APEX if you’re not sure how it works.

      Reply
      • Stefaan Vanderkerken

        I know it’s quite tricky to use the wwv_flow_api package. This is why I asked you if you have the export of the package.
        I try to understand everything and want to learn as much possible in apex. But you don’t find much info about the wwv_flow_api. A export of your package would help me more to understand everything. I’m trying to write it now myself and hope everything is correct what I’m doing.

        Greetings
        Stefaan

      • admin

        Hi Stefaan

        Unfortunately, I don’t still have access to my package that I wrote about in the post (as I explained). It was developed over a period of time and was very specific for an application with large numbers of similar pages. My blog post was to share the idea behind it.

        If you create an apex page and then export it you will have a sql file. You can then build a pl/sql procedure that will replicate what is in ths file file. This will then be specific to your own application. Once you have this skeleton you can start to use replace the hardcoded values with metadata so that the procedure can be used to generate different pages based on information you have in a metadata table (or tables).

        If you want to add a new component to a page ( for example a button ), the easiest way to understand the wwv_flow_api is to create a simple page manually in APEX which includes your new component, export the page and then investigate the export file to find the code that creates this button and add that to your procedure.

        When you must pass an ID into one of the arguments you will need to know where to find the ID – for this you should get to know the APEX dictionary views, for example you can get the template ID you need from apex_application_templates :

        select template_id
        into my_button_template_id
        from apex_application_templates
        where application_id = &my_app_id
        and template_type = ‘Button’
        and template_name = ‘Text with Icon Report’; — you can save this template name in your metadata table so your query can be dynamic

        You will probably need to get several values before you have what you need for the call to create the button.

        Then in your procedure the call to wwv_flow_api.create_page_button you can use these values. For example:
        wwv_flow_api.create_page_button(
        p_id => wwv_flow_api.id(x) — where x is the the max id +1
        ,p_flow_id => p_app_id — application_id
        ,p_flow_step_id => this_page_id — page_id used when page created in previous step
        ,p_button_sequence => 20 — for example, or this can be held in metadata table
        ,p_button_plug_id => this_region_id — region_id for the button – used when region created in previous step
        ,p_button_name => ‘SHOW_PAGE_X’ — for example, or this can be held in metadata table
        ,p_button_action => ‘REDIRECT_PAGE’ — for example, or this can be held in metadata table
        ,p_button_template_options =>’#DEFAULT#:t-Button–primary’ — for example, or this can be held in metadata table
        ,p_button_template_id =>wwv_flow_api.id( my_button_template_id ) — from previous query above
        ,p_button_is_hot =>’Y’ — for example, or this can be held in metadata table
        ,p_button_image_alt =>’Show Blah’ — for example, or this can be held in metadata table
        ,p_button_position =>’BODY’ — for example, or this can be held in metadata table
        ,p_button_redirect_url => target_URL — for button to redirect to ,this can be held in metadata table
        ,p_grid_new_row =>’N’ — for example, or this can be held in metadata table
        ,p_grid_new_column =>’Y’ — for example, or this can be held in metadata table
        );

        There will be many such sections in the page export file so it is best to start small and you will be able to understand the sql more easily. Start with creating a blank page, then add a static region, then add items or buttons etc. Each time build up your procedure (or package) until it has everything you need.

        wwv_flow_api is an undocumented package – I don’t think that Oracle will recommend using it (hence my warning) although I think there is a lot of interest in an api being provided that will allow the same functionality – maybe in a future release?

        Kathryn

  • Stefaan Vanderkerken

    looks like this blog remove the ‘less then, greater then’ characters

    Reply
  • Abdul Rehman

    How can we update the page item authorization from this api?

    Reply
    • admin

      I have only shown how to build a page from scratch using the api in the same way that export/import would create the page and its components.
      Remember that using the wwv_flow api is NOT SUPPORTED by Oracle. You would have to backup and test to make sure that any dependencies are not broken.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *