Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Joseph.

Asked: October 11, 2017 - 3:13 pm UTC

Last updated: October 24, 2017 - 1:54 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

To previous answer to MY SQL*Loader question I had asked, you said:

Examples of SQL*Loader String
sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl 
sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name control=my_control.ctl


When you have SQL*Loader contact us for additional help.

------------------------

To fill this string for proper execution, this data is found in the TNS Edit about my database:

EDWD.TAKEDAPHARM.COM=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=takt01-scan.unix.tpna.com)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=tedwd.takedapharm.com)
    )
  )

my control file

LOAD DATA
INFILE 'C:\Users\jgiallom\Documents\RADS42Development Area\DataLoadSLQLoader\ThirdParty_Data.csv' 
BADFILE 'C:\Users\jgiallom\Documents\RADS42DevelopmentArea\DataLoadSLQLoader\ThirdParty_Data.bad'
DISCARDFILE 'C:\Users\jgiallom\Documents\RADS42DevelopmentArea\DataLoadSLQLoader\ThirdParty_Data.dsc'

INTO TABLE "DOBS"."CP_THIRDPARTY"
APPEND
FIELDS TERMINATED BY '|'
(ID,
MASTER_RECORD_TYPE,
NAME,
PRIMARY_STREET_ADDRESS,
PRIMARY_COUNTRY,
PRIMARY_CITY,
PRIMARY_STATE_PROVINCE,
ZIP,
CLIENT_ID,
ITEM_TYPE,
PATH,
DATAYEAR)

LOADER STRING

 sqlldr.exe userid=*****/*****@//takt01-scan.unix.tpna.com:1521/TEDWD.TAKEDAPHARMA.COM control=ThirdPartyData.ctl


I run CMD and cd\mydirectory which contains all files.

I carefully type the [line above] and get this error message:


SQL*Loader: Release 11.2.0.3.0 - Production on Wed Oct 11 09:59:58 2017

in the log:

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

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

If I try a simple string  sqlldr.exe userid=myID/password control=mycontrol.ctl

I get error message:




SQL*Loader: Release 11.2.0.3.0 - Production on Wed Oct 11 10:07:34 2017

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

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12560: TNS:protocol adapter error


My Question: Can you provide any suggestions as to what is wrong and how I might correct what I am doing?

Thank you.


and Chris said...

Something is wrong in your configuration. It could be that the database is down, not registered with the listener or you've got the service name wrong. Or some combination of the above ;)

You can check the service name with:

select value from v$parameter 
where name='service_names';


You can find more information on the causes of these errors and solutions at:

http://docs.oracle.com/database/122/NETAG/troubleshooting-oracle-net-services.htm#NETAG394
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:431775600346873836

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL Loader errors

Joseph Giallombardo, October 12, 2017 - 5:02 pm UTC

Thanks I will check it out.

Followup

Joseph Giallombardo, October 12, 2017 - 9:58 pm UTC

Using TOAD TNS Ping Ultimate discovered this about

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 12-OCT-2017 16:47:15
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
C:\Oracle\Ora10\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=takt01-scan.unix.tpna.com) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=tedwd.takedapharm.com)))
OK (90 msec)

Based on previously provided string example..
I used:

sqlldr.exe userid=PAllegretti/pal234@//takt01-scan.unix.tpna.com:1521/tedwd.takedapharm.com control=ThirdPartyData.ctl

Error: 704 Internal error: ulconnect: OCIServerAttach [0]
Error: ORA 12514 TNS: listener does not currently know of service requested in connect descriptor

So, I have looked at the examples but I can't quit figure out how to get the "listener" to "listen"

Can you be a bit more specific...Thanks.



Connor McDonald
October 13, 2017 - 3:33 am UTC

Duplicating questions/reviews on two threads so that both Chris and I end up working on the same issue...doesn't win you any favours from us.

SQL Loader Errors

Joseph Giallombardo, October 17, 2017 - 3:50 pm UTC

The error I received:
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

...seems to imply that a "Listener" needs to be set up by the DBA in order to use SQL *Loader, or that there is a listener but it doesn't recognize my use of SQL*Loader.

They have provided me SQL*Loader. and I can have Database and can create tables, sequences, indexes, alter, insert, delete, edit, etc. but I can't load my tables...

What can I do or how might I phrase it to the DBA so that I can simply load the data?

Can you explain what "generally" might be done, administratively in order to use SQL*Loader?


Chris Saxon
October 17, 2017 - 4:37 pm UTC

You need to find out what service(s) your database has registered with the listener.

Ask your DBA to provide you with the connection string to the database and relevant TNSNAMES entries.

means of v$parameter

Joseph Giallombardo, October 17, 2017 - 7:03 pm UTC

I was given the code to check on service_names

select value from v$parameter
where name='service_names';

Error: Invalid SQL statement
Connor McDonald
October 18, 2017 - 3:27 am UTC

Where are you running it ? Works fine in SQL Plus (and most other tools)


SQL> select value from v$parameter
  2  where name='service_names';

VALUE
----------------------------------------
db122


SQL Loader Followup

Joseph Giallombardo, October 23, 2017 - 10:19 pm UTC

I was running it from TOAD logged on to my schema.

Question: When loading a CSV file 500,000 records to Oracle table, does the file have to be on the server, or can you have it in your local server. It seems that I get the error of listener can find the database, because the file is not on the server. Is that the requirement. As I client, I have to create the files from our MS Access application and somehow get them to the server first. Is there a tool for that?


Connor McDonald
October 24, 2017 - 1:54 am UTC

The file must be accessible by SQL Loader. If you are running SQL Loader on your PC, the file must be accessible by the PC (either on the PC itself or via network drive).

If you are running SQL Loader on the server, the file must be accessible by the server
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database