Re: Undocumented SQL REVERSE function.

Mark D Powell wrote:
>
> 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 --

I presume the function is used to implement reverse indexes (since you have to enable fbi's to have reverse indexes work)

I wonder what happens if you try the problematic select from three successive reverse indexed tables (with a suitable predicate to make use of the index)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."