This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Oct 31, 2012 3:11 PM by sky123 RSS

wwv_flow_lists_of_values$

sky123 Newbie
Currently Being Moderated
May I know what the wwv_flow_lists_of_values$ table in an APEX_040X00 stores? When the entries are created and updated by APEX?

Thanks.
  • 1. Re: wwv_flow_lists_of_values$
    VC Guru
    Currently Being Moderated
    It is obvious that those are Application List Of Values defined under
    Application Builder > Application X > Shared Components > Lists of Values
  • 2. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Hi,

    It appeared that the following tables were not automatically cleaned up after some applications (particularly the sample applications with flow_id 8950 and 8951) had been dropped from the workspaces. This caused the upgrade from APEX_040100 to APEX_040200 to fail when it tried to install the sample application with exactly the same ID 8950. How are these tables managed by APEX and why they were not automatically deleted?

    1. wwv_flows
    2. wwv_flow_lists_of_values$
    3. wwv_flow_list_of_values_data

    Thanks.
  • 3. Re: wwv_flow_lists_of_values$
    VC Guru
    Currently Being Moderated
    What error you are getting?

    It will be helpful for us if you post the exact error details.
  • 4. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Hi,

    The upgrade stopped at exactly the following (2 times already), which tried to run the "sample_app.sql" under the following path:
    ?:\apex\core\packaged_apps\sample_app.sql

    There were some obsolete entries of the sample application with exactly the same ID = 8950 in the following tables:
    1. APEX_040100.wwv_flows
    2. APEX_040100.wwv_flow_lists_of_values$
    3. APEX_040100.wwv_flow_list_of_values_data

    Therefore, after the APEX_040100 schema was copied into APEX_040200 and when it tried to insert those LOV again, it violated the constraint. The sample applications - as well as some others unused applications - were already dropped from the APEX workspace in APEX_040100. I don't know why these obsolete entries still exist in these tables. Should I simply delete them before the upgrade? Why weren't they automatically removed by APEX when the applications were dropped?

    Thanks.

    <pre>
    ...Product Portal Sample Database Application installation data: Application ID 8950

    APPLICATION 8950 - Sample Database Application
    Set Credentials...
    Check Compatibility...
    API Last Extended:20120101
    Your Current Version:20120101
    This import is compatible with version: 20120101
    COMPATIBLE (You should be able to run this import without issues.)
    Set Application ID...
    ...ui types
    ...user interfaces
    ...plug-in settings
    ...authorization schemes
    ...navigation bar entries
    ...application processes
    ...application items
    ...application level computations
    ...Application Tabs
    ...Application Parent Tabs
    ...Shared Lists of values
    Error creating list of values id="6952856676665672023" ORA-00001: unique
    constraint (APEX_040200.WWV_FLOW_LOV_UNIQUE2) violated
    begin
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (APEX_040200.WWV_FLOW_LOV_DATA_FK) violated -
    parent key not found
    ORA-06512: at "APEX_040200.WWV_FLOW_API", line 4511
    ORA-06512: at line 3
    </pre>

    Edited by: sky123 on Oct 29, 2012 4:47 PM
  • 5. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    There're also some entries in the "wwv_flow_list_of_values_data" table. The following table contained some applications, including 2 sample applications, that were already dropped from the APEX workspace. The flow_id "8950" belongs to the sample portal application.

    Thanks.

    SQL> edit
    Wrote file afiedt.buf

    1 select flow_id, count(*) total
    2 from apex_040100.wwv_flow_lists_of_values$
    3 group by flow_id
    4* order by 1
    5 /

    FLOW_ID TOTAL
    ---------- ----------
    101 5
    102 33
    103 15
    104 27
    105 3
    106 2
    109 3
    111 30
    116 23
    120 4
    125 19
    126 4
    127 2
    128 1
    4000 826
    4050 92
    4300 20
    4350 43
    4400 79
    4500 147
    4550 3
    4600 132
    4700 2
    4800 81
    4850 17
    4900 116
    8950 7

    27 rows selected.

    SQL>
  • 6. Re: wwv_flow_lists_of_values$
    VC Guru
    Currently Being Moderated
    sky123 wrote:
    Hi,

    The upgrade stopped at exactly the following (2 times already), which tried to run the "sample_app.sql" under the following path:
    ?:\apex\core\packaged_apps\sample_app.sql

    There were some obsolete entries of the sample application with exactly the same ID = 8950 in the following tables:
    1. APEX_040100.wwv_flows
    2. APEX_040100.wwv_flow_lists_of_values$
    3. APEX_040100.wwv_flow_list_of_values_data

    Therefore, after the APEX_040100 schema was copied into APEX_040200 and when it tried to insert those LOV again, it violated the constraint. The sample applications - as well as some others unused applications - were already dropped from the APEX workspace in APEX_040100. I don't know why these obsolete entries still exist in these tables. Should I simply delete them before the upgrade? Why weren't they automatically removed by APEX when the applications were dropped?
    What do you mean by APEX_040100 schema was copied into APEX_040200 ??

    Are you not running apexins.sql for upgrade?
  • 7. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Hi,

    Yes, I ran the apexins.sql. After it created the APEX_040200 during the upgrade, it copied the values in tables from APEX_040100 into APEX_040200.

    Thanks.
  • 8. Re: wwv_flow_lists_of_values$
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi Andy,

    can you check the status of the foreign key constraints wwv_flow_lov_fk on wwv_flow_lists_of_values$
        flow_id                   number not null
                                  constraint wwv_flow_lov_fk
                                  references wwv_flows
                                  on delete cascade,
    and wwv_flow_lov_data_fk on wwv_flow_list_of_values_data
        lov_id                    number
                                  constraint wwv_flow_lov_data_fk
                                  references wwv_flow_lists_of_values$
                                  on delete cascade,
    in your APEX_040100 schema. Are those constraints enabled and validated? I'm wondering how you could have a LOV for a non existing application if there is a "delete cascade" FK which automatically removes the LOV if an application is deleted. Those FK exist at least for 6 years.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 9. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Patrick,

    Both foreign key constraints were enabled.

    wwv_flow_lov_fk on wwv_flow_lists_of_values$
    wwv_flow_lov_data_fk on wwv_flow_list_of_values_data

    However, I think the problem was that the entries have not ever been deleted from the WWV_FLOWS table and, therefore, no DELETE CASCADE action has happened at all. Those obsolete entries are still in the APEX_040100.WWV_FLOWS table.

    Should I simply delete all the LOV entries (in all 3 tables) for those applications that were already dropped from the workspace - before I do the upgrade from APEX 4.1 to APEX 4.2? If so, among the applications provided by Oracle, which ones can I safely delete? Do I need to delete any application of ID between 4000 and 4900?

    Thanks.

    SQL> edit
    Wrote file afiedt.buf

    1 select id, name
    2 from apex_040100.wwv_flows
    3* order by id
    SQL> /

    ID NAME
    ---------- --------------------------------------------------------
    101 VR Data Store
    102 VR Order Management System
    103 FD Registry
    104 FD Registry_lower resolution
    105 User Management
    106 OL Viewer
    109 Contractor Management System
    111 Training Online
    113 Upload Files
    114 WORKSPACE_THEME
    115 WORKSPACE_THEME
    116 Invoices Online
    120 Online Training Materials Service
    123 WORKSPACE_THEME
    124 Email Form
    125 Quality and Assurance
    126 User Management
    127 Request For Proposals
    128 RFP Extranet Pages
    4000 Oracle APEX AppBuilder
    4050 Oracle APEX Internal Administration
    4155 Scheme Authentication Login
    4300 Oracle APEX Data Workshop
    4350 Oracle APEX Workspace Administration
    4400 Oracle APEX Application Migration
    4411 Oracle APEX System Messages and Native Types
    4500 Oracle APEX SQL Workshop
    4550 Oracle APEX Login
    4600 Oracle Application Express Learn More
    4700 Oracle APEX New Service Signup
    4800 Oracle APEX Team Development
    4850 RESTful Services
    4900 Oracle APEX Websheets
    8950 Sample Database Application
    8951 Collection Showcase

    35 rows selected.

    SQL>
  • 10. Re: wwv_flow_lists_of_values$
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,

    if an application still exists in wwv_flows then it isn't obsolete and exists in a workspace. Can you please also select the column SECURITY_GROUP_ID from wwv_flows. You can also query the view APEX_APPLICATIONS which will show you the workspace name of those applications.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
  • 11. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Patrick,

    Please please help me on this since I cannot find the answer anywhere. Below was the result when I queried the "apex_040100.apex_applications" view. I logged on to "apex_admin" but didn't see any workspace named "COM.ORACLE.APEX.REPOSITORY" at all. There were only 3 workspaces: INTERNAL, VRDS, and BOCC.

    Also, as I pointed out earlier, the "apex_040100.wwv_flows" table also contained 3 applications with ID 114, 115, and 123, which no longer existed in any of these workspaces. The value appeared in the "OWNER" column was "NOBODY".

    The "SECURITY_GROUP_ID" of the obsoleted applications with ID 114, 115, and 123 is "11". The "SECURITY_GROUP_ID" of the Oracle-provided applications 8950 and 8951 is also "11".

    Thanks.

    <pre>
    SQL> edit
    Wrote file afiedt.buf

    1 select application_id, substr(owner,1,15) owner, workspace
    2 from apex_040100.apex_applications
    3* order by 1
    SQL> /

    APPLICATION_ID OWNER WORKSPACE
    -------------- --------------------------------------------- ---------------------------------------
    101 VRDS_MGR VRDS
    102 VRDS_MGR VRDS
    103 MCAH_MGR VRDS
    104 MCAH_MGR VRDS
    105 VRDS_MGR VRDS
    106 VRDS_MGR VRDS
    109 BOCC BOCC
    111 HETC_MGR VRDS
    113 HETC_MGR VRDS
    116 INVOICE_MGR VRDS
    120 OSH_MGR VRDS
    124 MISC_MGR VRDS
    125 CHDP_MGR VRDS
    126 DPHP_MGR VRDS
    127 RFP_MGR VRDS
    128 RFP_MGR VRDS
    4000 APEX_040100 INTERNAL
    4050 APEX_040100 INTERNAL
    4155 APEX_040100 INTERNAL
    4300 APEX_040100 INTERNAL
    4350 APEX_040100 INTERNAL
    4400 APEX_040100 INTERNAL
    4411 APEX_040100 INTERNAL
    4500 APEX_040100 INTERNAL
    4550 APEX_040100 INTERNAL
    4600 APEX_040100 INTERNAL
    4700 APEX_040100 INTERNAL
    4800 APEX_040100 INTERNAL
    4850 APEX_040100 INTERNAL
    4900 APEX_040100 INTERNAL
    8950 APEX_040100 COM.ORACLE.APEX.REPOSITORY
    8951 APEX_040100 COM.ORACLE.APEX.REPOSITORY

    32 rows selected.

    SQL>
    </pre>
  • 12. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    Patrick,

    Here is some more information. I found this report under "Home > Manage Workspaces >Parsing Schemas" in APEX ADMIN site.

    Thanks.

    Application Parsing Schema Workspace
    101     VRDS_MGR     VRDS
    102     VRDS_MGR     VRDS
    103     MCAH_MGR     VRDS
    104     MCAH_MGR     VRDS
    105     VRDS_MGR     VRDS
    106     VRDS_MGR     VRDS
    109     BOCC     BOCC
    111     HETC_MGR     VRDS
    113     HETC_MGR     VRDS
    *114     NOBODY     COM.ORACLE.APEX.REPOSITORY*
    *115     NOBODY     COM.ORACLE.APEX.REPOSITORY*
    116     INVOICE_MGR     VRDS
    120     OSH_MGR     VRDS
    *123     NOBODY     COM.ORACLE.APEX.REPOSITORY*
    124     MISC_MGR     VRDS
    125     CHDP_MGR     VRDS
    126     DPHP_MGR     VRDS
    127     RFP_MGR     VRDS
    128     RFP_MGR     VRDS
    *8950     APEX_040100     COM.ORACLE.APEX.REPOSITORY*
    *8951     APEX_040100     COM.ORACLE.APEX.REPOSITORY*
  • 13. Re: wwv_flow_lists_of_values$
    sky123 Newbie
    Currently Being Moderated
    I have cleaned the APEX import/export repository and have dropped the 2 applications 8950 and 8951 in Oracle SQL Developer. Now they don't appear in the report under APEX admin. Is that all I have to take care of before running upgrade to APEX 4.2 again?

    Thanks.

    <pre>
    Application
    Parsing Schema
    Workspace
    101     VRDS_MGR     VRDS
    102     VRDS_MGR     VRDS
    103     MCAH_MGR     VRDS
    104     MCAH_MGR     VRDS
    105     VRDS_MGR     VRDS
    106     VRDS_MGR     VRDS
    109     BOCC     BOCC
    111     HETC_MGR     VRDS
    113     HETC_MGR     VRDS
    116     INVOICE_MGR     VRDS
    120     OSH_MGR     VRDS
    124     MISC_MGR     VRDS
    125     CHDP_MGR     VRDS
    126     DPHP_MGR     VRDS
    127     RFP_MGR     VRDS
    128     RFP_MGR     VRDS
    </pre>
  • 14. Re: wwv_flow_lists_of_values$
    Patrick Wolf Employee ACE
    Currently Being Moderated
    Hi,

    if those applications are in workspace 11 then they are template applications which your Instance Admin has uploaded.

    Login into your instance and go to "Manage Instance -> Manage Shared Components -> Template Applications", pick the template app and delete it on the detail page.

    Regards
    Patrick
    -----------
    My Blog: http://www.inside-oracle-apex.com
    APEX Plug-Ins: http://apex.oracle.com/plugins
    Twitter: http://www.twitter.com/patrickwolf
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points