Re: Undocumented SQL REVERSE function.

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D49A7B2.D50B3490_at_exesolutions.com>...
> "Barry P. Grove" wrote:
>
> > Hi,
> >
> > Just discovered the undocumented SQL REVERSE function; it's not the one
> > from utl_raw, and it doesn't seem to be defined in /rdbms/admin/standard.sql,
> > so I don't know why it might be there. Other than compatibility with
> > SQLServer and/or DB2. Checked Oracle versions v7.3.4, v8.0.4, v8.1.7 and
> > all of these support it, check out this otherwise innocuous function:
> > select reverse('abcdefg') from dual;
> >
> > Am I missing something? Is this documented in some place I missed?
> >
> > Barry.
>
> Interesting. I especially enjoy
>
> SELECT REVERSE(12345) FROM dual;
> and
> SELECT REVERSE(SYSDATE) FROM dual;
>
> SELECT REVERSE(null) FROM dual brings SQL*Plus down in an unceremonious crash.
>
> Daniel Morgan

Interesting, More like wierd:

UT1> select sysdate, reverse(sysdate), reverse(trunc(sysdate)) from dual;

SYSDATE REVERSE(S REVERSE(T
--------- --------- ---------
02-AUG-02 09-- -20 00-DECEMB

• reverse sysdate repeatedly produces different resutls, obviously due to time

UT1> select reverse(12345), reverse('12345') from dual;

REVERSE(12345) REVER
-------------- -----

    -7.790E+33 54321

I took Daniel's word about crashing the instance by trying to reverse(null). I had a C book which listed reverse as being a string function to do my last example, 12345 ==< 54321. On (I think) Pyramid I wrote a C program that used it and when we migrated to Sequent the function was unknown so I had to write it.

Too bad we probably can't get the story behind why this esists. Developer needed it internally or it was going to be provided but no one ever issued a final decision on how to handle numbers and date datatypes properly ....

• Mark D Powell --