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.
- 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.
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 ?
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.
Loudly announcing something is true and finding out you're wrong makes you feel foolish.
Finding out you're wrong and refusing to admit it makes you LOOK foolish.
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?
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
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.
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.
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?
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 ?