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
mastek_userAsked:
Who is Participating?
 
mastek_userConnect With a Mentor Author Commented:
We had to fire alter session close database link dblinkname before firirng the insert statment.
This resolved the issue.
0
 
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:
no
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.