Oracle SQL*Loader

Overview

This article shows how to configure your environment to use Oracle SQL*Loader.

This is useful when the machine on which the Runtime has no Oracle software pre-installed.

Download and install oracle instant client

Download "Oracle Instant Client" from the Oracle downloads website.

Make sure to download the "Basic Instant Client" or the "Instant Client Light".

And of course also download the "Tools Package" which contains the SQL*Loader tool.

Unzip all this material to a single directory, which will look like this:

D:\apps\oracle\instantclient_12_2>dir
 Volume in drive D is Data
 Volume Serial Number is 2891-91F0

 Directory of D:\apps\oracle\instantclient_12_2

12/12/2019  18:05    <DIR>          .
12/12/2019  18:05    <DIR>          ..
08/03/2017  14:34            19,968 adrci.exe
08/03/2017  14:34            23,264 adrci.sym
08/03/2017  14:35               512 BASIC_README
08/03/2017  14:34           617,984 exp.exe
08/03/2017  14:34           386,128 exp.sym
08/03/2017  14:34           200,192 expdp.exe
08/03/2017  14:34            99,520 expdp.sym
08/03/2017  14:34            55,808 genezi.exe
08/03/2017  14:34            56,264 genezi.sym
13/01/2006  07:36               342 glogin.sql
08/03/2017  14:34           310,272 imp.exe
08/03/2017  14:34           107,016 imp.sym
08/03/2017  14:34           208,896 impdp.exe
08/03/2017  14:34           104,048 impdp.sym
08/03/2017  14:27           778,752 oci.dll
08/03/2017  14:27           731,816 oci.sym
15/12/2016  08:56           152,576 ocijdbc12.dll
15/12/2016  08:56            44,936 ocijdbc12.sym
08/03/2017  13:58           565,248 ociw32.dll
08/03/2017  13:58            96,456 ociw32.sym
13/12/2016  23:40         4,036,257 ojdbc8.jar
08/03/2017  13:47            71,680 oramysql12.dll
08/03/2017  13:47            44,368 oramysql12.sym
24/11/2016  04:52         4,709,888 orannzsbb12.dll
24/11/2016  04:53         2,344,232 orannzsbb12.sym
08/03/2017  13:19         1,026,048 oraocci12.dll
08/03/2017  14:34         1,161,816 oraocci12.sym
08/03/2017  13:41         1,064,960 oraocci12d.dll
08/03/2017  14:34         1,137,064 oraocci12d.sym
08/03/2017  14:31       195,298,304 oraociei12.dll
08/03/2017  14:32        13,912,192 oraociei12.sym
29/07/2016  00:42           252,416 oraons.dll
08/03/2017  14:23           295,424 orasql12.dll
08/03/2017  14:23            60,072 orasql12.sym
12/01/2017  13:20         1,637,888 orasqlplusic12.dll
08/03/2017  13:44         1,525,248 sqlldr.exe
08/03/2017  13:44           257,432 sqlldr.sym
12/01/2017  13:20         1,064,448 sqlplus.exe
08/03/2017  14:36           176,288 sqlplus.sym
08/03/2017  14:36               516 SQLPLUS_README
08/03/2017  14:36               512 TOOLS_README
08/03/2017  14:34            19,968 uidrvci.exe
08/03/2017  14:34            23,264 uidrvci.sym
12/12/2019  18:05    <DIR>          vc14
08/03/2017  14:34            19,456 wrc.exe
08/03/2017  14:34            22,080 wrc.sym
26/01/2017  09:36            74,230 xstreams.jar
              46 File(s)    234,796,049 bytes
               3 Dir(s)  142,338,174,976 bytes free

Create the tnsnames.ora file

Create a directory and a text file named "tnsnames.ora". It will contain all your database connection data strings.

In this example, the file "D:\apps\oracle\network\admin\tnsnames.ora" will contain:

baseqa=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=qaoora11g2-01)(PORT=1521))(CONNECT_DATA=(SID=ORA112)))

Configure the Environment

Add the "Oracle Instant Client" installation directory to the system’s Path variable (1).

And also add the new environment variable TNS_ADMIN to declare the location of "tnsnames.ora" (2).

add oracle client in operating system variables

Design your metadata and mappings

This is all you need to do in terms of Environment.

Now you can Create / Reverse your Oracle metadata.

Make sure the "Physical Name" of the oracle server reflects the database name in your tnsnames.ora file. In my example : "baseqa".

And develop your Mapping, selecting the Templates which use SQL*Loader.