hemant_k wrote:

 

hi

is it possibel to have multiple connections of sql databases from oracle?

 

I am using express edition 11 .2

i have multiple dtabases of vaious clients

tried a entry in listner and tnsnames.ora

but not able to connect ...

may be a wierd question

but please help

Please clearly define what you mean by "multiple connections of sql databases from oracle".

Your question isn't weird.  It's simply too vague to know what you are asking.

Please describe the business problem you are trying to solve.

-- Edit

Is this question somehow related to your two recent previous threads?

connect sqlserver to oracle

connect sql server

hemant_k wrote:

 

sir

I have installed express edittion 11.2.0 on my machin .

we are providing hosting service for various clients.

have several clients' databases on one server.

Now while trying to connect sql data through oracle i could access a datbase say db1

now i want to establish connection to db2 for which i created ODBC separetely and added entries in lisner.ora and tnsnames.ora also created intsid.ora

Now the situation is i am able to access data from db2 and not from db1

My idea is to have both the connection live to access data from db1 and db2

is this possible ?

 

please help

 

hemu

Possible?

I don't know. It's still not at all clear WHAT you are trying to do.

Sounds to me like you might have defined a bunch of db_links in your XE database, which is running on your desktop.  And those links point to various non-Oracle databases in your data center.  Further, it sounds like some of those links work and other don't.  Do I have this correct?  If not, then please try again to explain your situation.  Your statement "trying to connect sql data from oracle" is itself very vague.  I know it seems clear to you, but we are not looking over your should to see exactly what it is you are doing, and with exactly what tools, etc. etc.

hemant_k wrote:

 

sir

"Sounds to me like you might have defined a bunch of db_links in your XE database, which is running on your desktop. And those links point to various non-Oracle databases in your data center. Further, it sounds like some of those links work and other don't. "

this is a situation exactly....

I am Trying to deploy reports in Apex with different db_links on different pages....

 

 

Ok, so going back to your question "My idea is to have both the connection live to access data from db1 and db2 is this possible ?"

The answer is yes.  In using db links, it's not really a question of "opening a connection", like you do when you connect with sqlplus or some other database client.  In this case, your XE database is the client to the other databases. So you simply:

select empid, depno from emp_tbl@db1;

select fname, lname, dob from emp_tbl@db2;

Now, going back to your opening post, where you said simply "but not able to connect ...".  That is totally devoid of actionable information.  Oracle is very good about emitting error codes and messages.  If you want help resolving an error, you need to share the error message, in the context of what command created it.

hemant_k wrote:

 

 

result of query

select * from dba_db_links

Ok, so for problems like this we just logically follow a series of links in a chain.

The first link is your failing SQL statement, and we see that it is referencing db link HRK1.

The next link in the chain is db link HRK1 is referencing your tns net service name 'test'.

So for the next link in the chain, show us the tnsnames.ora entry for 'test'.  From there we will know what to look at next.

hemant_k wrote:

 

hi

copy of tnsnames.ora

 

XE =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

 

 

EXTPROC_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = PLSExtProc)

(PRESENTATION = RO)

)

)

 

 

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

)

 

 

test=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=SIL_SW215.saas.SILASPDC.com)

(PORT=1521)

)

(CONNECT_DATA=

(SID=test))

(HS=OK))

 

 

sil_abc_mucs=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(HOST=SIL_SW215.saas.SILASPDC.com)

(PORT=1521)

)

(CONNECT_DATA=

(SID=sil_abc_mucs))

(HS=OK))

Ok, looking at the article I referred you to, what's the next link in the chain after tnsnames.ora?

hemant_k wrote:

 

sir

 

initest.ora

 

 

initsid.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

 

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = test

HS_FDS_TRACE_LEVEL = on

 

 

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

====================================initsil_abc_mucs.ora

 

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

 

 

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = sil_abc_mucs

HS_FDS_TRACE_LEVEL = on

 

 

 

 

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

=======================listner.ora

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

(PROGRAM = extproc)

)

)

 

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1521))

)

)

 

 

#LISTENER1 =

# (DESCRIPTION_LIST =

# (DESCRIPTION =

# (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

# (ADDRESS = (PROTOCOL = TCP)(HOST = SIL_SW215.saas.SILASPDC.com)(PORT = 1522))

# )

# )

 

 

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=test)

(ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

(PROGRAM=dg4odbc)

)

)

 

 

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(SID_NAME=sil_abc_mucs)

(ORACLE_HOME=C:\oraclexe\app\oracle\product\11.2.0\server)

(PROGRAM=dg4odbc)

)

)

===============sqlnet.ora

 

 

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

 

 

SQLNET.AUTHENTICATION_SERVICES = (tnsname)

DEFAULT_SERVICE_LISTENER = (XE)

 

regards

hemu

So did you actually walk through all of the config files while reading the article, or did you just dump them out here?  I gave you the link so you could help yourself.  Without doing the detail check that you should be doing, I can tell you that your listener.ora file is messed up.  Why do you have two LISTENER sections and two SID_LIST sections?