7

I'm trying to connect to an Oracle database through code (Ruby/DBI, but that's irrelevant) using an EasyConnect connection string. All of the Oracle documentation says to specify the connection string as //hostname:port/service_name. I don't have a service_name for the database that I need to connect to, I have a SID instead. Is there a way to create an EasyConnect connection string using a SID instead of a service name?

My connection code looks like this:

DBI.connect("DBI:OCI8://localhost:9000/the_sid", "username here", "password here")

I keep trying different things, and depending on what I try, I get one of two error messages:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor (DBI::DatabaseError)
ORA-12154: TNS:could not resolve the connect identifier specified (DBI::DatabaseError)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Jon Kruger
  • 4,009
  • 4
  • 33
  • 46
  • Are you sure you need the hostname and port number at all? The error message seems to indicate that you have TNS listener running. If the TNS names are correctly configured, then your connection string might be "DBI:OCI8:the_sid". – Codo Jan 10 '11 at 19:21
  • I don't have the TNS names configured. – Jon Kruger Jan 11 '11 at 12:26

1 Answers1

3

Did you try the SID as the SERVICE name? You can usually specify a SID where a service is asked for. SERVICE_NAMES typically defaults to the SID. At a SQL command prompt, enter:

SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      sid

SQL>

The value(s) in the VALUE column is/are the service name(s) the database is registered as.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • When I run that command from Oracle SQL Developer, I don't get any output (maybe I don't have permission to run that command?) – Jon Kruger Jan 10 '11 at 18:52
  • I believe it's a SQL*Plus command, so you need to run it from there. –  Jan 10 '11 at 19:15
  • 1
    How can I run SQL*Plus if I don't know how to write the connection string to connect to the database? :) Also, this is not a database that I own so if I need privileged rights for any of this, I won't have said rights. – Jon Kruger Jan 10 '11 at 20:06
  • Can you run: sqlplus user@'hostname/sid' and get a prompt for the password? – DCookie Jan 10 '11 at 20:12
  • When I do that, I get this error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA – Jon Kruger Jan 10 '11 at 20:31
  • Can you connect with: sqlplus user@'(description=(address_list=(address=(PROTOCOL=TCP)(HOST=hostname)(port=9000)) (connect_data=(sid=your_sid)))'. – DCookie Jan 11 '11 at 00:22
  • Are you logged into the database server? If so, you can bypass SQL*Net altogether by setting ORACLE_SID=your_sid and running sqlplus user/pw – DCookie Jan 11 '11 at 00:24
  • When I try to connect with the long connection string, I just get the sqlplus usage text (no error messages, not username prompt). – Jon Kruger Jan 11 '11 at 12:25
  • Sorry, syntax issue: sqlplus user/pw@(description=(address_list=(address=(protocol=TCP)(host=theHOST)(port=thePORT)))(connect_data=(sid=theSID))) – DCookie Jan 11 '11 at 15:40
  • When I do that, I get: `ORA-12162: TNS:net service name is incorrectly specified`. It's like it's still trying to connect with a service name even though I explicitly labeled it as a sid. – Jon Kruger Jan 11 '11 at 15:42
  • What OS are you running these commands on? What's your Oracle version? Are you running these commands on the server? – DCookie Jan 11 '11 at 16:05
  • I running on Ubuntu 10.07 (Lucid Lynx). Oracle is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit. I'm not running these commands on the server. – Jon Kruger Jan 11 '11 at 18:31
  • Do you set ORACLE_SID in your environment on the client? If so, unset it. – DCookie Jan 11 '11 at 18:39
  • And, you definitely need to quote that connect string, as the parens are interpreted by the shell. – DCookie Jan 11 '11 at 18:40