Error while connecting to Oracle Server from Instant client through CMD prompt, but SQL Developer connects?

Question

I am using Client provided laptop (windows XP professional 2002) and do not have administrative privileges (hence cannot st any required environment variables as well):

SQL Developer is provided and I am able to access the oracle server xaal5@:1521/ from SQL developer,

I need to use SQL LOADER to perform some heavy loading. I decided to download the instant client to see if I can save some time while my oracle client installation requests takes ages to go through approvals.

I went to the link: http://www.oracle.com/technetwork/topics/winsoft-085727.html for Windows and downloaded the following Instant Client Downloads for Microsoft Windows (32-bit)

Version 10.2.0.4

Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications Download instantclient-basic-win32-10.2.0.4.zip (34,742,358 bytes)

*Instant Client Package - SQL*Plus: Additional libraries and executable for running SQL*Plus with Instant Client Download instantclient-sqlplus-win32-10.2.0.4.zip (725,418 bytes)

Extracted these two zip files into C:\instantclient-win32 folder, got the following files (Yes there is no SQL LOADER in it , but i decided to first set the oracle client up and then look for SQL LDR)

sqlplus.exe oci.dll ocijdbc10.dll ociw32.dll orannzsbb10.dll oraocci10.dll oraociei10.dll Orasqlplusic10.dll classes12.jar ojdbc14.jar glogin.sql

I Ran the following CMD prompt of windows machine:

sqlplus xaal5@:1521/SID

and got the following error :

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

I created a tnsnames.ora file with following entry:

PLMS =  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXX)(PORT = 1521))
 (CONNECT_DATA =
  (SID = sblcusdv)
 ))

and set up a USER VARIABLES to point to the path of tnsnames.ora file (in the Environment variables window) , as i cannot create a system variable. $TNS_ADMIN=C:\instantclient-win32

I Ran the following CMD prompt again:

sqlplus xaal5@:1521/SID

and got the following error, again :

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

What am i doing wrong !! or is there another way to get SQL loader working on my system considering the constraints (long wait for approvals for oracle client installation).

Answer 1

When you use this format:

sqlplus xaal5@HOST:1521/SID

... you're using the 'easy connect' syntax, bypassing the tnsnames.ora and connecting to the service name SID - I assume you're actually using the SID from your TNS file, /sblcusdv. The SID and service name are not always the same thing, and the error indicates they are different in this environment.

To use your tnsnames.ora entry, use this syntax:

sqlplus xaal5@PLMS

That is, with the TNS alias you defined. If you don't want to use that file you need to know the service name. From the server you can use lsnrctl services to see which service names are registered, but you might not have access. If you have permissions you can do show parameters service_names from SQL*Plus (while connected via TNS), or you can query the performance views to get that information. But your account may not have access to that information either. In which case you'd need to ask your DBA. (I'm also assuming SQL Developer is connecting by SID, but it's possible it is using the service name, so you can just copy that).

sqlplus xaal5@//HOST:1521/SERVICE_NAME

You can't use SQL*Loader from the imstant client though, so you still have to wait for the full client to be installed. You might want to look at external tables as an alternative to SQL*Loader; then you could stick to instant client (or indeed SQL Developer). But you'd need to be able to get the data files into a DB-recognised directory on the server, which might haveits own issues.

Answer 2

You have to use the Description as stated.

sqlplus Username/Password@description

In your case:

sqlplus Username/Password@PLMS

This should be sufficient, no need to mention hostname or Port.

In case it doesn't work, check if your System variable PATH contain the oracle installation folder at first place. And when you have Oracle DB and Oracle Client installed on same machine, look if both TNSNAMES have the connection you looking for.