Data Deploy CLOB column

We have a column with data type as CLOB to store large contents from textarea item with tiny mce. When we try to update the contents data deploy fails with the following error:

DBD: *******************************************************
DBD: SQLException occured in TDbSchemaGroupCfg
DBD: Exception Message: ORA-00932: inconsistent datatypes: expected - got CLOB

DBD: Vendor Error Code: 932
DBD: SQL state: 42000
DBD: *******************************************************
DBD:
DBD: *******STACK TRACE*************
DBD: ERROR:
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement

The versions we are using is:

Data Deploy 7.3.2

uses ojdbc14.jar (Types 2 and 4)

Specification-Title: "Oracle JDBC driver classes for use with JDK1.4"

Specification-Version: "Oracle JDBC Driver version - 9.0.2.0.0"

Specification-Vendor: "Oracle Corporation" .

Any help with this will be highly appreciated.

Find more posts tagged with

Comments

Rick Poulin

http://lmgtfy.com/?q=ora-00932+expected+-+got+clob

The very first link has an answer that says CLOBs can't be used in WHERE clauses of SQL statements. In DataDeploy speak, that means make sure that you're not using that column as a primary/foreign key, and make sure you're using real-update.

Nakul

Hi Mark,

The CLOB column is neither a Primary Key or Foreign Key. The table only has one Primary Key column and that is ContentID.

Regards,

Nakul

Adam Stoller

Are you sure that's the schema that's failing?

There isn't any CLOB in there at all.

Or, perhaps, you need to look at the actual DB's schema and contrast that against what you're using for DD?

Rick Poulin

You seem to have glazed over the suggestion about 'real-update', which very well might be the fix here (once you find the correct DD config, per ghoti's post).

Also, for the record, I'm not actually Mark Twain.

Nakul

I am sorry!! I attached old file dbschema. The correct file is attached now.

I couldn't get it working in the same table. But when i create a separate table for just CLOB column alongwith ContentID column it works fine . ContentID is the primary and foreign key for the second table.

Thanks and Regards,

Nakul

Adam Stoller

(1) Try the real-update as per @rpoulin

(2) If that still doesn't work - extract the table schema from the DB and compare/contrast that with your DB schema (as per @ghoti :-))

Nakul

I have added real-update=yes but that doesn't do the trick either. It still fails with the same error.

When i checked the schema in the database it does recognize the column as CLOB too.

When we use CLOB does it have any restrictions on the number of characters ?

Untitled.png

Rick Poulin

Provide the full log so we can see what DD is trying to do.

As for the character limit, it varies based on your DB and version, but that's what Google is for. Or ask your DBAs.

Untitled.png

Nakul

Attached is the full log and also the Data Deploy config.

Nakul

Seems the issue is because of the following:

DoesRowExist Smiley Frustrated ELECT COUNT(*) FROM AIA_PanelSection WHERE ContentId = ? AND PanelSectionHeader = ? AND PanelHeader = ? AND PanelSectionSummary = ?

In this step the CLOB column is used in WHERE clause which is not allowed by oracle.

Any ideas how do we get rid of it ?

Rick Poulin

I thought the real-update would make the 'doesrowexist' go away. I can't find anything wrong with your config, but I do find it interesting that the CLOB in the 2nd level (AIA_Panels) worked but the one in the 3rd level (AIA_PanelSection) didn't.

Unless anybody else can find something wrong with your config, my suggestion at this point would be:

* replace the composite keys with a single-column PK; or

* flatten your schema so you don't have more than one level of nesting (historically, nested replicants have been a pain in the **** for DD.. sometimes if all the groups are children of the root group it solves some odd issues)

Nakul

Could this be because of some caching? In the logs i see:

DBD: Row map tree for group [MarketingPanels] built.
DBD: TTableSchemaHelper object for [AIA_Panels] found in cache.
DBD: Validating cached connection...
DBD: Cached connection OK

Also changing to just single PK wont't help as the combination of the two columns creates a unique recoed for the row.

new_report2.rptdesign

Rick Poulin

Nakul wrote:
Also changing to just single PK wont't help as the combination of the two columns creates a unique recoed for the row.

It might actually help DD if you made it a single column key, by creating a new column and then adding a uniqueness constraint on your two existing columns. Long story short, composite columns haven't been DD's strong suit either, historically speaking.

Both suggestions I made above require changes to your DB schema, mainly to deal with DD quirks.

Snap 2015-11-24 at 11.37.17.png