Home » SQL & PL/SQL » Client Tools » Can connect via Java's DriverManager.getConnection, but not SQLPlus (Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 on Solaris)
Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403043] Wed, 13 May 2009 09:55 Go to next message
ShiningMasamune
Messages: 6
Registered: May 2009
Location: New Jersey
Junior Member

Greetings all. I'm having trouble connecting to my database with SQLPlus. The Java program I'm working on connects to it just fine with Java's DriverManager.getConnection, but I don't know how to convert the parameters of that function into what SQLPlus needs. I'm not sure if my project manager will approve of me giving out our database url, so hopefully this will suffice: The "url" param of getConnection is of the form

jdbc:oracle:thin:@xxxx.yyyy.edu:1521:zzzz

So I tried the following in SQLPlus
SQL> connect user@\\xxxx.yyyy.edu:1521\zzzz
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

I don't have access to any files on the computer oracle runs on, all I have is the Java code. I even went as far as to write my own client in Java for basic queries, but that only takes me so far and it's getting pretty annoying. Help?

Client OS is Windows, if it matters.
Re: Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403065 is a reply to message #403043] Wed, 13 May 2009 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You have at least 2 options.
1) create a valid tnsnames.ora file on your windows client.
2) Discover how to properly format the CONNECT_IDENTIFIER (the string to the right of the at-sign("@)).

I know #2 exists, but a quick SEARCH by me did not find URL with actual valid syntax.

Good Luck!
Re: Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403093 is a reply to message #403043] Wed, 13 May 2009 12:52 Go to previous messageGo to next message
ShiningMasamune
Messages: 6
Registered: May 2009
Location: New Jersey
Junior Member

I've created a tnsnames.ora file with the following content (with x/y/z's replaced with their actual values in the above Java url string)
MYDB =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.yyyy.edu)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = zzzz)
 )
)

yet it still doesn't seem to work:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Re: Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403096 is a reply to message #403093] Wed, 13 May 2009 13:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Still, you are one step further along.

You now have connected the listener at xxxx.yyyy.edu, and that listener tells you it doesn't know the service zzzz.

Either zzzz is not really the service name, or the database is down.
Re: Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403097 is a reply to message #403043] Wed, 13 May 2009 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Troubleshooting SQL*Net can be challenging & even more so when no access directly to the DB server itself exists.

You likely have a file on the client call sqlnet.ora.
If so please post contents here.

Oracle's SERVICE names can be impacted by what is call GLOBAL_NAME

As a possible solution change
(SERVICE_NAME = zzzz)
to be
(SID = zzzz)

On the database server system a process called the LISTENER exists
I know it is working since access via jdbcthin succeeds.
The LISTENER process maintains a file called listner.log

This file logs every connection request it get & indicates whether or not the request succeeded in connecting to DB or error code why it failed.

It would be helpful if you could confirm that your errors are appearing to confirm you are getting to the desired listener.

Can you obtain a copy from some independent source a known working tnsnames.ora file?

Good Luck!
Re: Can connect via Java's DriverManager.getConnection, but not SQLPlus [message #403098 is a reply to message #403097] Wed, 13 May 2009 13:29 Go to previous message
ShiningMasamune
Messages: 6
Registered: May 2009
Location: New Jersey
Junior Member

Aha! Changing SERVICE_NAME to SID did the trick. Many thanks!
Previous Topic: isqlplus
Next Topic: Migrating Pro*PL1 to PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 12:39:51 CST 2016

Total time taken to generate the page: 0.09482 seconds