0

I tried each of the following - none worked:

[oracle@localhost]$ sqlplus system/oracle@SID_NAME

[oracle@localhost]$ sqlplus system/oracle@localhost:1521/SID_NAME

[oracle@localhost]$ sqlplus system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=SID_NAME)))

[oracle@localhost]$ sqlplus "system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=SID_NAME)))"

Does SQL*Plus support direct connection to an "Oracle database link" at initial command prompt? If so - how?

SID_NAME does not point to a database but "Oracle database link": https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#i1007709

I am able to query the "Oracle database link" after connection to the local Oracle database. The local Oracle database is where the link has been created and points to the remote database. It connects via an ODBC DSN.

However, I am interested in connecting to the Oracle database link directly at initial sqlplus command prompt.

The local Oracle database port is 1521.

The error message I get:

ERROR: ORA-01017: invalid username/password; logon denied

The whole point is to debug (GDB) an ODBC driver. With isql - at command prompt - I can provide the ODBC DSN and when I run info sharedlibrary at GDB prompt - I can see my loaded library and hence debug. I am interested in getting the driver loaded in the same way using sqlplus. I was unable to see the loaded library post connection. Hence - trying to get the library loaded at initial command just like isql.

At what point does sqlplus actually load the ODBC driver for remote database? I know it does when a query is being executed and unloads it on exit. But I am unable to trap it - even though a breakpoint exists.

Kapil Vyas
  • 607
  • 2
  • 8
  • 22
  • 1
    If you have a database link that happens to have the same name as your local SID, where is it pointing, and who is it connecting as? You can't connect to the link itself, but you can connect directly to wherever the link is pointing (unless both DBs are remote and network restrictions let the two DBs communicate but you can only reach one), which *seems* to be what you are actually doing. However...the error message is fairly clear... – Alex Poole May 31 '18 at 23:18
  • My local SID has a different name ("orcl12c") compared to the database link ("SID_NAME"). I can connect to database directly using the ODBC DSN. So sqlplus does not allow connecting to the link directly? – Kapil Vyas May 31 '18 at 23:35
  • SQLPlus doesn't use ODBC, it uses OCI, compiled C binaries, to connect to the database via SQLNet...unless it's on the same machine as the database, and then it can use bequeath – thatjeffsmith May 31 '18 at 23:38
  • What is the localhost doing in the connection string ? If you connect from a PC to a database on a different server and that database then refers to "localhost" it is referring to the server, while the PC's "localhost" is the PC. – Gary Myers Jun 01 '18 at 03:00
  • The simple answer is the ODBC driver never gets loaded. So not possible to debug this way. – Kapil Vyas Jul 10 '18 at 22:18

2 Answers2

1

One does not connect to a Database Link.

A database link tells Database A how to get to Database B.

Assuming you have the DB_LINK definition, you could use the same details to connect directly to database B.

So in a way, yes SQL*Plus can connect.

Your connection string is saying, connect me to a database called SID_NAME, that's running on MY computer, via port # 1521.

The error message is telling you that the username and password combination are incorrect. So you're 90% of the way there - you just need the right 'key' to open the lock.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • 2
    Also, a 'database link' is not what you think it is. A database link is a connection database A can make to database B so one can query data from between the two instances. – thatjeffsmith May 31 '18 at 23:01
  • So then how do I debug (GDB) the driver to uncover what sqlplus does? I am able to see what ODBC calls isql makes. I can also see the ODBC logs when I query using the Oracle DB links. However, I am unable to debug sqlplus. – Kapil Vyas Jun 01 '18 at 04:51
  • @KapilVyas your plan is flawed - using SQL*Plus to debug ODBC doesn't make any sense, because SQL*Plus doesn't USE ODBC. It would make more sense to create an ODBC data source in Excel or Access and 'test' your ODBC connection there. – thatjeffsmith Jun 01 '18 at 10:39
0

SQLPlus talks to a remote database using Oracle DB Link. SQLPlus does not directly make ODBC calls. SQLPlus talks directly to the database. Databases talks to each other via DB Links, in this case, Oracle DB Link through "Oracle Heterogeneous Services (HS)". ODBC driver "never" gets loaded by SQLPlus, thus, there is no way to debug the driver using this method. Also, neither does the database that SQLPlus connects to is loading the ODBC driver. It uses OCI/C code just like SQLPlus does to connect to another database.

Kapil Vyas
  • 607
  • 2
  • 8
  • 22