Batch Loading CSV to Oracle Database, Again, via REST APIs

Do you know what’s sad? An empty table, that’s really, really sad. So in this blog post, I’ll demonstrate batch loading CSV to Oracle…using ORDS and REST APIs for AUTOREST enabled TABLEs.

Create your Database

If you already have a database with ORDS, you can skip to the next section. But, I’m going to give you everything you need to get started from ZERO.

Environment: Always Free Autonomous Transaction Processing (ATP) Cloud Oracle Database

This will only take about 5 minutes if you don’t already have one. And I know you folks love the YouTubes. So here’s a new channel from someone you may recognize, our former and future intern, Layla!

It’s less than a 4 minute video, go watch, like, and subscribe!

Now, after you’ve created your database, you’re going to want a fresh, new application schema. DO NOT USE ADMIN.

Create a new USER

Users are basically synonymous with schemas in Oracle. Think of a schema as the collection objects owned by a USER. We’re going to create an EMPLOYEES table in a new schema, called HR.

Logged in as the ADMIN (or another DBA) user, we’re going to go to the Administration and Users section of SQL Developer Web.

Click on THIS box.

You can see your existing users, edit them (reset passwords!), and create new ones.

Ok, now this next part is VERY important. We’re going to create a new user, and we want to make sure they can do the stuff we want them to be able to do, but nothing more. This is known as a minimal required privileges philosophy.

DO NOT FORGET TO GIVE YOUR NEW USER QUOTA ON THE DATA TABLESPACE.

Use a strong password, obviously.

You need the ‘Web Access’ so you can REST Enable the table. The EMPLOYEES table REST APIs will actually get executed using the HR user in the database.

Upper right corner, assign some QUOTA on the tablespace, which in Autonomous will almost ALWAYS be ‘DATA’. Our employees table only has like 100 rows in it, so ’25M’ is more than enough.

And that’s basically it, click ‘Create User.’

Now you could grant MORE roles, but we have ‘CONNECT’ and ‘RESOURCE’ selected out-of-the box.

Once the user has been created, you can now login as that user. Do that now.

On the USERS page, you’ll see your new user, and there’s a handy link you can use to get directly to their login page.

Creating our TABLE, REST Enable it

I’m going to test my account, make sure I can actually create objects AND store data in my schema.

Ok, onto our actual table, EMPLOYEES.

TABLE: EMPLOYEES (sans foreign keys and triggers)

CREATE TABLE EMPLOYEES(
    EMPLOYEE_ID NUMBER(6,0), 
    FIRST_NAME VARCHAR2(20) , 
    LAST_NAME VARCHAR2(25)  CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE, 
    EMAIL VARCHAR2(25)  CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE, 
    PHONE_NUMBER VARCHAR2(20) , 
    HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE, 
    JOB_ID VARCHAR2(10)  CONSTRAINT EMP_JOB_NN NOT NULL ENABLE, 
    SALARY NUMBER(8,2), 
    COMMISSION_PCT NUMBER(2,2), 
    MANAGER_ID NUMBER(6,0), 
    DEPARTMENT_ID NUMBER(4,0)
);

You can copy/paste this text into your SQL worksheet, and hit ctrl+enter to execute the code immediately.

Refresh your table list –

My table is there, and I can REST Enable it!

Signed in as the object owner (HR), I can simply right-click and REST Enable my table. Clicking ‘Ok’ would run this code (ORDS is our PL/SQL API for ORDS):

BEGIN
    ORDS.ENABLE_OBJECT(
        P_ENABLED      => TRUE,
        P_SCHEMA      => 'HR',
        P_OBJECT      => 'EMPLOYEES',
        P_OBJECT_TYPE      => 'TABLE',
        P_OBJECT_ALIAS      => 'employees',
        P_AUTO_REST_AUTH      => TRUE
        );
    COMMIT;
END;

Note that I’m choosing NOT to alias the EMPLOYEES table on the URI. I however DO recommend you do just that. So switch P_OBJECT_ALIAS to something like ‘not_employees.’

On the REST Enable dialog, we also have the option to require AUTH. We want to ALWAYS say ‘Yes!’

Any request will need to be Authorized via this role or privilege.

Click ‘Enable!’

If that worked (it will!), you’ll see the tables list refresh with little connection plug next to it.

Right-click on the table, again.

We try to make this easy for you, want ‘BATCH LOAD’ and you can pick your environment.

So, we’re good to go, we can load our table now, right?

ALMOST.

Our data (CSV)

We’re going to need some data, so you will you. I’ll share 🙂

"EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY","COMMISSION_PCT","MANAGER_ID","DEPARTMENT_ID"
100,"Steven","King","SKING","515.123.4567",18-06-1987,"AD_PRES",240000,,,90
101,"Neena","Kochhar","NKOCHHAR","515.123.4568",22-09-1989,"AD_VP",17000,,100,90
102,"Lex","De Haan","LDEHAAN","515.123.4569",14-01-1993,"AD_VP",17000,,100,90
103,"Alexander","Hunold","AHUNOLD","590.423.4567",03-01-1990,"IT_PROG",9000,,102,60
104,"Bruce","Ernst","BERNST","590.423.4568",20-05-1991,"IT_PROG",6000,,103,60
105,"David","Austin","DAUSTIN","590.423.4569",25-06-1997,"IT_PROG",4800,,103,60
106,"Valli","Pataballa","VPATABAL","590.423.4560",05-02-1998,"IT_PROG",4800,,103,60
107,"Diana","Lorentz","DLORENTZ","590.423.5567",07-02-1999,"IT_PROG",4200,,103,60
108,"Nancy","Greenberg","NGREENBE","515.124.4569",18-08-1994,"FI_MGR",12000,,101,100
109,"Daniel","Faviet","DFAVIET","515.124.4169",16-08-1994,"FI_ACCOUNT",9000,,108,100
110,"John","Chen","JCHEN","515.124.4269",28-09-1997,"FI_ACCOUNT",8200,,108,100
111,"Ismael","Sciarra","ISCIARRA","515.124.4369",30-09-1997,"FI_ACCOUNT",7700,,108,100
112,"Jose Manuel","Urman","JMURMAN","515.124.4469",07-03-1998,"FI_ACCOUNT",7800,,108,100
113,"Luis","Popp","LPOPP","515.124.4567",07-12-1999,"FI_ACCOUNT",6900,,108,100
114,"Den","Raphaely","DRAPHEAL","515.127.4561",08-12-1994,"PU_MAN",11000,,100,30
115,"Alexander","Khoo","AKHOO","515.127.4562",18-05-1995,"PU_CLERK",3100,,114,30
116,"Shelli","Baida","SBAIDA","515.127.4563",24-12-1997,"PU_CLERK",2900,,114,30
117,"Sigal","Tobias","STOBIAS","515.127.4564",24-07-1997,"PU_CLERK",2800,,114,30
118,"Guy","Himuro","GHIMURO","515.127.4565",31-10-1998,"PU_CLERK",2600,,114,30
119,"Karen","Colmenares","KCOLMENA","515.127.4566",10-08-1999,"PU_CLERK",2500,,114,30
120,"Matthew","Weiss","MWEISS","650.123.1234",18-07-1996,"ST_MAN",8000,,100,50
121,"Adam","Fripp","AFRIPP","650.123.2234",10-04-1997,"ST_MAN",8200,,100,50
122,"Payam","Kaufling","PKAUFLIN","650.123.3234",01-05-1995,"ST_MAN",7900,,100,50
123,"Shanta","Vollman","SVOLLMAN","650.123.4234",10-10-1997,"ST_MAN",6500,,100,50
124,"Kevin","Mourgos","KMOURGOS","650.123.5234",16-11-1999,"ST_MAN",5800,,100,50
125,"Julia","Nayer","JNAYER","650.124.1214",16-07-1997,"ST_CLERK",3200,,120,50
126,"Irene","Mikkilineni","IMIKKILI","650.124.1224",28-09-1998,"ST_CLERK",2700,,120,50
127,"James","Landry","JLANDRY","650.124.1334",14-01-1999,"ST_CLERK",2400,,120,50
128,"Steven","Markle","SMARKLE","650.124.1434",08-03-2000,"ST_CLERK",2200,,120,50
129,"Laura","Bissot","LBISSOT","650.124.5234",20-08-1997,"ST_CLERK",3300,,121,50
130,"Mozhe","Atkinson","MATKINSO","650.124.6234",30-10-1997,"ST_CLERK",2800,,121,50
131,"James","Marlow","JAMRLOW","650.124.7234",16-02-1997,"ST_CLERK",2500,,121,50
132,"TJ","Olson","TJOLSON","650.124.8234",10-04-1999,"ST_CLERK",2100,,121,50
133,"Jason","Mallin","JMALLIN","650.127.1934",14-06-1996,"ST_CLERK",3300,,122,50
134,"Michael","Rogers","MROGERS","650.127.1834",26-08-1998,"ST_CLERK",2900,,122,50
135,"Ki","Gee","KGEE","650.127.1734",12-12-1999,"ST_CLERK",2400,,122,50
136,"Hazel","Philtanker","HPHILTAN","650.127.1634",06-02-2000,"ST_CLERK",2200,,122,50
137,"Renske","Ladwig","RLADWIG","650.121.1234",14-07-1995,"ST_CLERK",3600,,123,50
138,"Stephen","Stiles","SSTILES","650.121.2034",26-10-1997,"ST_CLERK",3200,,123,50
139,"John","Seo","JSEO","650.121.2019",12-02-1998,"ST_CLERK",2700,,123,50
140,"Joshua","Patel","JPATEL","650.121.1834",06-04-1998,"ST_CLERK",2500,,123,50
141,"Trenna","Rajs","TRAJS","650.121.8009",17-10-1995,"ST_CLERK",3500,,124,50
142,"Curtis","Davies","CDAVIES","650.121.2994",29-01-1997,"ST_CLERK",3100,,124,50
143,"Randall","Matos","RMATOS","650.121.2874",15-03-1998,"ST_CLERK",2600,,124,50
144,"Peter","Vargas","PVARGAS","650.121.2004",09-07-1998,"ST_CLERK",2500,,124,50
145,"John","Russell","JRUSSEL","011.44.1344.429268",02-10-1996,"SA_MAN",14000,0.4,100,80
146,"Karen","Partners","KPARTNER","011.44.1344.467268",06-01-1997,"SA_MAN",13500,0.3,100,80
147,"Alberto","Errazuriz","AERRAZUR","011.44.1344.429278",11-03-1997,"SA_MAN",12000,0.3,100,80
148,"Gerald","Cambrault","GCAMBRAU","011.44.1344.619268",16-10-1999,"SA_MAN",11000,0.3,100,80
149,"Eleni","Zlotkey","EZLOTKEY","011.44.1344.429018",30-01-2000,"SA_MAN",10500,0.2,100,80
150,"Peter","Tucker","PTUCKER","011.44.1344.129268",30-01-1997,"SA_REP",10000,0.3,145,80
151,"David","Bernstein","DBERNSTE","011.44.1344.345268",24-03-1997,"SA_REP",9500,0.25,145,80
152,"Peter","Hall","PHALL","011.44.1344.478968",20-08-1997,"SA_REP",9000,0.25,145,80
153,"Christopher","Olsen","COLSEN","011.44.1344.498718",30-03-1998,"SA_REP",8000,0.2,145,80
154,"Nanette","Cambrault","NCAMBRAU","011.44.1344.987668",09-12-1998,"SA_REP",7500,0.2,145,80
155,"Oliver","Tuvault","OTUVAULT","011.44.1344.486508",23-11-1999,"SA_REP",7000,0.15,145,80
156,"Janette","King","JKING","011.44.1345.429268",30-01-1996,"SA_REP",10000,0.35,146,80
157,"Patrick","Sully","PSULLY","011.44.1345.929268",04-03-1996,"SA_REP",9500,0.35,146,80
158,"Allan","McEwen","AMCEWEN","011.44.1345.829268",01-08-1996,"SA_REP",9000,0.35,146,80
159,"Lindsey","Smith","LSMITH","011.44.1345.729268",10-03-1997,"SA_REP",8000,0.3,146,80
160,"Louise","Doran","LDORAN","011.44.1345.629268",15-12-1997,"SA_REP",7500,0.3,146,80
161,"Sarath","Sewall","SSEWALL","011.44.1345.529268",03-11-1998,"SA_REP",7000,0.25,146,80
162,"Clara","Vishney","CVISHNEY","011.44.1346.129268",12-11-1997,"SA_REP",10500,0.25,147,80
163,"Danielle","Greene","DGREENE","011.44.1346.229268",19-03-1999,"SA_REP",9500,0.15,147,80
164,"Mattea","Marvins","MMARVINS","011.44.1346.329268",24-01-2000,"SA_REP",7200,0.1,147,80
165,"David","Lee","DLEE","011.44.1346.529268",23-02-2000,"SA_REP",6800,0.1,147,80
166,"Sundar","Ande","SANDE","011.44.1346.629268",24-03-2000,"SA_REP",6400,0.1,147,80
167,"Amit","Banda","ABANDA","011.44.1346.729268",21-04-2000,"SA_REP",6200,0.1,147,80
168,"Lisa","Ozer","LOZER","011.44.1343.929268",12-03-1997,"SA_REP",11500,0.25,148,80
169,"Harrison","Bloom","HBLOOM","011.44.1343.829268",23-03-1998,"SA_REP",10000,0.2,148,80
170,"Tayler","Fox","TFOX","011.44.1343.729268",24-01-1998,"SA_REP",9600,0.2,148,80
171,"William","Smith","WSMITH","011.44.1343.629268",23-02-1999,"SA_REP",7400,0.15,148,80
172,"Elizabeth","Bates","EBATES","011.44.1343.529268",24-03-1999,"SA_REP",7300,0.15,148,80
173,"Sundita","Kumar","SKUMAR","011.44.1343.329268",21-04-2000,"SA_REP",6100,0.1,148,80
174,"Ellen","Abel","EABEL","011.44.1644.429267",12-05-1996,"SA_REP",11000,0.3,149,80
175,"Alyssa","Hutton","AHUTTON","011.44.1644.429266",19-03-1997,"SA_REP",8800,0.25,149,80
176,"Jonathon","Taylor","JTAYLOR","011.44.1644.429265",24-03-1998,"SA_REP",8600,0.2,149,80
177,"Jack","Livingston","JLIVINGS","011.44.1644.429264",23-04-1998,"SA_REP",8400,0.2,149,80
178,"Kimberely","Grant","KGRANT","011.44.1644.429263",24-05-1999,"SA_REP",7000,0.15,149,
179,"Charles","Johnson","CJOHNSON","011.44.1644.429262",04-01-2000,"SA_REP",6200,0.1,149,80
180,"Winston","Taylor","WTAYLOR","650.507.9876",24-01-1998,"SH_CLERK",3200,,120,50
181,"Jean","Fleaur","JFLEAUR","650.507.9877",23-02-1998,"SH_CLERK",3100,,120,50
182,"Martha","Sullivan","MSULLIVA","650.507.9878",21-06-1999,"SH_CLERK",2500,,120,50
183,"Girard","Geoni","GGEONI","650.507.9879",03-02-2000,"SH_CLERK",2800,,120,50
184,"Nandita","Sarchand","NSARCHAN","650.509.1876",27-01-1996,"SH_CLERK",4200,,121,50
185,"Alexis","Bull","ABULL","650.509.2876",20-02-1997,"SH_CLERK",4100,,121,50
186,"Julia","Dellinger","JDELLING","650.509.3876",24-06-1998,"SH_CLERK",3400,,121,50
187,"Anthony","Cabrio","ACABRIO","650.509.4876",07-02-1999,"SH_CLERK",3000,,121,50
188,"Kelly","Chung","KCHUNG","650.505.1876",14-06-1997,"SH_CLERK",3800,,122,50
189,"Jennifer","Dilly","JDILLY","650.505.2876",13-08-1997,"SH_CLERK",3600,,122,50
190,"Timothy","Gates","TGATES","650.505.3876",11-07-1998,"SH_CLERK",2900,,122,50
191,"Randall","Perkins","RPERKINS","650.505.4876",19-12-1999,"SH_CLERK",2500,,122,50
192,"Sarah","Bell","SBELL","650.501.1876",04-02-1996,"SH_CLERK",4000,,123,50
193,"Britney","Everett","BEVERETT","650.501.2876",03-03-1997,"SH_CLERK",3900,,123,50
194,"Samuel","McCain","SMCCAIN","650.501.3876",01-07-1998,"SH_CLERK",3200,,123,50
195,"Vance","Jones","VJONES","650.501.4876",17-03-1999,"SH_CLERK",2800,,123,50
196,"Alana","Walsh","AWALSH","650.507.9811",24-04-1998,"SH_CLERK",3100,,124,50
197,"Kevin","Feeney","KFEENEY","650.507.9822",23-05-1998,"SH_CLERK",3000,,124,50
198,"Donald","OConnell","DOCONNEL","650.507.9833",21-06-1999,"SH_CLERK",2600,,124,50
199,"Douglas","Grant","DGRANT","650.507.9844",13-01-2000,"SH_CLERK",2600,,124,50
200,"Jennifer","Whalen","JWHALEN","515.123.4444",17-09-1987,"AD_ASST",4400,,101,10
201,"Michael","Hartstein","MHARTSTE","515.123.5555",18-02-1996,"MK_MAN",13000,,100,20
202,"Pat","Fay","PFAY","603.123.6666",17-08-1997,"MK_REP",6000,,201,20
203,"Susan","Mavris","SMAVRIS","515.123.7777",07-06-1994,"HR_REP",6500,,101,40
204,"Hermann","Baer","HBAER","515.123.8888",07-06-1994,"PR_REP",10000,,101,70
205,"Shelley","Higgins","SHIGGINS","515.123.8080",08-06-1994,"AC_MGR",12000,,101,110
206,"William","Gietz","WGIETZ","515.123.8181",07-06-1994,"AC_ACCOUNT",8300,,205,110

A few important things, this needs to be CSV, with column headers. It’s easier if the strings are double-quoted. And our employees have a HIRE_DATE, defined as a DATE. So we’re going to need a DATE FORMAT we can use to tell ORDS what to expect when it sees those values. More on that in a few paragraphs.

You CAN deviate from those ‘rules,’ but if you do, you’ll need to use some of the optional parameters when calling the POST API on our EMPLOYEES table.

Are we ready to try?

Authentication & Authorization

We’re going to try to access the API now, but it’s not going to work. I want you to see what happens when we’re authenticated but not authorized.

Instead of using cURL (I hate it), let’s try a REST API client/GUI like Insomnia or Postman.

I’ve talked before here how we should be using the OAuth2 workflow to authenticate to our REST APIs vs using Database Authentication in Autonomous. But…we’ll keep it simpler here in this use case. And by simple, I mean Basic AUTH.

Using our HR username and password on the POST request, we get something perhaps a bit unexpected?

We’re authenticated, but NOT authorized. Our session doesn’t have the privilege required to access the REST API we published on our table. Remember we clicked the ‘protect’ switch when enabling the table, and it showed us a ROLE and PRIV? We need grant the privilege to the ‘SQL Developer’ role if we want to use database authentication.

To remedy that, head to the REST panel, then click on ‘Security’ and ‘Privileges.’

Find your privilege and click ‘the kebab button’ on the upper right-hand corner of the card, and select Edit.

Go ahead and give our Privilege a label and description.

The important part is up there where it says ‘Roles.’

We’re going to move ‘SQL Developer’ over to the Roles list for this privilege, and hit ‘Save.’

The ‘SQL Developer’ role is inherited by any Authenticated request that used database username and password. So once we have that, we won’t get the HTTP 401 (DENIED!) error.

Loading CSV to Oracle via REST. Look ma, no code!

You can basically just hit the ‘go’ button again now, but not quite. Remember we were talking about the DATE formats? Yeah, we need to account for that.

We’re going tell ORDS what our date format is in the CSV text file by using a parameter in the POST URI.

Bookmark these Docs

The parameter we want to use here is ‘dateFormat’ –