After The Datatype of FND Attachment Tables Is Changed, The COLLECTION Complete Refresh Fails with ORA-00932 for Notes(Attachments) (Doc ID 2140178.1)

Last updated on JANUARY 03, 2021

Applies to:

Symptoms

ERROR
When Planning Data Pull targets the "Note(Attachments)" with Complete Refresh, Planning Data Pull Worker finishes with error.

Error statement in concurrent log are as follows:

06-APR 03:47:04 : Long Text sql stmt - INSERT INTO MSC_ST_LONG_TEXT (
MEDIA_ID, LONG_TEXT, SR_INSTANCE_ID, REFRESH_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) SELECT
MLT.MEDIA_ID, TO_LOB(MLT.LONG_TEXT), :v_instance_id, :v_refresh_id,
MLT.LAST_UPDATE_DATE, MLT.LAST_UPDATED_BY, MLT.CREATION_DATE, MLT.CREATED_BY,
MLT.LAST_UPDATE_LOGIN FROM MSC_AP_LONG_TEXT_V MLT
06-APR 03:47:04 : Error pulling data from MSC_AP_LONG_TEXT_V to MSC_ST_LONG_TEXT.
06-APR 03:47:04 : ORA-00932: inconsistent datatypes: expected LONG BINARY got CLOB
06-APR 03:47:04 : ORA-00932: inconsistent datatypes: expected LONG BINARY got CLOB
06-APR 03:47:04 : Error_Stack...
06-APR 03:47:04 : ORA-00932: inconsistent datatypes: expected LONG BINARY got CLOB
06-APR 03:47:04 : Error_Backtrace...
06-APR 03:47:04 : ORA-06512: at "APPS.MSC_CL_PULL", line 7039 ORA-06512: at "APPS.MSC_CL_PULL", line 1805

Steps to Reproduce

The issue can be reproduced at will with the following steps:

1. Run a complete refresh of collections for note(Attachment)
   Responsibility : Advanced Supply Chain Planner
   Navigation: Collections -> Oracle Systems -> Standard Collection
   
   Parameters:
    Collection Method: "Complete Refresh"
    note(Attachment): "Yes"
   
   
2. After submitting the reqest set,  Planning Data Pull Worker finishes with error

Changes

 Changed the Datatype of FND_DOCUMENTS_LONG_TEXT from LONG to CLOB.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.