Why can't I connect via jdbc using SQLcl

Question

I can connect to a remote database using SQLDeveloper.

I am trying to connect to the same database using sqlcl from the command line, but I am getting an error.

Here is the command that I am running:

/bin/sql username/[email protected]:1521/vdbsl14 

I have also tried:

/bin/sql username/pass@//delphix.......etc.

Here is the error I am receiving:

SQLcl: Release 4.2.0.16.131.1023 RC on Wed Jun 15 11:36:33 2016

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

  USER          = username
  URL           = jdbc:oracle:thin:@delphix-vdb-n-1.va2.b2c.nike.com:1521/vdbsl14
  Error Message = Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
Username? (RETRYING) ('username/*********@delphix-vdb-n-1.va2.b2c.nike.com:1521/vdbsl14'?) 

Also in SQLDeveloper, I just enter the following under "custom jdbc url" and it connects without any issue, so I was hoping I could connect via the command line using the same URL but so far, it is not working:

jdbc:oracle:thin:@delphix-vdb-n-1.va2.b2c.nike.com:1521:vdbsl4

Perjured

Answer 1

It looks like your SID and service name are not the same. In SQL Developer you seem to be using the SID - at least in the custom JDBC URL you showed - as denoted by the colon in :vdbsl4.

Your SQLcl URL is using the service name, as denoted by the slash in /vdbsl14. Using the SID instead (i.e. changing the / to :) in that URL should work since it's using JDBC:

sqlcl username/[email protected]:1521:vdbsl14

Alternatively (and preferably, in my opinion) find out what your service name actually is. If you have sufficient privileges on the database you can do show parameters service_names from SQL Devleoper, or if you have access to the server as DBA you can do lsnrctl services, or even look at the tnsnames.ora in case there is a TNS alias defined that shows the service name. (listener.ora isn't likely to help, but could give hints or if you're lucky show a default service name).

You can use that service name in a JDBC URL, as /service_name.

You can also use a TNS alias from SQLcl (or SQL*Plus). You may already have a tnsnames.ora available; if not you might be able to copy it from your server, or create your own. That can refer to the SID or the service name.

You can even pass a full TNS description to SQL*Plus (not sure about SQLcl) but that's a bit unpleasant. If you don't have/want a tnsnames.ora you can use 'easy connect' syntax, which is the same as you're using for SQLcl - but that has to be the service name, it doesn't allow SIDs.

Romaine

Fixing the SID as you suggested worked. I can finally connect after 3 weeks of suffering, thanks! – Perjured

I'd still try and find out the service name and use that, but maybe that's just what I'm used to... – Romaine

@AlexPoole - point of order: checking the listener.ora and/or tnsnames.ora may or may not yield anything. The listener can quite easily run with all default values with no listener.ora at all, depending entirely on dynamic registration from the databases. They are worth checking for potential clues, but certainly not definitive. But perhaps that's what you had in mind. – Romulus

@Romulus - yes indeed, and the listener,ora wouldn't normally list service names anyway (except maybe a default). And then there's the domain info from sqlnet.ora... so yes, hints at best, and more likely from tnsnames.ora. Thanks. – Romaine