How can i call this database function with DS4. Do I need to wrap this in another named function/stored procedure and import by name because I can’t create this in the source and don’t really want to store in our staging area nor do I want to use the sql transform.
Many thanks in advance.
jags274 (BOB member since 2012-05-10)
yes, wrap it in a stored procedure/function.
Werner Daehn 
(BOB member since 2004-12-17)
Hi again Werner,
I’ve wrapped this in a Data Services Custom function as follows:
$L_I_Hash_Value = ‘’;
$L_I_String = ‘’;
$L_I_String = $P_I_String;
$L_I_Hash_Value = sql(‘ds’,‘select ora_hash({$L_I_String}) from dual’);
return $L_I_Hash_Value;
…and now half of the returns are null, even though the string i’m feeding the function always has a value.
jags274 (BOB member since 2012-05-10)
Don’t know why. But you should not use the sql() function, you should create a stored procedure, import it into the datastore and then use that either in the mapping of the first query or as new-function-call.
Werner Daehn (BOB member since 2004-12-17)
Thanks again Werner, the reason was because the return was defined as an integer which has its own limitations and yes as an imported dbase function its considerably quicker however it does mean its another object to migrate and promote to next environment.
Anyhow on the same topic, does DS offer an alternative to ORA_HASH, e.g. something in python that gives a deterministic value.
Many thanks
jags274 (BOB member since 2012-05-10)
Hm. encrypt(string) maybe? We have not exposed the hash function we are using internally yet although I had requested a few years back.
Werner Daehn (BOB member since 2004-12-17)