Home » SQL & PL/SQL » SQL & PL/SQL » Heterogeneous Services datatype conversion error (10.1.0.5)
Heterogeneous Services datatype conversion error [message #343839] Thu, 28 August 2008 05:55 Go to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Hello all,
I have an oracle database and a sql database. In order to copy some data from one table of the sql db to one table of oracle db, we have a oracle gateway setup on one of the servers. we now have a db link craeted to the the sql db.

to copy data, I am passing a sys refcursor to one of the stored procs in sql db which returns a sql select in it. the datatypes in both the db tables are more or less same. on execution of that sp, i am getting the following error,

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ORDER_SCHEMA.DOTSEND_SALES_STAFF", line 40
ORA-28528: Heterogeneous Services datatype conversion error
ORA-02063: preceding line from OB_INITTG4MSQL_DOTSEND1
ORA-06512: at line 1


i have checked the data on the sql db side and it follows all validations. and this copy of data is actually a job which has been running fine since 3 years. Sudddenly it stopped working. It looks like it is a data issue but it is not. I have checked all data.
Also, i am not understanding as to how the error can come when data is loaded into the refcursor.

I have attached the package for your reference. Please help.


Re: Heterogeneous Services datatype conversion error [message #344047 is a reply to message #343839] Thu, 28 August 2008 12:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
ORA-06502: PL/SQL: numeric or value error: character string buffer too small


This error is pretty clear. It says you are stuffing 10 bytes into a 5 byte field.

SQL> declare
  2     v1 varchar2(1) := 'abc';
  3  begin null;
  4  end;
  5  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2


You need to figure out which variable is getting STUFFED. Try commenting out lines of code till you find it. I suggest you look on or around line 40.

This may be a result of communication issues bewteen the databases or just a mistake in your code.

Good luck, Kevin
Previous Topic: Trick question
Next Topic: Is it safe to use ROWID for update statements
Goto Forum:
  


Current Time: Sat Dec 10 02:55:37 CST 2016

Total time taken to generate the page: 0.11089 seconds