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.