I have a problem with the oreplace function:
if i use the function to replace a character from a specified field and table,the function works fine:
select DatabaseName.oreplace(fieldName,'@','') from table where id = '000000'The function will replace successfully the sign '@' in the selected field.
The issue is when using the function to replace a character as below:
In this case, the function will execute without error but will not return any result and the query will keep executing until the user abort it.
If "DatabaseName" is something other than TD_SYSFNLIB, then the "oreplace" you are invoking is a user-defined function not supported by Teradata.
Thanks Fred for your reply.
The DatabaseName is the same where the function exists.
I tried to replace the function from bteq and create it under another database but nothing changed.
To note that the oreplace function was working properly before we created on the same database a new UDF(udf_16BEHex2Char) and we were getting the below error when using the new UDF:
" SELECT Failed. 3610: Internal error: Please do not resubmit the last request. SubCode, CrashCode: 0, 3610"
So i don't know if the orpelace function is affected by this error or it is not related.
udf_16BEHex2Char sounds like a UDF from the Unicode Tool Kit, those functions have been tested properly and shouldn't cause an error.
Why don't you use Teradata's oReplace in stead of reinventing the wheel?
Regarding the 3610, this should be an incident (after checking it's not related to some bad code in your UDF).
Thanks Dieter for your reply
We are using the below Teradata's oreplace and it was working fine on the system:
REPLACE FUNCTION DatabaseName.OREPLACE
(input_string VARCHAR(16000) CHARACTER SET LATIN,
search_string VARCHAR(512) CHARACTER SET LATIN,
replace_string VARCHAR(512) CHARACTER SET LATIN)
RETURNS VARCHAR(16000) CHARACTER SET LATIN
PARAMETER STYLE SQL
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!oreplace2!c:\udf\oreplace2.c'
Is there any parameter or configuration on the database to check and that would be causing this problem with the oreplace function?
There's no table to check for problematic UDFs.
The oReplace UDF from the old Oracle library was running at lots of customer sites for years.
Try the built-in td_sysfnlib.oReplace instead.
Dieter is absolutely right. I wrote the original oreplace function long ago for Teradata releases that are no longer supported, and it could be incompatible with current releases for any number of reasons - I haven't even looked at it in years! The TD_SysFnLib version is supported by the Teradata support center. If you want to continue using the oreplace2.c version instead, then you have the source code and will need to find someone in your organization who can debug it.