This content has been marked as final. Show 8 replies
I have a customer that wants to take an instance of XE and have it connect to another Oracle 10g instance on a server. They DO NOT want a dblink used to connect the XE database to their 10g database. Is it possible to add an entry to the XE's tnsnames.ora file to see the 10g database and select from it for som ereporting and nothing else?
If the two servers can ping/see each other, then your solution of adding an entry in tnsnames.ora will work.
Adding an entry to the tnsnames.ora file on the XE machine won't allow the XE database to query the 10g database (though it may be a useful initial step).
If you don't want to create the database link, the other options I could imagine
- Create a .Net stored procedure in the XE database that connects to the 10g database and runs a query. I can't imagine why this would be allowed if a database link is disallowed but it is not technically a database link. You could do the same thing with a Java stored procedure but my understanding is that Java stored procedures aren't allowed in XE
- Create a DBMS_SCHEDULER job (or a .Net stored procedure) that calls a script on the XE database that connects to the 10g database (potentially via SQL*Plus) and then load the results into XE
- Create a web service on the 10g database that the XE side can call via UTL_HTTP to extract whatever data is needed
That's exactly how to configure a client. The important bits are:
Is it possible to add an entry [ to the ] tnsnames.ora
1. host (where the database lives)
2. port (how to get to the listener)
3. ORACLE_SID or service_name ( particulars for the instance)
If a database user is set up with the same user name and password in each database, setting up the link is fairly simple. Its much easier to connect via a link than it would be to take a schema export for every target database and load it up in a different instance, unless one likes busy work ;)
The 10g net services doc at http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/part1.htm#i997104 is a good place to get started.
Perhaps I'm missing something. Adding the entry to the tnsnames.ora file would allow an executable on the system where the XE database is running to connect to the 10g database. But it would not allow the XE database to connect to the 10g database without jumping through additional hoops to effectively shell out from the XE system to call one of those external executables. Adding the tnsnames.ora entry would obviously make it easier to create a database link to the 10g system, but that seems to be disallowed.
Agreed - I am assuming the requirement is something as basic as using sqlplus to connect to the remote database.
... Is it possible to add an entry to the XE's tnsnames.ora file to see the 10g database and select from it for som ereporting and nothing else?
If the requirement is something more complex, then the solution would be different :-)
What is required is this:
The customer has a database owned by another company, the other company does NOT want a dblink built in the server that contains the data they want to do reports on. We want to use APEX in teh XE database to generate some reports for end users (Customer is using MS Access to download data and do reports.. Yuck...)
So if I add an entry into the XE's tnsnames.ora and try to connect to the other server and run selects, will this work?
sure you can, we do similar stuff with XE+APEX on Ubuntu and Db server running on a Sparc Box - have shell scripts controlling the flow. We also use the external directories. One system (Oracle) mounts and writes to that, and other can read from the same external directory (check Ask Tom for external directories - he also have a new article in the latest Oracle magazine, which I read yesterday). You can do similar stuff even if you are running XE+Apex on Windows and Oracle DB server of your customer is also on Windows (or Unix).
If you want the XE database to read data from the proprietary database, you would need a database link created in the XE database that points to the proprietary database. From the standpoint of the remote database, an incoming connection is an incoming connection regardless of whether it is coming via a database link. Unless the vendor is being a real pain and is doing something like checking V$SESSION to try to determine whether an incoming connection is from another Oracle database server, that shouldn't create any issues. Now, for basic security reasons, you may want to create a new user that the database link would use to connect to the vendor's database, but that's the only intervention that should be necessary on the vendor system.
Assuming you want to create a database link on the XE system that connects to the vendor's database, having the tnsnames.ora entry on the XE system certainly simplifies the syntax.