Remove multiple Ascii characters from input data
Posted: Wed Sep 06, 2017 12:21 pm
Hi,
I have a requirement to remove multiple ascii characters from the input data.
Eg- input data has below values
column name - Description
==================
Paratransit¿Web-Based¿Trip¿Boo
TRE DFW ROW Maintenance Tie Re
Input data length - char (30)
Output data length - Varchar(30)
I am getting an error 'A data conversion error was encountered in bulk load mode for row '
1)Transformation rule - Convert(char(160):char(168):char(194),'',TrimB(TrimF(NullToEmpty(In_ACACTIVITY.DESCRIPTION)))- error as above
2) CONVERT(CHAR(194), "", CONVERT(CHAR(191), "", Convert(char(160),'',TrimB(TrimF(NullToEmpty(In_ACACTIVITY.DESCRIPTION))))))-- error as above
3) When I changed the datalength to 40 everything got loaded.But not allowed to do so.
4)I changed the bulk load to write mode as 'Insert' and table action to
'Create' in target oracle stage and getting error of 'Error code: 12899, Error message: ORA-12899: value too large for column "MSPO_TEST"."DESC_PROJECTS2"."DESCRIPTION" (actual: 34, maximum: 30).
How should I remove the multiple ascii characters from the input data? I am able to remove one ascii character char(160) using the Ereplace function (which I posted in the forum).
Please let me know what is the mistake I am doing. Thanks.
I have a requirement to remove multiple ascii characters from the input data.
Eg- input data has below values
column name - Description
==================
Paratransit¿Web-Based¿Trip¿Boo
TRE DFW ROW Maintenance Tie Re
Input data length - char (30)
Output data length - Varchar(30)
I am getting an error 'A data conversion error was encountered in bulk load mode for row '
1)Transformation rule - Convert(char(160):char(168):char(194),'',TrimB(TrimF(NullToEmpty(In_ACACTIVITY.DESCRIPTION)))- error as above
2) CONVERT(CHAR(194), "", CONVERT(CHAR(191), "", Convert(char(160),'',TrimB(TrimF(NullToEmpty(In_ACACTIVITY.DESCRIPTION))))))-- error as above
3) When I changed the datalength to 40 everything got loaded.But not allowed to do so.
4)I changed the bulk load to write mode as 'Insert' and table action to
'Create' in target oracle stage and getting error of 'Error code: 12899, Error message: ORA-12899: value too large for column "MSPO_TEST"."DESC_PROJECTS2"."DESCRIPTION" (actual: 34, maximum: 30).
How should I remove the multiple ascii characters from the input data? I am able to remove one ascii character char(160) using the Ereplace function (which I posted in the forum).
Please let me know what is the mistake I am doing. Thanks.