[Oracle][ODBC SQL Server Driver]Invalid parameter number {07009} in accessing from Synonym to MS SQL

[Karthik Rajendiran]

Dear Forum Members,
We would like to get assistance in a strange error occurred after altering a table in MSSQL and inserting to table via DB LInk in Oracle.

Back Ground
- Database Link was established between our Application Database [ oracle to Customer MS SQL server 2008 R2  SP 3 10.50.600
- Synonym was created in Oracle to Isnert/ Select table residing in Remote MSS QL

Setup was working fine last few years, We recently add 3 columns to the MS SQL Server and it si relfecting in Oracle
when we did Desc <SYNONYM>

But while inserting in hibernate, we are getting following.

Caused by: java.sql.SQLException: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver]Invalid parameter number {07009}

AGRICULTURE_INCOME                                 VARCHAR2(40) OTHER_INCOME                                       VARCHAR2(40) FORM60_REF_NO                                      VARCHAR2(40) FORM60_APPLIED_DATE                                VARCHAR2(10 CHAR)


Can anyone throw light in this

[Karthik Rajendiran]

TO add to above, Found one weird
If we insert with less than 100 colums in our Hibernate, it is working, moment 100th column is introducted it is giving.

Tried - Commenting all columns and only add new columsn - Insert - Worked
      - Added 99 columns - Worked
     - Added 100th column  - Gave error
  - Removed a column with 250 Varchar [ thinking if size is the issue] - Same Error

Is there any limit that hibernate can allow only <100 columns only ?

[Tim Holloway]

No, there is no limit. Modern-day software rarely has fixed limits on stuff like that.

Oracle, on the other hand was not designed in the modern day. I'd contact Oracle Support.

I can say from said experience, though, that working with tables with many columns in Oracle itself can be a problem. There's nothing like getting an error message that says something like "Invalid value in colum" and having to find out by trial and error which of 150 columns it was.

[Dave Tolls]

Both SQL Server (the target DB) and Oracle have a max column limit of 1000 (well, I think it's 1024).

Which possibly leaves an issue with the driver?

[Karthik Rajendiran]

Dave Tolls wrote:Both SQL Server (the target DB) and Oracle have a max column limit of 1000 (well, I think it's 1024).

Which possibly leaves an issue with the driver?

Ok,
Following scenarios troubleshooting
Lets say Table in Target DB is  MY_CUST in MSSQL

- Is there any issue source Oracle DB [ Synonym/ Table]
Created a dummy table with 103 coums [ Table, and Synonym was working fine]
- The Problem is only with a synonym created for table@ DBLink , unable to trouble shoot is it Hibernate / Env

Can you guide ,what else can be troubleshooted

[Dave Tolls]

Drop hibernate and test using oracle alone.
You have the Oracle DB with (presumably) a link and synonym with a SQL Server table...after all, that's your error.
So test it in Oracle.
So the same query that is causing your issue in your Java app, but do it directly in the DB.

[Paul Clapham]

Karthik Rajendiran wrote:

Caused by: java.sql.SQLException: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver]Invalid parameter number {07009}

ODBC??? Really??? If you're using SQL Server it seems to me that using the SQL Server JDBC driver would be the way to go. Using Oracle and connecting to SQL Server via ODBC seems like you're just asking for trouble.

[Karthik Rajendiran]

Actually Current architecture, we trying to push a data to other application database in MS SQL via DB LInk
- Our application is using hibernate to push the data to synonym present in Oracle
SYnonym iscreated for table in other database MSSQL

How can we solve in this setup?
Is there any way to enable logs, what is being passed to MSSQL or tcp dump?

[Karthik Rajendiran]

We are using Thin Client -Oracle in hibernate file  to connect to oracle Database [ SYNONYM]

+ Error comes only if the insert has more than 100 columns in insert

Can we able to find ,what query is sent to the MSSQL server via DB LINk?

[Dave Tolls]

As I suggested, forget about Hibernate and Java.
They are currently irrelevant to the problem.

Test the DBlink directly.

If you think it is the 100 columns causing the issue then that should be easy enough to prove.

[Karthik Rajendiran]

As per Dave

We tried direct JDBC Thin Client to connect and insert to the DBLInk and got error when columsn included above or equal to 100 in the synonym
we got the same error
Issue is not about hibernate or mssql , it is about the thin client driver only.

Is it known bug when inserting to a dblink getting invalid parameter ?

[Dave Tolls]

At least you can now ask Oracle a more specific question.

And have you tried directly in Oracle?
Using SQL-Plus?

It may be a DBLink issue.