BI Publisher Report Error: Caused by: java.io.IOException: Root problem: oracle.apps.xdo.XDOException:No corresponding LOB data found

We were doing some cleanup of BI Publisher reports. Some of reports were importing one sub template. That sub template's name was changed. We modified most of the reports and changed the imported sub template name. In few reports we missed to change sub template's name. When user ran the report they got following error

Caused by: java.io.IOException: Root problem: oracle.apps.xdo.XDOException:No corresponding LOB data found : SELECT L.FILE_DATA FILE_DATA,DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH , L.LANGUAGE LANGUAGE, L.TERRITORY TERRITORY , B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY DEFAULT_TERRITORY , B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE, B.USE_ALIAS_TABLE USE_ALIAS_TABLE , B.START_DATE START_DATE, B.END_DATE END_DATE, B.TEMPLATE_STATUS TEMPLATE_STATUS , B.USE_ALIAS_TABLE USE_ALIAS_TABLE, B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME , B.DATA_SOURCE_CODE DATA_SOURCE_CODE , L.LOB_TYPE LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE L.APPLICATION_SHORT_NAME= :1 AND L.LOB_CODE = :2 AND L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND L.LOB_CODE = B.TEMPLATE_CODE AND (L.LOB_TYPE = 'TEMPLATE' OR L.LOB_TYPE = 'MLS_TEMPLATE') AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4) OR (L.LANGUAGE = :5 AND L.TERRITORY = :6) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND L.TERRITORY= B.DEFAULT_TERRITORY ))

The error message has sql statement that caused the error and bind variables too were displayed. When i ran failed sql statements using bind variables displayed in OPP log file, sql was returning one row. I could not figure out the reason for failure. So i opened the rtf template and i noticed the import statements towards the bottom of the rtf template. i checked the properties and found old sub template name. I corrected the sub template name and report worked fine.

The sql statements shown in OPP log file was used for sub template too but bind variable information for sub template was not displayed in the OPP log file. Bind variable information was displayed for main report template. It seems Oracle development wanted to display enough information for debugging but they were displaying irrelevant bind variable information. If they had displayed correct values of bind variables that caused sql statement to fail, it would have been 30 second job to figure out root cause.