Home » RDBMS Server » Server Utilities » ora-12514 error (Oracle 10g)
ora-12514 error [message #574540] Fri, 11 January 2013 23:22 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I am trying to upload a csv file into table using sqlloader on client computer, i am getting an error message while giving the ctl file , the error is ORA-12514 , can someone tell me where to store this ctl file and refer it as i am following the below steps.

1.Created the .csv file
2.copying it under home directory where oracle client is installed
3.created the ctl file from text editor and saved it under the name loader1.ctl.
contents of loader1.ctl is as below.


CREATE TABLE EMP_SETTLE (ID_NO VARCHAR2(12), SET_DT VARCHAR2(30));

LOAD DATA

INFILE   'c:/orant/emp_setle.csv'


INTO TABLE emp_settle


FIELDS TERMINATED BY ","




Sample .csv is attached.


Re: ora-12514 error [message #574541 is a reply to message #574540] Fri, 11 January 2013 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member

ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
One cause of this problem is when the Oracle database is down & needs to be started.
A remote client send a request to the Listener asking to be connected to a specific service.
If/when the listener does not know anything about that service, the listener responds with ORA-12514

post results from the following two OS commands:
lsnrctl status
lsnrctl service

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

*This line contains valuable debugging details. So post this line & surrounding lines.*

for additional debugging suggestions read the URL below:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/
Re: ora-12514 error [message #574542 is a reply to message #574541] Fri, 11 January 2013 23:54 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks blackswan for the very prompt response and help,i had one database locally installed and it was on d drive and the one which i was referring to was on remote there was no link, i created one link and then now i am facing the problem like this below.i am also attaching the status files of lsnrctl status and lsnrctl service.If possible kindly tell me what are the exact steps to be followed for uploading the data from .csv to oracle , like where the loader.ctl file should reside.


Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\arif>CD\

C:\>CD ORANT

C:\orant>SQLLDR RAKPAYLIVE/ORION@ORION

control = LOADER

SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jan 12 09:26:25 2013

Copyright (c) 1982, 2007, Oracle.  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

C:\orant>lsnrctl status

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 12-JAN-2013 09:26
:39

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER1
Version                   TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - Produ
ction
Start Date                12-JAN-2013 07:25:42
Uptime                    0 days 2 hr. 0 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.o
ra
Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\listener1.lo
g
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Arif.aicsteel.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "rakdb" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
Service "rakdbXDB" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
Service "rakdb_XPT" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\orant>spool c:\status.txt
'spool' is not recognized as an internal or external command,
operable program or batch file.

C:\orant>spool c:\status
'spool' is not recognized as an internal or external command,
operable program or batch file.

C:\orant>lsnrctl service

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 12-JAN-2013 09:27
:32

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "rakdb" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:53 refused:0 state:ready
         LOCAL SERVER
Service "rakdbXDB" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: ARIF, pid: 2036>
         (ADDRESS=(PROTOCOL=tcp)(HOST=Arif.aicsteel.com)(PORT=60266))
Service "rakdb_XPT" has 1 instance(s).
  Instance "rakdb", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:53 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

C:\orant>set echo off
Environment variable echo off not defined

C:\orant>spool test.sql
'spool' is not recognized as an internal or external command,
operable program or batch file.

C:\orant>SQLLDR rakpaylive/orion@orion

control = loader1.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Sat Jan 12 09:41:43 2013

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

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


Re: ora-12514 error [message #574544 is a reply to message #574542] Sat, 12 January 2013 00:39 Go to previous message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that - if you tried to connect to RAKDB database, you might have succeeded. There's no ORION there ./fa/3314/0/. Finally, there's no LOADER1.CTL file there either.

BTW, what's the point in posting rubbish statements that failed, such as your SPOOL attempts on operating system command prompt; SPOOL belongs to SQL*Plus, not Windows. Redirection works on CMD. Here's an example, so that you'd do that properly next time:
C:\Users\lf>dir *.ctl
 Volume in drive C has no label.
 Volume Serial Number is 2857-9020

 Directory of C:\Users\lf

09.04.2012.  17:03               208 test.ctl
04.05.2012.  18:48               451 test1.ctl
04.05.2012.  22:33               187 test2.ctl
06.06.2012.  21:00               265 test3.ctl
               4 File(s)          1.111 bytes
               0 Dir(s)  19.824.971.776 bytes free

C:\Users\lf>dir *.ctl > output.txt               --> redirection into a file

C:\Users\lf>type output.txt
 Volume in drive C has no label.
 Volume Serial Number is 2857-9020

 Directory of C:\Users\lf

09.04.2012.  17:03               208 test.ctl
04.05.2012.  18:48               451 test1.ctl
04.05.2012.  22:33               187 test2.ctl
06.06.2012.  21:00               265 test3.ctl
               4 File(s)          1.111 bytes
               0 Dir(s)  19.824.947.200 bytes free

C:\Users\lf>
Previous Topic: ROWS Command line parameter
Next Topic: Import to a lower version
Goto Forum:
  


Current Time: Wed Oct 01 04:22:35 CDT 2014

Total time taken to generate the page: 0.11444 seconds