Sql loader 704 internal error ulconnect ociserverattach 0

Problem

Publish failing for all apps in the environment

Symptom

From console error details:

SQL*Loader-704: Internal error:
ulconnect: OCIServerAttach [0]

From the .out file in
the temp directory:

ORA-12154: TNS:could not resolve the connect
identifier specified

Cause

The Oracle bulk load utility, sqlldr, could not successfully connect to the
oracle instance.

Resolving The Problem

During the publish process, a series of files are created in the TEMP directory on the job server that is processing the publish. If you open the .cmd file in the TEMP directory, you should see the sqlldr command that is being executed. Try running that same command in a command prompt on that server (to simulate what happens during a publish. If the command doesn’t work when you try to run it manually, that could indicate an issue with the Oracle SQLLDR utility. A common problem is the @ symbol in either
the oracle username or password — that’s valid in other parts of Oracle, but will cause the sqlldr utility to fail.

Historical Number

1037914

Содержание

1. SQL*Loader-704: Internal Error: Ulconnect: OCIServerAttach [0] ORA-12154 (Doc ID 2598867.1)
2. Applies to:
3. Symptoms
4. Cause
5. To view full details, sign in with your My Oracle Support account.
6. Don’t have a My Oracle Support account? Click to get started!
7. SQL*Loader-704
8. Best Answer
9. Answers
10. SQL*Loader-704: Internal error
11. Best Answer
12. Answers
13. SQL*Loader-704 Error
14. Comments
15. SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
16. Answers

More over I could not find the oracle service in Administrative tools>Services of windows. how it’s possible. I am using the Oracle 10.2 client

Do you have the environement variable TNS_ADMIN set correctly?
It should point to the directory where you have the tnsnames.ora file.

Try to run sqlloader from command line to see whether it loads the data.

I have set the TNS_ADMIN environment variable and ran the sqlloader from command line.

I got this error

SQL*Loader: Release 10.2.0.1.0 — Production on Mon Jan 19 10:08:19 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (Premiums.ctl)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

But Premiums.ctl is available in that folder. Could you please help me here.

What is the OS?
Any file permission issues?

OS : windows XP
I  have administrator privilege .May be some other issue.

Any body can help me in fixing this issue.

hey, I have cross verified that and now it’s working fine from the command propmt i.e datas are writing properly from the csv file to the database. I am wondering how it’s not happening automatically,
 Please give me some clue to figure out this issue.

Your application that loads the file(i dont know what application is that), could be passing a wrong connect string.

Check from your application whether you can make a successful connection to oracle.

My application is deployed in the Tomcat 6.0 and connection from it to oracle is fine. Might be some other issue I guess. any other possible solution to fix this issue.

Hey any body please help me to figure out the issue.

View this solution by signing up for a free trial.

I have fixed this issue with the help of you guys. thank u

Read these next…

• 
  

  Can’t communicate with scanner

  Hardware

  Hello everyone,I have a client that uses Kodak ScanMate i940 scanners at their desks. They recently got a new computer that we prepped and put on their domain. This new computer is recognizing the scanner but it is unusable with the error «can’t communica…

• 
  

  Different MAC address, same computer name — multiple DHCP leases

  Windows

  I’m noticing a lot more now that our Wifi DHCP scopes are filled by the same computer name, but with different mac addresses. I know there’s a feature in Windows 10 called Random hardware addresses, but that setting is off. Is there anything else that cou…

• 
  

  Snap! — No-Password Logins, Solar Powered Water Filter, Glitch in the Matrix?

  Spiceworks Originals

  Your daily dose of tech news, in brief.

  Welcome to the Snap!

  Flashback: February 9, 1996: Introduction of the Bandai Pippin (Read more HERE.)

  Bonus Flashback: February 9, 1990: Galileo Probe does a Venus Flyby (Read more HERE.)

  You nee…

• 
  

  Roku TV being used as Wallboard Issues

  Hardware

  Helping someone out at their shop. They have 4 large Roku screens and 2 laptops with dual HDMI ports for video. They are viewing static website business dashboards and PowerPoint. At first all 4 screens connected to wireless, worked for a while but with a…

• 
  

  Charging for SSO

  Security

  We have SSO set up with around 5 or 6 solution providers via our M365. Not one of them charges for this, they just sent us the documentation.I identified another online service in use by one of our departments which would benefit from using SSO for staff …

sqlldr user/password@connection_string control=data_load.ctl

OPTIONS(SKIP=1)

LOAD DATA

INFILE ‘data_input.csv’ 

BADFILE ‘data_input.err’ 

APPEND INTO TABLE DATA_RANGE_INPUT 

FIELDS TERMINATED BY «,» OPTIONALLY ENCLOSED BY ‘»‘

TRAILING NULLCOLS

(

 range_id «DATA_INPUT_SEQ_01.NEXTVAL»,

 code_a, 

 code_b,

 from_data, 

 to_data, 

 status_ind,

 status,

 creation_date  SYSDATE,

 last_modification_date SYSDATE,
 skip_this_field  FILLER,

 update_user CONSTANT «DATALOAD»)

OPTIONS(SKIP=1) — skip header line




===========================================
Example B — Load Huge file
===========================================
Issue B.

In this example, the load is processing 1,000,000 rows and more.

OPTIONS (ROWS=5000000, BINDSIZE = 400000000, READSIZE=400000000, ERRORS=20000000)

LOAD DATA
APPEND
INTO TABLE SQLLOAD_COUNTERS_HIST
fields terminated by «,»
TRAILING NULLCOLS
(
ts_last_modified,
node_id,
static_id,
dyn1,
dyn2,
counter_sum,
counter_delta,
counter_name 
)


ROWS — Number of rows in conventional path bind array or between direct path data saves
BINDSIZE — Size of conventional path bind array in bytes  
            (Default 256000)
READSIZE — Size of read buffer                  
            (Default 1048576)
ERRORS — Number of errors to allow            
          (Default 50)



===========================================
Example C — Load DATE using TO_DATE and SUBSTR functions
===========================================
Issue A.
In this example, The first field has miliseconds in the date format.
In the database, this field is DATE, without milliseconds.


Solution
Use String and Conversion functions:
 «TO_DATE(SUBSTR(:ts_last_modified,1,19),’YYYY-MM-DD hh24:mi:ss’)» 

Issue B.

Inside the data, there are numerous delimiter lines, in this format ‘————————‘ , that should be ignored.


Solution
Add Options part, where runtime parameters can be specified inside the control file.
Inside the Options part, add ERRORS=1000000.
By setting the ERRORS parameter to a very high value, we avoid the error «MAXIMUM ERROR COUNT EXCEEDED».




Data looks like:
2014-11-01 00:12:08.716,104032100000000,298,0,34,0,codeA
2014-11-01 00:12:08.716,104032000000001,1,405,13868,7,codeB

2014-11-01 00:12:08.716,104032000000001,1,298,24076,6,codeC
———————————-
2014-11-01 00:27:08.717,104032100000000,298,0,34,0,codeA
2014-11-01 00:27:08.717,104032000000001,1,405,13871,3,codeB

2014-11-01 00:27:08.717,104032000000001,1,298,24082,6,codeC

OPTIONS (ERRORS=1000000)

LOAD DATA

INFILE ‘counter_for_load.txt’

BADFILE ‘counter_for_load.bad’

DISCARDFILE ‘counter_for_load.dis’

APPEND 

INTO TABLE MY_COUNTERS_TABLE

FIELDS TERMINATED BY ‘,’

TRAILING NULLCOLS

(ts_last_modified «TO_DATE(SUBSTR(:ts_last_modified,1,19),’YYYY-MM-DD hh24:mi:ss’)»,

 static_id INTEGER EXTERNAL(20),

 dyn1 INTEGER EXTERNAL(22),

 dyn2 INTEGER EXTERNAL(22),

 counter_sum INTEGER EXTERNAL(20),

 counter_delta INTEGER EXTERNAL(20),

 counter_name FILLER,

 dyn3 «0»,

 dyn4 «0»,

 dyn5 «0»,

 is_processed «0»

 )

sqlldr userA/passA@orainst control=control_file.ctl log=my_sqlldr.log

Example D — Runtime error SQL*Loader-704:
===========================================
When running sqlldr, there is the following error:
SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified


The TNS ORA-12154 error suggests that the connect identifier is not known.


But when testing that very same connection with sqlplus, there is no error at all, and the connection is OK.
What is going on??!!


Example:
sqlplus userA/passA@orainst


SQL*Plus: Release 9.2.0.1.0 — Production on Mon Dec 1 14:10:39 2014
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 — Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options



SQL>

>sqlldr userid = userA/passA@orainst control=control_file.ctl


SQL*Loader: Release 10.2.0.1.0 — Production on Mon Dec 1 14:10:16 2014


Copyright (c) 1982, 2005, Oracle.  All rights reserved.


SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12154: TNS:could not resolve the connect identifier specified

Solution:

The issue is with Oracle Client installation and Path Environment Variable in Windows.

Apparently, that specific computer had three Oracle Client Installations:

Oracle 9, Oracle 10, Oracle 11.

Oracle 9 tnsnames.ora had the correct service descriptor.

sqlplus was able to connect using the Oracle 9 Client tnsnames.ora

sqlldr issue was resolved only after adding the correct entry to Oracle 10 Client tnsnames.ora file.

But when running sqlldr command without parameters, we get the sqlldr «about» and «help» page.
And sqlldr version is Oracle 10!
Now it is clear, why sqlldr is «looking» into the Oracle10 Client tnsnames.ora file.

>sqlldr
SQL*Loader: Release 10.2.0.1.0 — Production on Mon Dec 1 19:34:19 2014
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,…]
Valid Keywords:


    userid — ORACLE username/password
   control — control file name
       log — log file name
       bad — bad file name
      data — data file name
   discard — discard file name
discardmax — number of discards to allow          (Default all)
      skip — number of logical records to skip    (Default 0)
      load — number of logical records to load    (Default all)
    errors — number of errors to allow            (Default 50)
      rows — number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize — size of conventional path bind array in bytes  (Default 256000)
    silent — suppress messages during run (header,feedback,errors,discards,partitions)
    direct — use direct path                      (Default FALSE)
   parfile — parameter file: name of file that contains parameter specifications
  parallel — do parallel load                     (Default FALSE)
      file — file to allocate extents from
skip_unusable_indexes — disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance — do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued — commit loaded rows when load is discontinued  (Default FALSE)
  readsize — size of read buffer                  (Default 1048576)
external_table — use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows — number of rows for direct path column array  (Default 5000)
streamsize — size of direct path stream buffer in bytes  (Default 256000)
multithreading — use multithreading in direct path
 resumable — enable or disable resumable for current session  (Default FALSE)
resumable_name — text string to help identify resumable statement
resumable_timeout — wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache — size (in entries) of date conversion cache  (Default 1000)


PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is ‘sqlldr
scott/tiger foo’; an example of the latter is ‘sqlldr control=foo
userid=scott/tiger’.  One may specify parameters by position before
but not after parameters specified by keywords.  For example,
‘sqlldr scott/tiger control=foo logfile=log’ is allowed, but
‘sqlldr scott/tiger control=foo log’ is not, even though the
position of the parameter ‘log’ is correct.

У меня есть база данных Oracle, основанная на сервере, и я хочу импортировать данные в таблицы на сервере. Я пытаюсь использовать sqlloadr (sqlldr) для вставки всех данных в базу данных oracle, которая находится на localhost, и в этом случае проблем нет, но проблема возникает, когда я пытаюсь подключить веб-сервер (не localhost).

Команда, которую я использовал в случае localhost:

sqlldr <usrname>/<password> control=table.ctl log=table.log ERRORS=25000

И следующее касается веб-сервера

sqlldr <usrname>/<password>@LABSERVER control=table.ctl log=table.log ERRORS=25000

Где я создал tnsnames.ora следующим образом:

LABSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = <hostaddress>)(Port = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <servicename>)
    )
  )

Но я получил эту ошибку:

SQL*Loader-704: Internal error: ulconnect:OCIServerAttache [0]

ORA-12154: TNS:could not resolve the connect identifier specified

Любая идея будет оценена по достоинству

«где я создал tnsnames.ora, который выглядит следующим образом:»

Похоже, это означает, что вы разместили файл TNSNAMES.ORA с сервера базы данных.

Помните, что вам также понадобится файл TNSNAMES.ORA на клиенте. Это тоже правильно настроено?

По сути, эта ошибка сообщает вам, что с учетом хоста, порта, протокола и имени_службы клиент не смог найти прослушивающую службу Oracle. Убедитесь, что ваше имя хоста допустимо (например, вы можете его пропинговать), что слушатель прослушивает 1521 и что имя_службы в базе данных сервера действительно является указанным вами именем. Вы можете проверить это, введя эту команду в sqlplus:

SHOW PARAMETER service_names

Который сообщает вам, что база данных регистрирует в качестве слушателя.

Для тех, кто, как я, нашел эту ветку.

Одна вещь, которую нужно проверить: права доступа к файлу tnsnames.ora.

Для клиентов на одном компьютере, но не входящих в группу администраторов баз данных, и с правильно установленными ORACLE_HOME и ORACLE_BIN сообщалось об ошибке. Права доступа к файлу tnsnames.ora препятствовали чтению всем; поскольку файл tnsnames.ora не может быть прочитан, 704/12514 выброшено, поскольку имя службы не может быть разрешено.

Из командной строки: chmod 644 $ ORACLE_HOME / network / admin / tnsnames.ora