oreplace function

Database
Fan

oreplace function

Hello,

 

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:

select Databasename.oreplace('abcde','c','x')

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.

 

Thanks

 

6 REPLIES
Teradata Employee

Re: oreplace function

If "DatabaseName" is something other than TD_SYSFNLIB, then the "oreplace" you are invoking is a user-defined function not supported by Teradata.

 

Fan

Re: oreplace function

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.

 

Thanks

 

Junior Contributor

Re: oreplace function

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).

Fan

Re: oreplace function

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

SPECIFIC oreplace2

LANGUAGE C

NO SQL

PARAMETER STYLE SQL

DETERMINISTIC

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?

 

Thanks

 

Junior Contributor

Re: 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.

Teradata Employee

Re: oreplace function

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.