Skip to Main Content
  • Questions
  • load date into apex 20.1 with defaults for some columns


Question and Answer

Connor McDonald

Thanks for the question, Loai.

Asked: October 13, 2020 - 4:36 pm UTC

Last updated: October 08, 2021 - 1:19 am UTC

Version: 20.1

Viewed 1000+ times

You Asked

I want to insert data from excel or csv file (Id, Name, Address, Amount) into database table by using data load but during the insert of this data i want to decide this data is related to HSBC Bank (Because i have many bank and each bank have thousand of customer). so how i can insert the name of HSBC bank to the table during the load of data (Note that the excel or csv file did not contain the name of HSBC bank).

and Connor said...

Yes, you just need a default on the column.

For example, I did this

1) table with default

create table asktom.t ( pk int generated as identity primary key, x int, y int default 0 );

2) Create an APEX app with a standard data loader page. I then ran it with the following data


(ie, only 1 column worth of data). I get prompted to choose which column to load that into (I chose X).

3) The data then loads and I look at the table

SQL> select * from asktom.t;

        PK          X          Y
---------- ---------- ----------
         1          1          0
         2          2          0
         3          3          0
         4          4          0
         5          5          0
         6          6          0
         7          7          0
         8          8          0
         9          9          0
        10         10          0

10 rows selected.

You can see the Y picks up its default without incident.


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment


A reader, December 10, 2020 - 10:46 am UTC

Can the default be set by the app and not at the DB level?

Sky McKinley, October 07, 2021 - 6:09 pm UTC

For example, if the value should be the value of the APP_USER system variable? That can't be done at the DB level, so can it be done during the data load process?
Connor McDonald
October 08, 2021 - 1:19 am UTC

As long the db can see it, eg you could put it in a context variable, then yes.

APEX populates a lot of its metadata into the standard USERENV context

More to Explore


Keep your APEX skills fresh by attending their regular Office Hours sessions.