10 Replies Latest reply on Oct 10, 2018 3:03 PM by ascheffer

    Validating Records in APEX Data Load Wizard

    Nivetha Ramnath

      Hi All,

       

      I have an upload screen where user can upload an excel, so that the uploaded records gets inserted into my table.

      Am using Page Type- Data Load Wizard.

      Data_Load_Page.PNG

      User will upload the excel file and the following report will be displayed,

      Data_Excel.PNG

      Here I need to validate few columns like Entity,Description cannot be null and default value for currency should be EUR?

      How can I create validations for this data on click of NEXT button. User cannot upload the data without these validations?

      Am using APEX 18.1.

      Please advise.

       

      Thanks & Regards,

      Nivetha Ramnath

        • 1. Re: Validating Records in APEX Data Load Wizard
          Dharmendra Kumar-Oracle

          Hi,

           

          Any reason for not having not null constraints and default value for such columns?

          Check Transformation Rules under "Data Load Definition".

           

          Thanks,

          Dharmendra

          • 2. Re: Validating Records in APEX Data Load Wizard
            Nivetha Ramnath

            Hi,

            I do have not null constraints and default values, but constraints error are thrown only in the final screen.

            I want to display the error message on click of next button in the first page so that in case of any errors user can modify the excel and re-upload.

            Thanks for the info regarding Transformation Rules, let me check it.

             

            Thanks & Regards,

            Nivetha Ramnath

             

            .

            • 3. Re: Validating Records in APEX Data Load Wizard
              Nivetha Ramnath

              Hi ,

              I tried creating transformation rule as you said above for settings values for updated_by and updated_date columns (NOT NULL columns in table).

               

              Transformation_Rules.PNG

              but these columns will not be there in the excel uploaded.

              When user uploads the excel file I want these two columns to be set as SYSTIMESTAMP and :APP_USER.

              Am getting the error "ORA-01400: cannot insert NULL into ("GOEQPLOL1SSW"."FINANCE_BANKING_POSITION"."UPDATED_AS_ON")".

               

              Thanks & Regards,

              Nivetha Ramnath

               

              • 4. Re: Validating Records in APEX Data Load Wizard
                Dharmendra Kumar-Oracle

                Hi Nivetha,

                 

                I am assuming these columns are to record audit details for the records. If so, these should be mapped in the insert/update DML trigger.

                 

                Thanks,

                Dharmendra

                • 5. Re: Validating Records in APEX Data Load Wizard
                  Mike Kutz

                  What you seek is a "Data Extract, Transform, and Load Wizard". (ETL)

                  It really should be "Data Extract, Transform, Validate, and Load Wizard" -- but, ETL is the industry standard term.

                   

                  APEX only supplies a simplified "Data Load Wizard".  Any requirement beyond a simple "load data into a table", this feature will fail you.

                   

                  I believe your requirement to validate data prior to loading is "OUT-OF-SCOPE" for this feature of APEX. (things might have changed since 4.2, but I doubt it.)

                   

                  Workaround:  DIY Data Load Wizard

                  (DIY === Do It Yourself)

                   

                  • Start with ORDS feature XLS2COLLECTION the plugin Excel2Collection on your first page.
                  • You should have a process the checks and verifies every row.
                    • I recommend breaking down each check into a separate procedure
                    • I suggest you use a VIEW on the collection for the SELECT portion. Use the APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE() to update one of the columns for your PASS/FAIL
                    • This code should be in a PACKAGE that is maintained by a Code Repository!!!!
                  • From there, you can use a simple IR on the VIEW (on the COLLECTION).
                    • This is your "Let's review your data before you actually load it" page.
                    • Make sure you highlight the PASS/FAIL -- possibly filter for FAIL only -- and save that as the Default Report
                  • Depending on your requirements, you could get away with a simple MERGE statement (which will run 1000x faster than the slow-by-slow processing that the "Data Load Wizard" uses)
                    • If you really want the nice "rows added", "row modified", "rows with error" type report, you'll need to do a similar slow-by-slow processing.
                    • Just -- don't commit after every row.  (IIRC - the APEX Data Load Wizard did this .... but that was v4.2)

                   

                  If you need help with the package design, I can point you towards a code generator that will get you 95% of the way there.

                   

                  My $0.02

                   

                  MK

                   

                  PS - Please read "with a grain of salt".  I loath the Data Load Wizard because it appears to be powerful but it isn't.

                  • 6. Re: Validating Records in APEX Data Load Wizard
                    Nivetha Ramnath

                    Hi Mike,

                    Am using Excel2Collections Plugin 0.906 in APEX 18.1

                    But Am getting this error "PLS-00201: identifier 'SYS.DBMS_UTILITY' must be declared" , so I commented out the DBMS_UTILITY in the plugin code and it works now.

                    I have used this plugin with same steps as you have mentioned above in APEX 5(except for the MERGE).

                    I should try it with simple MERGE statement.

                    Thank you so much for the info.

                     

                    Regards,

                    Nivetha Ramnath

                    • 7. Re: Validating Records in APEX Data Load Wizard
                      Mike Kutz

                      Nivetha Ramnath wrote:

                       

                      Hi Mike,

                      Am using Excel2Collections Plugin 0.906 in APEX 18.1

                      But Am getting this error "PLS-00201: identifier 'SYS.DBMS_UTILITY' must be declared" , so I commented out the DBMS_UTILITY in the plugin code and it works now.

                      I have used this plugin with same steps as you have mentioned above in APEX 5(except for the MERGE).

                      I should try it with simple MERGE statement.

                      Thank you so much for the info.

                       

                      Regards,

                      Nivetha Ramnath

                      Did you download the plugin from the link I gave you?

                       

                      The error you see is a database error.  What version/edition of the database you are using?

                       

                      Please post results of:

                      select * from v$version;
                      

                       

                      Also, I'm sure the author, Anton Scheffer ( ascheffer ), would be interested in your error.

                       

                      MK

                      • 8. Re: Validating Records in APEX Data Load Wizard
                        ascheffer

                        Mike Kutz I moved the latest release to github to please the people at apex.world, https://github.com/antonscheffer/excel2collections

                        And regarding the error, some DBAs see packages like dbms_utility, dbms_xmlgen or dbms_lob as a security risk and revoke the grant to public. The plugin uses some of these very basic packages.

                        That might cause errors, which can resolved by either the DBA granting the required packages to public/the parsing user of the APEX application or the user who wants to use the plugin by removing the offending code.

                        In this case dbms_utility is only used for error logging, so that should not be a problem (but of course I don't give any support anymore for the plugin in that case) Removing calls to dbms_lob would stop the plugin from doing anything useful.

                        • 9. Re: Validating Records in APEX Data Load Wizard
                          Mike Kutz

                          Thanks for the update.

                           

                          Should I point people to the apex.world version or straight to the GitHub version?

                          • 10. Re: Validating Records in APEX Data Load Wizard
                            ascheffer

                            apex.world  is THE place to look for APEX content , so that  seems more appropriate.