Home » RDBMS Server » Networking and Gateways » HS ODBC set isolation level when reading from ext non oracle db
HS ODBC set isolation level when reading from ext non oracle db [message #244592] Wed, 13 June 2007 08:19 Go to next message
jimbojim
Messages: 2
Registered: June 2007
Junior Member
Oracle 10G

Hello,

I have a database link set up using Heterogeneous Services ODBC.

here are the initodbc.ora details

HS_FDS_CONNECT_INFO = "DSN=ser;DB=myDB;UID=123;PWD=xxxx;HOST=mySRV;PORT=600"
HS_FDS_TRACE_LEVEL = OFF

my tns entry is:

ser =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=server)(PORT=1521))
(CONNECT_DATA=(SID=myser))
(HS=OK)
)

This works fine and I can connect and query off of the remote non oracle db fine. However is there anyway to set the default isolation level to read uncommitted?? Im getting a record lock error occasionally so is it possible to perform a read uncommitted or 'dirty read' on a remote non oracle database via a database link using Heterogeneous Services ODBC.

I know oracle does not support this type of transaction level but is it poosible when reading from a remote non oracle DB??

thanks and appreciate any help!!

Jimbo
Re: HS ODBC set isolation level when reading from ext non oracle db [message #281762 is a reply to message #244592] Mon, 19 November 2007 04:45 Go to previous messageGo to next message
joemcglynn
Messages: 3
Registered: November 2007
Junior Member
Hi,

Did you get anywhere with this issue. I have same problem at moment trying to extract large volume of data from a progress database into oracle.


Cheers,
Joe
Re: HS ODBC set isolation level when reading from ext non oracle db [message #281858 is a reply to message #244592] Mon, 19 November 2007 10:59 Go to previous messageGo to next message
joemcglynn
Messages: 3
Registered: November 2007
Junior Member
cmon , this is a simple one to solve.
Re: HS ODBC set isolation level when reading from ext non oracle db [message #281862 is a reply to message #281858] Mon, 19 November 2007 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Give us the solution.

Regards
Michel
Re: HS ODBC set isolation level when reading from ext non oracle db [message #281968 is a reply to message #244592] Tue, 20 November 2007 03:27 Go to previous message
jimbojim
Messages: 2
Registered: June 2007
Junior Member
There is no way to set or fool oracle into selecting with a uncommitted read even when selecting data from an external database over a DB_LINK

What we did to get around this was to by-pass the odbc driver from oracle to progress all together as any locked columns on the progress side bombed out for us when selecting over the DB_LINK even with us set transaction read only.

We instead used SQLServer as the buffer between ORACLE and Progress. With SQLServer you can set the isolation level to read uncommitted. so we created SQLServer views of progress tables via a SQLServer Linked server and then we select from those views from a DB_Link from oracle to SQLServer.

This seems to be working fine

Thanks
Previous Topic: ORA-12541 TNS No listener with pl/sql developer
Next Topic: TNSNAMES.ORA
Goto Forum:
  


Current Time: Sun Dec 04 06:48:01 CST 2016

Total time taken to generate the page: 0.21929 seconds