Win a copy of Zero to AI - A non-technical, hype-free guide to prospering in the AI era this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

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

 
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.






Can anyone throw light in this
 
Karthik Rajendiran
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
Saloon Keeper
Posts: 22634
153
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Master Rancher
Posts: 4699
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Master Rancher
Posts: 4699
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 25930
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Karthik Rajendiran wrote:



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
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Master Rancher
Posts: 4699
49
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Master Rancher
Posts: 4699
49
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Sasparilla and fresh horses for all my men! You will see to it, won't you tiny ad?
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic