Skip to main content
Question

Oracle Upsert not working

  • November 28, 2024
  • 7 replies
  • 122 views
stu_home
Contributor
Forum|alt.badge.img+5

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

 

 

7 replies

bwn
Evangelist
Forum|alt.badge.img+26
  • bwnEvangelist
  • Evangelist
  • November 28, 2024

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.

stu_home
1 person upvoted this
  • stu_home
    stu_home
s.jager
Influencer
Forum|alt.badge.img+16
  • s.jagerInfluencer
  • Influencer
  • November 29, 2024

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...
stu_home
1 person upvoted this
  • stu_home
    stu_home
stu_home
Contributor
Forum|alt.badge.img+5
  • stu_homeContributor
  • Author
  • Contributor
  • November 29, 2024

​@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.

geomancer
Evangelist
Forum|alt.badge.img+47
  • geomancerEvangelist
  • Evangelist
  • November 29, 2024

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.

s.jager
stu_home
2 people upvoted this
  • s.jager
    s.jager
  • stu_home
    stu_home
stu_home
Contributor
Forum|alt.badge.img+5
  • stu_homeContributor
  • Author
  • Contributor
  • November 29, 2024

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?

geomancer
Evangelist
Forum|alt.badge.img+47
  • geomancerEvangelist
  • Evangelist
  • December 2, 2024

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.

aurorarosalie
Contributor
Forum|alt.badge.img+1

Did you get the solution?

Data Recovery Software

Reply


Helpful Members This Week

Community Stats

30,940
Posts
117,250
Replies
38,741
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.