Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting TNS problem while using SQLLDR unix command

inDiscoverAug 30 2013 — edited Sep 3 2013

I have ons shell file (finalloader1.sh) which contains below lines of code to load a text file (data.txt) into an oracle table.

export ORACLE_HOME=/opt/oracle/product/11.2.0.3/client
export TNS_ADMIN=/sys_apps/admnapps/loadingplace/bin
export ORACLE_SID=d_btgen
export PATH=/opt/oracle/product/11.2.0.3/client/bin:$PATH

##---------------------------------------------------------------------------------##
## SQL*Loader command to execute the loading of data from data.txt into oracle table ##
##---------------------------------------------------------------------------------##

function sql_load
{
        sqlldr /@d_btgen, control=/sys_apps/admnapps/loadingplace/bin/load.ctl, log=/sys_apps/admnapps/loadingplace/in/load.log, errors=100
        result=$?
        check_for_error ${result} 101
}

function sql_script
{
        sqlplus /@d_btgen @/sys_apps/admnapps/loadingplace/bin/update_insert.sql
        result=$?
        check_for_error ${result} 102
}

exec
        sql_load
        sql_script

See the text file (data.txt) format below , which I am trying to load (note that seperator is pipe operator)

hafees|hamsa||hafees hamsa|39879|+1234|asdfg@xxx.com|consultant|Professional|215010||2013-08-12||A||sdfgt2

See the controller file (/sys_apps/admnapps/loadingplace/bin/load.ctl) below.

LOAD DATA
INFILE '/sys_apps/admnapps/loadingplace/in/data.txt'
BADFILE '/sys_apps/admnapps/loadingplace/in/bad.bad'
DISCARDFILE '/sys_apps/admnapps/loadingplace/in/dsc.dsc'
DISCARDMAX 5
INTO TABLE emp_data FIELDS TERMINATED BY '|'
(
name_last"upper(:name_last)"
, name_first"upper(:name_first)"
, c_middle_initial"upper(SUBSTR(:c_middle_initial, 1, 14))"
, emp_name"upper(:emp_name)"
, em_number
, phone
, email
)

But problems comes here when I am executing my shell file (finalloader1.sh). See the error below.

SQL*Loader: Release 11.2.0.3.0 - Production on Fri Aug 30 07:06:54 2013

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified
ERROR invoking SQL*Loader in function sql_load.
finalloader1.sh: line 68: print: command not found
Shell Script UNSUCCESSFULLY ENDED with a return code of (1) at \c
08/30/2013/07:06:54 AM

I am not understanding the issue. I googled this many times but came to know that there is some configuration issue in tnsnames.ora file. But as per my knowledge I have done every settings those I know. See my tnsnames.ora file below.

d_btgen.world=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rwad10.rw.discoverfinancial.com)(PORT=1576))(CONNECT_DATA=(SERVICE_NAME=d_btgen.dfs.us)))

Any one of you can help me on this. You timely help is well appreciated.

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 1 2013

Added on Aug 30 2013

#database-key-features, #export-import-sql-loader-external-tables

4 comments

1,243 views