Skip to main content
  • EN

Hi,

I need to use Upsert to either Update or Insert features into an Oracle database.

If I use Insert to add values it works a treat, so the connection to the Oracle tables is good.

As the Oracle tables are large, when adding the writer I use Import from Dataset…
 

This picks up the format of the date fields and all sorts. It works well for initially inserting the data.

However, when dealing with deltas, which might be Inserts or Updates I want to use Upsert. I can test it on a filegeodatabase and it works perfectly. 

Taking that knowledge into the Oracle process like this:
 

I use the NODE_ID as this is the unique identifier.

However, this fails.

So I have followed the guidance to implement fme_db_operation, I have created a new attribute field of that name, and used a FeatureReader, FeatureMerger, and then AttributeCreator to define whether the feature is classed as INSERT or UPDATE. All works well in the workspace, and a manual / visual comparison between Workbench and the Oracle table concludes that the values in fme_db_operation are correct.


However, when executing either the UPSERT or the fme_db_operation it fails to write.

The error code is thus:
Execution of statement `UPDATE "FME_RW"."XXWACS_LINEAR_LOC" SET "VERSION" = :"VERSION", "CHAR_TYPE_CD" = :"CHAR_TYPE_CD", "COST_CENTER_CD" = :"COST_CENTER_CD", "PERCENTAGE" = :"PERCENTAGE", "COSTCENTRE" = :"COSTCENTRE", "NODE_ID_TYPE_FLG" = :"NODE_ID_TYPE_FLG", "W1_ID_VALUE" = :"W1_ID_VALUE", "IDENTIFIER" = :"IDENTIFIER", "DESCR100" = :"DESCR100", "NODE_TYPE_CD" = :"NODE_TYPE_CD", "BUS_OBJ_CD" = :"BUS_OBJ_CD", "NODE_DPOS_FLG" = :"NODE_DPOS_FLG", "W1_OPERATION_FLG" = :"W1_OPERATION_FLG", "PARENT_NODE_ID" = :"PARENT_NODE_ID", "CRITICALITY_FLG" = :"CRITICALITY_FLG", "LOCATION_CLASS_FLG" = :"LOCATION_CLASS_FLG", "ADDRESS1" = :"ADDRESS1", "ADDRESS2" = :"ADDRESS2", "ADDRESS3" = :"ADDRESS3", "ADDRESS4" = :"ADDRESS4", "W1_CROSS_STREET" = :"W1_CROSS_STREET", "CITY" = :"CITY", "W1_SUBURB" = :"W1_SUBURB", "COUNTY" = :"COUNTY", "STATE" = :"STATE", "POSTAL" = :"POSTAL", "COUNTRY" = :"COUNTRY", "W1_GEO_LAT" = :"W1_GEO_LAT", "W1_GEO_LONG" = :"W1_GEO_LONG", "TIME_ZONE_CD" = :"TIME_ZONE_CD", "HOUSE_TYPE" = :"HOUSE_TYPE", "NUM1" = :"NUM1", "NUM2" = :"NUM2", "IN_CITY_LIMIT" = :"IN_CITY_LIMIT", "GEO_CODE" = :"GEO_CODE", "OWNING_ACCESS_GRP_CD" = :"OWNING_ACCESS_GRP_CD", "SERVICE_AREA_CD" = :"SERVICE_AREA_CD", "WORK_REQ_APPROVAL_PROF_CD" = :"WORK_REQ_APPROVAL_PROF_CD", "BUILDING" = :"BUILDING", "ROOM" = :"ROOM", "POSITION" = :"POSITION", "RCM_SYSTEM_FLG" = :"RCM_SYSTEM_FLG", "RUN_TO_FAILURE_FLG" = :"RUN_TO_FAILURE_FLG", "INV_COST_CENTER_CD" = :"INV_COST_CENTER_CD", "INV_ADJTMT_COST_CENTER_CD" = :"INV_ADJTMT_COST_CENTER_CD", "MARK_UP_COST_CENTER_CD" = :"MARK_UP_COST_CENTER_CD", "STOCK_TRANSFER_COST_CENTER_CD" = :"STOCK_TRANSFER_COST_CENTER_CD", "INV_EXPENSE_CD" = :"INV_EXPENSE_CD", "MARK_UP_EXPENSE_CD" = :"MARK_UP_EXPENSE_CD", "BUYER_CD" = :"BUYER_CD", "RR_APP_REQ_FLG" = :"RR_APP_REQ_FLG", "PUR_DOC_ACTION_FLG" = :"PUR_DOC_ACTION_FLG", "PLANNER_CD" = :"PLANNER_CD", "BACK_LOG_GRP_FLG" = :"BACK_LOG_GRP_FLG", "MAINTMGR_CD" = :"MAINTMGR_CD", "TAX_COST_CENTER_CD" = :"TAX_COST_CENTER_CD", "INVOICE_WRITEOFF_COST_CTR_CD" = :"INVOICE_WRITEOFF_COST_CTR_CD", "TAX_EXPENSE_CD" = :"TAX_EXPENSE_CD", "SERVICE_COND_FLG" = :"SERVICE_COND_FLG", "INVOICE_WRITEOFF_EXPENSE_CD" = :"INVOICE_WRITEOFF_EXPENSE_CD", "NODE" = :"NODE" WHERE "NODE_ID" = :"NODE_ID"' did not succeed; error was `ORA-00932: inconsistent datatypes: expected - got CLOB'. (serverType=`ORACLE8I', serverName=`(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SV900659.volt.local)(PORT = 1521))(CONNECT_DATA = (SID = WACSPRED)))', userName=`FME_RW', password=`***', dbname=`')

The next line of the log reads:
Oracle Writer: Abnormal channel cleanup required, likely due to previous error

 

 

It appears from the error a type of data type mismatch

error was `ORA-00932: inconsistent datatypes: expected - got CLOB

 

You could perhaps run SchemaScanner over the Features to check that it outputs a Schema with Data Types that don’t mismatch the destination Oracle Table.  Like trying to write Characters into a numeric field.

As Bwn says, the error is a datatype mismatch, that is quite clear (most software give you pretty good clues in error messages as to what went wrong these days...). So, a couple of things to check:

  • You are using a Non-Spatial connection. Do those features have geometry, and is that geometry column in that TL;DNR-list of columns? This one may happen when trying to insert geometry as WKT for example.
  • When updating a row, you probably added or changed something in the values of one or more of the attributes. This can cause the datatype of that attribute to change, which may be enough to throw Oracle off. So use the SchemaScanner indeed, and try to match that against the table definition. Look for attributes that contain text - a CLOB is a Character Large OBject, so it’s most likely some attribute that contains too much text, is therefore altered by the writer to CLOB, while the column definition is nvarchar.
  • You say NODE_ID is the unique identifier, but is that also the primary key in the table? Those two are not necessarily the same things...

​@bwn and ​@s.jager many thanks for those pointers. I will take a look at the possibility of a mismatch. When setting up the writers I have used “Import from dataset” to try to prevent a mismatch. I will experiment further.

When a new attribute is created, its Data Type may unintentionally be set to buffer. Buffer is interpreted as CLOB by Oracle.

So the first place to look would be all places where a new attribute is created in the workspace.

Hi again ​@geomancer you always seem to come to my aid!

When writing into the tables using INSERT it works without incident, why would it error if simply figuring out if it is an INSERT or UPDATE using the UPSERT function?

Hi ​@stuarmitagehome, what happens when you try to UPDATE a row?

When searching for upsert and error ORA-00932 I find comments saying 'You can't put a CLOB in the WHERE clause’. And I suspect update (and upsert when updating) uses a where-clause internally.

Maybe you can use someting like TO_CHAR(your_clob_column).

I'm not a specialist on Oracle or upsert though, it's just a hunch based on the search results I got.

Did you get the solution?

Reply


Community Stats

30,234
Posts
114,762
Replies
37,900
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.