• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1164
  • Last Modified:

ORA-28500 Oracle to SQL Server Hetero database link for large inserts

i've written a procedure in Oracle which reads records through cursor from SQL server table and insert into another table in sql server. The access of tables is through heterogeneous database link. This works fine for say 100 records but for large no of records i get exception
"ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]
ORA-02063: preceding 2 lines from DBLINKNAME
0
mastek_user
Asked:
mastek_user
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
I'm not a SQL Server person.  I'm responding to a Neglected question alert.

There's not a lot of error messages to work with.  I suggest you contact Oracle Support on this one since no other Expert has shown up.
0
 
mastek_userAuthor Commented:
i'm using Oracle 10g XE(10.2) edition. Another observation the insert succeeds if its run from anonymous block. but fails when run through a procedure
0
 
mastek_userAuthor Commented:
We had to fire alter session close database link dblinkname before firirng the insert statment.
This resolved the issue.
0
 
mastek_userAuthor Commented:
no
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now