OraFAQ Forum: Networking and Gateways » HS ODBC set isolation level when reading from ext non oracle db

Home » RDBMS Server » Networking and Gateways » HS ODBC set isolation level when reading from ext non oracle db

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

HS ODBC set isolation level when reading from ext non oracle db [message #244592]

Wed, 13 June 2007 08:19

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

Report message to a moderator

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

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

Report message to a moderator

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

joemcglynn
Messages: 3
Registered: November 2007

Junior Member

cmon , this is a simple one to solve.

Report message to a moderator

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Give us the solution.

Regards
Michel

Report message to a moderator

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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 12:30:39 CDT 2025