Load DWH job ERROR: negative substring length not allowed (cmn_get_plain_text_fct)
The Load DWH job might fail when Rich Text fields are included in DWH
This happens when a RTF type attribute is included and the field content at DB side does not contain the closing '%</plain-text>' tag
The bg-dwh.log file contains errors like the following:
ERROR 2023-08-22 09:15:29,774 [dwh_dim_field_mapping - Table input 2] dwh.event (clarity:
[email protected]:session:Load Data Warehouse) Table input 2 - org.pentaho.di.core.exception.KettleDatabaseException:
An error occurred executing SQL:
SELECT CASE WHEN a.record_count > 0 THEN 1 ELSE 0 END record_exists
FROM (SELECT COUNT(1) record_count
FROM DWH_RDM_ROADMAP_ITEM_V src
WHERE (CLARITY_UPDATED_DATE::TIMESTAMP IS NULL OR CLARITY_UPDATED_DATE::TIMESTAMP >= to_timestamp('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'))) a
ERROR: negative substring length not allowed
Where: PL/pgSQL function cmn_get_plain_text_fct(character varying) line 8 at assignment
at org.pentaho.di.core.database.Database.openQuery(Database.java:1776)
at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:242)
at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: ERROR: negative substring length not allowed
Where: PL/pgSQL function cmn_get_plain_text_fct(character varying) line 8 at assignment
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:244)
at org.pentaho.di.core.database.Database.openQuery(Database.java:1765)
... 4 more
.....
2023/08/22 09:15:30 - User Defined Java Class.0 - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : Unexpected error
2023/08/22 09:15:30 - User Defined Java Class.0 - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_RDM_ROADMAP_ITEM
2023/08/22 09:15:30 - User Defined Java Class.0 - at Processor.processRow(Unknown Source)
2023/08/22 09:15:30 - User Defined Java Class.0 - at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1019)
2023/08/22 09:15:30 - User Defined Java Class.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/08/22 09:15:30 - User Defined Java Class.0 - at java.base/java.lang.Thread.run(Thread.java:829)
2023/08/22 09:15:30 - dwh_etl_interface_dim_pg - Transformation detected one or more steps with errors.
2023/08/22 09:15:30 - dwh_etl_interface_dim_pg - Transformation is killing the other steps!
2023/08/22 09:15:30 - dwh_etl_interface_dim_pg - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : Errors detected!
