Forum Stats

  • 3,752,916 Users
  • 2,250,585 Discussions
  • 7,868,049 Comments

Discussions

question on reverse

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,688 Black Diamond
    edited Nov 12, 2019 6:38AM

    It can. Length isn't needed even if we sort in ascending order:

    select  listagg(substr(qid,-level,1)) within group(order by level)

    SY.

    Stew AshtonRanagal
  • BluShadow
    BluShadow Member, Moderator Posts: 41,325 Red Diamond
    edited Nov 12, 2019 7:58AM
    BrunoVroman wrote:Thank you Stew and SY.I thought that Oracle wouldn't present such a function to users if we aren't supposed to use it as it looks quite innocent (I would say in the same family as things like INITCAP); I would expect from Oracle to have a lot of "hidden code" (for example to create revers indexes) that users don't need to see, and that if anyone can run something like REVERSE, it should be documented (and there might be a "byte level" and "char level" versions like LENGTH LENGTHB, or use of a parametrer…)Anyway, nothing critical, just amazing ;-)(I don't remember how I learned that REVERSE existed, and as I wrote if I use it is very rarely and not for serious things)Best regards,Bruno.

    Bruno, there are plenty of example of functions that Oracle have created that they use for their own purposes, but have never officially made them public by documenting them.

    One of the unusual ones is as follows:

    Ansi Date literals...

    SQL> select date '2019-11-12' from dual;

    DATE'2019-11-12'
    --------------------
    12-NOV-2019 00:00:00

    IS documented: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ

    But Ansi Time literals...

    SQL> select time '15:14:13' from dual;

    TIME'15:14:13'
    ------------------------------------------
    15.14.13.000000000

    are also in there, but NOT documented.

    BrunoVromanL. FernigriniRanagal
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,688 Black Diamond
    edited Nov 12, 2019 9:25AM

    It is not just time literals:

    https://community.oracle.com/message/12373734#12373734

    But even in 19C:

    Do not define columns with the following SQL/DS and DB2 data types, because they have no corresponding Oracle data type:

    • GRAPHIC
    • LONG VARGRAPHIC
    • VARGRAPHIC
    • TIME

    Note that data of type TIME can also be expressed as Oracle datetime data.

    SY.

  • Ranagal
    Ranagal Member Posts: 635 Bronze Badge
    edited Nov 12, 2019 9:28AM

    Hi SY,

    SY wrote:select  listagg(substr(qid,-level,1)) within group(order by level)

    Oh, good observation. Thanks for the explanation.

    Regards,

    Ranagal