10 Replies Latest reply: Jun 21, 2012 8:51 PM by Ashish_Apps RSS

    SQL exception occurred during PL/SQL upload  (Web ADI)

    bharat-oracle
      Hi,

      I am having issue loading data using Web ADI, I am getting "SQL exception occurred during PL/SQL upload" error, I tried to restart Apache, also looked into the BNE.log file for the exact error but I am still not sure about this exception, can anyone please help?

      bne:text="SQL exception occurred during PL/SQL upload."
      bne:cause="Database insert error"

      RDBMS: 11.2.0.3.0
      Oracle Applications: 12.0.6

      Thanks,
      Bharat
        • 1. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
          Hussein Sawwan-Oracle
          I am having issue loading data using Web ADI, I am getting "SQL exception occurred during PL/SQL upload" error, I tried to restart Apache, also looked into the BNE.log file for the exact error but I am still not sure about this exception, can anyone please help?
          Please rename the log file, reproduce the issue and check the log file then.
          bne:text="SQL exception occurred during PL/SQL upload."
          bne:cause="Database insert error"
          Please see these docs.

          R12 Uploading Intercompany Transactions Shows SQL Exception Occurred During PL/SQL Upload [ID 1234063.1]
          Batch Element Entry (BEE) Spreadsheet Interface > 10 Rows Fails with Error: 'SQL exception occured during PL/SQL Upload.' [ID 388012.1]
          How Do You Setup An AGIS Transaction That Has Several Transaction Lines For The Same Transaction [ID 946499.1]
          FCH: Error: "The upload process has completed with errors. Please Close to return to the document and fix the errors. - No rows uploaded - <999> rows were invalid" During WebADI Data Upload [ID 553025.1]
          R12: Legal Entity Name must be < 31 characters. [ID 472505.1]
          Oracle Payroll 'Batch Element Entry ( BEE )' Frequently Asked Questions ( FAQ ) [ID 1353021.1]

          Thanks,
          Hussein
          • 2. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
            bharat-oracle
            Thanks for the reply Hussein, I got that fixed, the issue was with my package.
            • 3. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
              bharat-oracle
              Hi,

              I have requirement where I need to download the excel data directly into WEB ADI template, could you please let me know the approach? I am able to do the import data, but I currently need a way to export the OAF page data into the Web ADI template that I created for import (created using HR Integrator Setup-->Create Document-->Define Layout). I would really appreciate your help.

              Thank you,
              Bharat
              • 4. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                Ashish_Apps
                Does this menas that your OAf is basically a GUI . Data collector where from user will insert the data and then you want to upload the same using API/PL-SQL.

                If so then it means that only one record will be uoploadied each time. On the click of submit Button.

                But unfortunately there is no direct way of loading data from OAF to ADI.

                You need to get the data into EXCEL in ADI format and then upload the EXCEL as is.

                I personally feel that for your requirement you basically need a custom table which will collect the data from OAF and on submit_request you should do two things

                a) insert into custom table and commit the data.
                b) submit your pl/sql procedure which will take the data from custom table and load into base table using API.
                • 5. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                  bharat-oracle
                  Thanks for your reply, as per our requirement, the user needs to export the data from the OAF page using the export functionality into the excel sheet, then they might do some tweaks to the data and upload the same data into custom table using Web ADI template, my question was if we can export the data directly into the template (Web ADI) so that we can use the same template to upload the data into the table, else we might need to take the data from the excel sheet which was generated by the export button and then copy paste the same into Web ADI template, we just wanted to make it a one step process, please let me know if you need any further details.
                  • 6. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                    Ashish_Apps
                    So what I can understand is that :

                    a) You have a custom table
                    b) You have developed a OAF page which exports data from the custom table into EXCEL which is exactly the same layout as that of ADI
                    c) user makes some changes to the data
                    d) and uploads the Excel using WEBADi.

                    If my above understanding is correct :

                    Create an UPDATE Style Integrator which will download data into ADI make the change and upload from tehre only no need of OAF Page.

                    You just need to create a view for ADI Download( with parameters if any) and then upload the same.

                    In case you want to get more details you may refer the belwo link It has a very good example:
                    http://www.slideshare.net/AshishHarbhajanka/webadi-asampleimplementation
                    OR
                    http://oracleapps-atechniciansview.blogspot.com.es/2011/05/webadi-sample-implementation.html
                    • 7. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                      bharat-oracle
                      a) You have a custom table --> YES
                      b) You have developed a OAF page which exports data from the custom table into EXCEL which is exactly the same layout as that of ADI -->

                      Here instead of getting the data into the excel can we directly get the data into the ADI that we created?


                      c) user makes some changes to the data -->YES
                      d) and uploads the Excel using WEBADi. --> YES
                      • 8. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                        Ashish_Apps
                        When you export the data doesnt it come sin Excel.

                        You need to copy the EXCEL Content and paste in ADI Layout and upload.

                        or else follow the approach i said.

                        Creating a UPDATE(Matadata) Styled ADI
                        • 9. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                          bharat-oracle
                          Thanks I have tried you approach and its working as expected, could you please let me know if we can add a where clause to restrict view using WEBADI?
                          • 10. Re: SQL exception occurred during PL/SQL upload  (Web ADI)
                            Ashish_Apps
                            Yes you can, Please use MAintain Standalone Integrator for same. Here you will e able to add extra ehere caluse something like:

                            where last_name = $PARAM$.last_name
                            and business_group_id = $PARAM$.business_group_id
                            ..................
                            ...................
                            ..................


                            You can add a max of 5 parameters and attach value set too.

                            For attaching value set use below api

                            bne_param_defns_pkg.insert_row

                            Also you can change the property of your integrator columns like (Required, Default, Displayed, Read Only, Change Prompts) etc by using an integrator name
                            WEB ADI : Interface Columns Integrator.

                            By default this is not visible in menu , but you may enable it using following script :

                            BEGIN
                            UPDATE BNE.BNE_INTEGRATORS_B
                            SET ENABLED_FLAG = 'Y'
                            WHERE INTEGRATOR_CODE = 'INTERFACE_COLS';

                            COMMIT;
                            END;

                            Edited by: 936671 on Jun 21, 2012 6:51 PM