Oracle Thin Client Error when Inserting

[ThomasMIP]

Cold Fusion 8 fully patched on Windows Server 2008 fully patched
Oracle 8.1.7.1 on Linux as DB with Network Option (encryption)
Oracle Client 10.2.0.3 Thin Client (tried with ojdbc14 and ojdbc12 classes)

SELECT, DELETE, UPDATE statements all work just fine.

When an INSERT statement is sent to the DB it is processed (the insert is made and committed!) but we are getting a database error message "Typlänge größer als Höchstwert" which translates approximately to typ length is larger than the limit.

Several threads show this as a problem when the return from the db is too large but this is definately not the case (a simple insert of one small row).

The SQL that is run at the DB includes a suffix of "RETURNING ROWID" which is not part of our SQL and is apparently added by CF or the thin client. We do not handle the return value (we tried but that did not help either).

Due to the fact that the INSERT is actually made and committed, we assume the problem must be at the client end and not in the database.

This is a blocking problem and appears to be confined to CF 8 with the Thin Client.
CF 8 with an ODBC connection to the same DB works OK.

Be very glad for any help as we want to go to production with this environment.

Cheers
Thomas

[paross1]

Going into production with such a de-supported Oracle version? Good luck with that.

I suspect your 10.2 client, but since we're using 10gR2 and haven't used 8i for years, I have no way of duplicating your problem. Have you looked in OTN at Oracle8i JDBC Drivers? I'm not sure if these drivers would even be compatible with CF 8, but anything beats having to settle for ODBC if you can help it.

Phil

[ThomasMIP]

Hi Phil,

>>Going into production with such a de-supported Oracle version? Good luck with that.
Agreed. But we don't have the "small change" necessary for an upgrade to 10g (11) Enterprise with Network Option Pack. Sad but true.

The 10.2 Client works with ASPNET in OLEDB without problems (and has for years). This is why I suspect that the interaction of CF with the 10g Client (when working with 8i) has a problem. There is a compatability modus in the thin client for 8i ( -Doracle.jdbc.V8Compatible=true ) which makes the date format work correctly. I would have hoped it would take care of the rest too.

>>anything beats having to settle for ODBC if you can help it
Also agreed. That's why we are fighting so hard to find a solution.

It has not been possible to install Oracle 8xx Drivers cleanly in Windows Server versions since Windows 2003 Server, and we're certainly not going back to Windows 2000!! Or did you mean something else on OTN?

Thanks for your thoughts.

Cheers
Thomas

[paross1]

I noticed in LiveDocs for cfquery for CF 8 that the result parameter is a little different than described for CF7 in that it mentions the ROWID being returned for Oracle insert statements.

What happens when you try including a result parameter (even if you don't end up using the result_name.ROWID variable)?

result Optional Name for the structure in which cfquery returns the result variables. For more information, see Usage.

The cfquery tag also returns the following result variables in a structure. You can access these variables with a prefix of the name you specified in the result attribute. For example, if you assign the name myResult to the result attribute, you would retrieve the name of the SQL statement that was executed by accessing #myResult.sql#. The result attribute provides a way for functions or CFCs that are called from multiple pages, possibly at the same time, to avoid overwriting results of one call with another. The result variable of INSERT queries contains a key-value pair that is the automatically generated ID of the inserted row; this is available only for databases that support this feature. If more than one record was inserted, the value can be a list of IDs. The key name is database-specific.

result_name.ROWID Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.

I have a hunch that there are differences between Oracle 8i and 10g in how the rowid value is being "returned".

Phil

[Guest]

I believe the rowid may be being returned due to CF8's support of auto-generated keys. This is a new feature of CF8. It is always on with CF8.0 or configurable in CF8.01. A hot fix/technote will soon publish that has a patch for CF8.0 to allow you to configure the DSNs to turn off autogeneratedkeys. It also describes the method to use for CF801. The method is the same for 8 and 801, but you need the patch for cf8.0.

The technote published as, " Patch Available to Disable Auto-generatedkeys in Datasources - ColdFusion 8.0, 8.0.1". Published May 1, 2008.

[ThomasMIP]

@ Phil - Yes, we had tried by handling the result set but that doesn't help either.

@ All - We are working on this offline with Ken at the moment. When we have a solution we will post it here.

Cheers
Thomas

[Guest]

Hello Thomas,
I have submitted bug 71828 on the error we get using the Ora 10.2 thin driver against Oracle 817:
Stack Trace (click to expand)
at cforathinTest2ecfm1893475648.runPage(E:\ColdFusion8\wwwroot\test\orathinTest.cfm:11) at cforathinTest2ecfm1893475648.runPage(E:\ColdFusion8\wwwroot\test\orathinTest.cfm:11)
java.sql.SQLException: OALL8 is in an inconsistent state
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.T4C8Oall.marshal(T4C8Oall.java:352)

We can continue to work this in the forums. Or you can open an actual support incident.

I would suggest trying the Oracle JDBC fat client against this since you have the client already setup. Hopefully this will provide a reasonable workaround.

I have not been working this. ColdFusion support plans, including single paid incidents are documented at http://www.adobe.com/support/programs/coldfusionstd/?tab:contact=1. The options include single paid incidents.

[Guest · Accepted solution]

I am able to use the 10.2 Oracle OCI or 'fat client' JDBC driver to make updates and deletes to my 8.1.7 database. Only a few changes are required with the Oracle client already installed. You must update the Coldfusion8\runtime\bin\jvm.config java.library.path to include the Oracle client dll directory. For me it was:
java.library.path=...,E:/Oraclient_10_2
You must restart coldfusion for all java configuration updates.
You must also change the JDBC URL from jdbc:oracle:thin:... to jdbc:oracle:oci:...

You may also still use the Oracle thin client where it works, using the OCI driver only where needed. You could test your SQL with both drivers to see if you note any significant performance difference. Use what works best.

[ThomasMIP]

Hello Ken,

thank you for your help. We have now set up the fat client and will be testing it's reliability. At least the inserts do work now. Previous attempts to use the fat client have not worked, but that was with older versions of the oracle client and CF.

Thanx Thomas