1 2 Previous Next 15 Replies Latest reply: Oct 31, 2012 5:11 PM by sky123 RSS

    wwv_flow_lists_of_values$

    sky123
      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
          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
            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
              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
                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
                  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
                    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
                      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-Oracle
                        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
                          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-Oracle
                            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
                              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
                                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
                                  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-Oracle
                                    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