Moving large data between Oracle database and CSV

I'm currently working on a project that involves importing and exporting large volumes of data between spreadsheet-friendly comma-separated-value (CSV) files and an Oracle database. Although import/export of data can be done using a GUI tool like SQLDeveloper, it is not practical for large volumes or when such jobs need to be scripted. This blog describes how this can be done using BASH scripts and the command-line tools sqlplus and sqlldr.

Scenario

Suppose I run an online shop and maintain a list of purchase transactions with fields such as item_id, customer_id, delivery_status, etc. On my Oracle database, I have PURCHASE_TRANSACTIONS, ITEMS and CUSTOMERS tables with the appropriate joins.

Exporting from Oracle DB to CSV

To export data from the Oracle database table (normally with additional joins and filtering criteria), create a SQL file with:

• a query to write out the header row of the CSV
• a query to get the data and concatenate them together in a CSV format

Something like this to export a list of items pending delivery:

set head off;
set feed off;
set trimspool on;
set linesize 32767;
set pagesize 32767;
set echo off;
set termout off;

spool &1;
select 'ITEM_ID,ITEM_NAME,PRICE,CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS' from dual;
select pt.item_id
|| ',"' || itm.name
|| '",' || itm.price
|| ',' || pt.customer_id
|| ',"' || c.name
|| '","' || c.address
|| '"'
from PURCHASE_TRANSACTIONS pt
join CUSTOMERS c on pt.customer_id = c.id
join ITEMS itm on pt.item_id = itm.id
where pt.delivery_status = 'PENDING'
order by pt.customer_id;

spool off;
exit;

Note the double-quotes around textual data. To execute this, save it to a file named export-pending.sql and write a simple BASH script like so:

sqlplus -S MY_DB_USER/MY_DB_PASSWORD@MY_DB_SERVER:1521/DB_SID @export-pending.sql pending-delivery-report.csv

Running this script will write out the file pending-delivery-report.csv with contents like:

ITEM_ID,ITEM_NAME,PRICE,CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_ADDRESS
432,"Super Mouse Pad",23.5,11,"John Smith","45 Some Street, Sydney, NSW 2000"
877,"USB Fan",14,11,"John Smith","45 Some Street, Sydney, NSW 2000"
344,"NeverDie UPS",125.3,67,"Alex Jones","101 Scary Road, Melbourne, VIC 3000"
...

This export functionality is typically used to generate spreadsheet friendly reports.

Importing from CSV to Oracle DB

Suppose I have a CSV file items-data.csv containing a new list of items that I want to ADD to the ITEMS table, in the following format:

ID,NAME,PRICE
1001,'Lightbulb',1.95
1002,'Car battery',98
1003,'Super cool camera',450.50
...

To load this into the database, create a control file load-items.ctl with the following:

load data
append
into table ITEMS
fields terminated by ',' optionally enclosed by '\''
(
    ID,
    NAME,
    PRICE
)

Note that in order to REPLACE the entire table's contents with the CSV data, simply change the append instruction (second line) to replace. Finally, create a simple BASH script with the following to run sqlldr:

sqlldr MY_DB_USER/MY_DB_PASSWORD@MY_DB_SERVER:1521/DB_SID control=load-items.ctl data=items-data.csv direct=true rows=1000 skip=1 errors=1 log=load-items.log bad=load-items.bad discard=load-items.discard

After executing this script, check the items-data.log for the progress of the data load. The load-items.bad and load-items.discard files contain the data with errors.

SQL*Plus connection to remote Oracle database

SQL*Plus is a very handy command line tool when you want to quickly access an Oracle database or execute SQL statements from scripts, e.g. BASH scripts. However, getting it to connect to a remote Oracle database server in a script proved to be quite a challenge. This blog documents how to write the connection string in various cases.

Local database

Connecting to a local database is trivial, just use:

$ sqlplus dbUser/dbPassword@dbSid

Remote database from command line

Here's the nasty syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))'

You can also connect using a SERVICE_NAME instead of SID.
Note that the single-quotes are needed to preserve the brackets.

Remote database from a BASH script

I needed to write a script that called SQL*Plus several times. In order to make it maintainable, the username, password and the part after the @ were specified as environment variables. Unfortunately, there did not seem to be any obvious way of setting the value of the environment variable so that the single-quotes were properly sent to the sqlplus command at run time. I had to explicitly escape each bracket with a backslash, like so:

DB_CONNECTION=\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=remoteServer\)\(PORT=1521\)\)\)\(CONNECT_DATA=\(SID=dbSid\)\)\)

Because this script was part of a Java project, I subsequently moved this value to a Java properties file. This means every backslash had to be escaped with another backslash to prevent Java from interpreting it. The entry in the Java properties file:

db.connection = \\(DESCRIPTION=\\(ADDRESS_LIST=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(HOST=remoteServer\\)\\(PORT=1521\\)\\)\\)\\(CONNECT_DATA=\\(SID=dbSid\\)\\)\\)

(I know I could have just used JDBC, but there was a good reason why I had to use SQL*Plus for this Java project.)

Define a local service name to a remote server

An alternative to using the big ugly connection string is to define a local service name in the local tnsnames.ora that references the remote instance. Add the following:

DB_LOCAL_NAME =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= remoteServer)(Port= 1521))
    (CONNECT_DATA = (SID = dbSid))
  )

to

${ORACLE_HOME}/server/network/admin/tnsnames.ora

Connecting to the remote database is simply:

$ sqlplus dbUser/dbPassword@DB_LOCAL_NAME

Of course, this solution has a few drawbacks (enough to dissuade me from using it).
- Need write permission to the tnsnames.ora.
- The application relies of custom entries in tnsnames.ora, which must be maintained together with the application code.
- Every environment (e.g. development, staging, UAT, production) in which the application is deployed to will need to have its tnsnames.ora modified and maintained.

---

Date sensitive integration testing of database code with Oracle

Note:

• Commands starting with $ are to be entered in a shell, e.g. BASH.


• Commands starting with SQL> are to be entered in a database client, e.g. SQLPlus or JDBC code

Scenario

Lately, I've been working with a lot of database code, mostly packaged as stored procedures, running on Oracle 10g XE. These stored procedures are tested using a set of automated integration tests written in Java (JUnit + DBUnit). Everything was fine until one day, I needed to implement a logic that filters out data based on whether a date field stores a date within a certain period of time. The SQL for this was quite trivial, for example:

SELECT ... WHERE table1.the_date_field > ADD_MONTHS(SYSDATE, -6)

This example would filter out all results with the_date_field storing a date older than 6 months ago.

The Problem

The problem arose when I needed to write automated tests for this. The tests loaded up a set of hardcoded test data fixture (using DBUnit). The test data would have hardcoded date values. Obviously, these tests would give different results over time because SYSDATE would return the current system time by default.

The Solution(s)

One possible solution would be to dynamically inject the dates into the test data when the tests were executed. However, this would require a lot of work because of the quantity and spread of the dates. I would need to convert existing hardcoded dates into offsets from SYSDATE, then write some code to perform the injection at runtime.

Given this, I chose the reverse approach :- make SYSDATE return a fixed value.

Stopping time

Luckily, Oracle provided a way to let me do just this.
Firstly, I had to login to the database as a SYSDBA and grant my Oracle user the ALTER SYSTEM system privilege.
Now, I could invoke the following SQL in the set-up code of the JUnit tests:

SQL> alter system set fixed_date = '01-APR-2005'

This would make SYSDATE always return April 1st, 2005. All the test data could now be measured relative to this date, and the tests would not break no matter when they were run.

The tear-down code of the JUnit tests would invoke:

SQL> alter system set fixed_date = 'none'

to revert SYSDATE back to its default behaviour.

PFile and SPFile

However, when I tried to restart Oracle server, it failed to start up. After a few hours, I found out why. Oracle by default stored parameter settings in a binary file format called SPFile. Any changes made with ALTER SYSTEM were always persisted to this file, so that when Oracle was restarted, it tried to load them again. For some reason (a bug IMHO), Oracle could not load the fixed_date value of 'none', it simply died.

The workaround was to change to a different file format called PFile. PFile is a normal text file that could be editted, and unlike SPFile, Oracle would not persist any parameter changes to it. In my opinion, PFile is a better choice for a development or automated testing database, where changes made during tests should not be persisted or interfere each other. To convert from SPFile to PFile, first login as a SYSDBA:

$ $ORACLE_HOME/bin/sqlplus sys as sysdba

then execute:

SQL> create pfile from spfile;

Then shut down Oracle server, and remove the SPFile. The SPFile name has the following format: spfileSID.ora, where SID is the database SID. I was developing on a Ubuntu machine and my SID was XE. Hence, I ran this:

$ rm $ORACLE_HOME/dbs/spfileXE.ora

The PFile name has the format initSID.ora, so it was initXE.ora on my machine. Edit this file with a text editor and delete the line:

*.fixed_date='NONE'

Finally, restart Oracle.

Limitations

I found that when SYSDATE was fixed to a hardcoded value, jobs could not be scheduled using DBMS_JOB functions. DBMS_JOB.SUBMIT did not seem to work at all. Therefore, I can't yet think of a way of both freezing time and testing any code that rely on scheduled jobs.

---