4 Replies Latest reply on Jun 19, 2019 6:47 AM by Deepika Nagendran-Oracle

    Data Upload wizard update in oracle apex

    Deepika Nagendran-Oracle

      Hi all,

          Is there any way to skip a particular column from getting updated through upload wizard?

        • 1. Re: Data Upload wizard update in oracle apex
          Carsten Czarski-Oracle

          Hi Deepika,

           

          the end user could choose "Do not load" in the column mapping dialog. As an alternative ( if you want to prevent updating the column under all circumstances), you could create a view on your table. This view simply selects all columns from the table, except the one you don’t want to have updated. Then create the data loading wizard using the view instead of the table. Then the column won’t be visible at all for data loading.

           

          I hope this helps

           

           

          Carsten

          • 2. Re: Data Upload wizard update in oracle apex
            Deepika Nagendran-Oracle

            Hi Carsten,

             

            Thanks for the reply. Sorry I missed a point. I want to insert in case of new row and not update if the value is already there.

             

            Deepika

            • 3. Re: Data Upload wizard update in oracle apex
              Carsten Czarski-Oracle

              Hi Deepika,

               

              the Data Loading wizard always updates the row if it already exists. I would see these options for you:

              • Create a view on top of your table (simply selecting the columns for data loading) and add in INSTEAD OF trigger for that view as follows:
              create or replace view tr_io_viewname 
              instead of insert or update on {view}
              for each row
              begin
                  if inserting then
                      insert into {table} ({col1},{col2}, .... values (:new.{col1}, :new.{col2};
                  elsif updating then
                      null;
                  end if;
              end;
              
              • Load the data into another (staging) table first - then add a button to the final data loading page. On click of the data loading button, execute some PL/SQL code, which loads the data from the staging table into your final table, omitting all existing rows.
              • If you are on APEX 19.1, you might consider using the new APEX_DATA_PARSER.PARSE table function, which allows to parse CSV, XLSX, JSON or XML files and which returns parsed data as a table. Using your own PL/SQL code, you can then process the data however you want. The "Sample Data Loading" packaged application contains a few examples.

               

              I hope this helps

               

              -Carsten

              • 4. Re: Data Upload wizard update in oracle apex
                Deepika Nagendran-Oracle

                Hi Carsten,

                 

                Thank you for the help.

                 

                I will work on trigger. I was hoping there should be some way in the utility tool itself

                 

                Deepika