Export data from Oracle Application Express and import via Oracle SQL*Loader

The other day, Josie from Oracle asked me:

"When I export the data, both as csv and xml the date is exported like this: 2005-08-29T00:00:00. sqlldr has a fit about that!"

What she was saying, in rather abbreviated form, was that she was having difficulty using the Oracle utility SQL*Loader to import a data file which was exported from Application Express -> Utilities -> Data Load/Unload. In particular, Josie was having difficulty loading the data of datatype DATE.

If you Unload to Text the EMP table, you'll get something that looks like:

"7839","KING","PRESIDENT","","1981-11-17T00:00:00","5000","","10"
"7698","BLAKE","MANAGER","7839","1981-05-01T00:00:00","2850","","30"
"7782","CLARK","MANAGER","7839","1981-06-09T00:00:00","2450","","10"
"7566","JONES","MANAGER","7839","1981-04-02T00:00:00","2975","","20"
"7788","SCOTT","ANALYST","7566","1982-12-09T00:00:00","3000","","20"
"7902","FORD","ANALYST","7566","1981-12-03T00:00:00","3000","","20"
"7369","SMITH","CLERK","7902","1980-12-17T00:00:00","800","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20T00:00:00","1600","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22T00:00:00","1250","500","30"
"7654","MARTIN","SALESMAN","7698","1981-09-28T00:00:00","1250","1400","30"
"7844","TURNER","SALESMAN","7698","1981-09-08T00:00:00","1500","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12T00:00:00","1100","","20"
"7900","JAMES","CLERK","7698","1981-12-03T00:00:00","950","","30"
"7934","MILLER","CLERK","7782","1982-01-23T00:00:00","1300","","10"

What should stand out to you is the value for the EMP.HIREDATE column. Why is it formatted this way?

To explain it simply, users of Application Express span all possible countries, territories and languages. A date format that works in one language may not work in another. A good example is a date value that contains an actual month name or an abbreviation of a month name. Today's date in English in Oracle date format DD-FMMonth-RRRR would be 17-July-2008. But change your language to German and you'll get 17-Juli-2008. If your data contains '17-July-2008' and you try to import it into a system with German language settings, it will fail - 'July' is not a valid month name in German.

For the export of DATE type data from Application Express, we needed to use something that works across all languages. We could have devised our own canonical date format. But instead, we decided to employ the international representation of date and time, namely, ISO 8601. So for those who scratch their head and wonder where that odd "T" comes from in the date value, here is your answer.

With this understanding in place of why this value is in this odd-looking format, let's get back to Josie's original question - how do I import this using SQL*Loader? Using a SQL *Loader SQL Operators and escape characters, it's quite easy. Here is a SQL*Loader control file which can be used to load the EMP data from above:

load data
   infile "/tmp/emp.txt"
   append into table emp
   fields terminated by ','
   optionally enclosed by '"'
   trailing nullcols
   (EMPNO, ENAME, JOB, MGR, HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')", SAL, COMM, DEPTNO)

The critical element in the field list, of course, is:

HIREDATE "to_date(:HIREDATE,'rrrr-MM-dd\"T\"HH24:mi:ss')"

which is simply applying a TO_DATE SQL operator to the HIREDATE field of the data file. Additionally, the data value will be represented as a string in a specific format. The double-quotes before and after the 'T' must be escaped, so SQL*Loader doesn't try to interpret that as the end of the expression.


Happy loading.