4 Replies Latest reply: Sep 2, 2013 11:52 PM by Barbara Boehmer RSS

    Getting TNS problem while using SQLLDR unix command

    inDiscover


      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

      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

      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.