1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL LOADER -- Unable to Connect

Discussion in 'Other Development Tools' started by robin, Jan 10, 2012.

  1. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi,

    I have oracle apps installed on my system.I never worked with sql loader before.I read a document and tried to practise.

    I created a control file, a data file .

    Then I invoked sql loader from cmd as

    sqlldr control=sa.ctl log=sa.log


    It gives the following error :

    sql *loader -704 : Internal error : ulconnect :OCIServerAttach[0]

    ORA-12154 : TNS :Could not resolve the connect identifier

    ORA -12560 :TNS : Protocol adapter error specified.

    I tried different combinations like giving the full path of ctl and log files;placing the control and data files in different places .It didnot work.


    Mine is local machine.I didnot install apps myself.I googled the error and it says that there is some problem with tnsnames.ora I find that the host is defined there.And besides my sql *plus,toad and the application are running fine.
    It's only sql loader that gives this error.This is my first time.Plz Help



    here's how tnsnames.ora looks like in my machine :



    # TNSNAMES.ORA Network Configuration File: D:\jdevolper\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    EXTPROC_CONNECTION_DATA.ORA.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    )
    )

    VIS.ORA.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = apps.ora.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = vis)
    )
    )
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where are you specifying the username/password and server? I don't see any login information in your command line; it should look like this:

    sqlldr user/pass@db control=sa.ctl log=sa.log


    If you don't specify such information the default is the ORCL instance and you do not have any database of that name running.
     
  3. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad


    I have specified userid and pwd as u suggested but I still get this error.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You have to username , password and Db Name as suggested.
    eg:

    Code (Text):

    sqlldr scott/tiger@mydb control=sa.ctl log=sa.log
     
     
  5. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Raj,

    I have specified database name as well.Sorry for not mentioning that in my post.

    sqlldr apps/apps@vis control=sa.ctl log=sa.log
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Ok. Now try tnsping the same dbname/service name from the command prompt as given below and show us the output.

    Code (Text):
    tnsping vis
     
  7. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    tnsping vis

    TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 15-FEB-20
    12 17:30:29

    Copyright (c) 1997 Oracle Corporation. All rights reserved.

    Used parameter files:

    TNS-03505: Failed to resolve name
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Check yor TNSNAME.ora file to ensure that it contains the parameters for the service name "vis" .

     
  9. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi Raj,

    I checked the tnsnames file.I dont see anything wrong there.


    TNSNAMES.ORA Network Configuration File: D:\jdevolper\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    EXTPROC_CONNECTION_DATA.ORA.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    )
    )

    VIS.ORA.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = apps.ora.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = vis)
    )
    )



    All my other tools like reports,forms and toad are connecting
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Try
    Code (Text):
    tnsping VIS.ORA.COM

    Also show the entries form the listener of the "vis" server.

    Code (Text):
    lsnrctl status
    Just check if you have such service exists over there.