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).
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.