Orafce concat operator

Hello list,

The Orafce compatibility package doesn't seem to have operators defined
(looked in the sql load file). The function I'm specifically interested
in, is Oracle's concatenation that regards a NULL as the empty string
and hence returns 'the other value'. This in contrast with Pg's || that
returns NULL if either of the operands is NULL. The Orafce package
contains a concat function with Oracle behaviour, however an operator is
missing.

Having an associative operator has benefits over having only a function,
since that would make translating expressions like 'monkey' || 'nut' ||
NULL easy.

What about adding something like operator ||| in the orafce package for
concat?

Regards,
Yeb Havinga

2010/2/10 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
> Hello list,
>
> The Orafce compatibility package doesn't seem to have operators defined
> (looked in the sql load file). The function I'm specifically interested in,
> is Oracle's concatenation that regards a NULL as the empty string and hence
> returns 'the other value'. This in contrast with Pg's || that returns NULL
> if either of the operands is NULL. The Orafce package contains a concat
> function with Oracle behaviour, however an operator is missing.
>
> Having an associative operator has benefits over having only a function,
> since that would make translating expressions like 'monkey' || 'nut' || NULL
> easy.
>
> What about adding something like operator ||| in the orafce package for
> concat?

no, it could be confusing and it isn't enough, because it isn't only
|| or concat problem. On Oracle empty string is equal to NULL and NULL
is equal to empty string.

example: '' is null, length('')

http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/

so we are not able emulate this behave.

Regards
Pavel Stehule

>
> Regards,
> Yeb Havinga
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Pavel Stehule wrote:
>> What about adding something like operator ||| in the orafce package for
>> concat?
>>
>
> no, it could be confusing and it isn't enough, because it isn't only
> || or concat problem. On Oracle empty string is equal to NULL and NULL
> is equal to empty string.
>
> example: '' is null, length('')
>
> http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/
>
> so we are not able emulate this behave.
>
Hi Pavel,

Thanks for your quick reply. What you say is entirely true, however I'm
not sure if you completely understood my question, so please do not be
offended if I try to elaborate it a bit:

I did not ask for full '' = NULL emulation, only for an operator to
match the concat function, so that code conversion with repetetive
concats are easier (string || string || string etc) which occur
frequently. Ofcourse a translator can be made that converst that to
concat(string,concat(string,concat(string etc))), however that's
confusing too in a different way. So imho the only problem is to think
of an operator that somehow resembles || so the programmer recognizes a
concat, but also adds something so the programmer recognizes: not
strict. What about ||+ ?

And then, at the conversion of e.g. (string || string || string) IS
NULL, confusion arises, but this is not due to the concat, but more to
the IS NULL clause together with the strange '' = null, and that must be
handled otherwise.

So the operator would speed up part of the code conversion.

regards,
Yeb Havinga

2010/2/10 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
> Pavel Stehule wrote:
>>>
>>> What about adding something like operator ||| in the orafce package for
>>> concat?
>>>
>>
>> no, it could be confusing and it isn't enough, because it isn't only
>> || or concat problem. On Oracle empty string is equal to NULL and NULL
>> is equal to empty string.
>>
>> example: '' is null, length('')
>>
>> http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/
>>
>> so we are not able emulate this behave.
>>
>
> Hi Pavel,
>
> Thanks for your quick reply. What you say is entirely true, however I'm not
> sure if you completely understood my question, so please do not be offended
> if I try to elaborate it a bit:
>
> I did not ask for full '' = NULL emulation, only for an operator to match
> the concat function, so that code conversion with repetetive concats are
> easier (string || string || string etc) which occur frequently. Ofcourse a
> translator can be made that converst that to
> concat(string,concat(string,concat(string etc))), however that's confusing
> too in a different way. So imho the only problem is to think of an operator
> that somehow resembles || so the programmer recognizes a concat, but also
> adds something so the programmer recognizes: not strict. What about ||+ ?
>
> And then, at the conversion of e.g. (string || string || string) IS NULL,
> confusion arises, but this is not due to the concat, but more to the IS NULL
> clause together with the strange '' = null, and that must be handled
> otherwise.
>
> So the operator would speed up part of the code conversion.
>

I have a different opinion. You have to change a application source
code. So I don't like it in orafce. Maybe we can implement varchar2
text type and for this type redefine basic functions. But it could be
a messy and maybe contraproductive. Orafce is tool for better
migration, but it isn't full compatibility tool - what can be
implemented effective and well, then can be in orafce. EnterpriseDB do
"full" compatibility with Oracle

but - I don't see a problem. Everybody who like operator ||| can do it
very simple - not all have to be in Orafce.

Regards
Pavel Stehule

> regards,
> Yeb Havinga
>
>
>
>