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

Posted on 2011-02-16
Medium Priority
Last Modified: 2013-12-19
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
Question by:mastek_user
  • 3
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34912348
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.

Author Comment

ID: 34958547
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

Accepted Solution

mastek_user earned 0 total points
ID: 35025927
We had to fire alter session close database link dblinkname before firirng the insert statment.
This resolved the issue.

Author Closing Comment

ID: 35067610

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

Join & Write a Comment

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question