Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

LISTAGG with DISTINCT option

ApexBineApr 4 2016 — edited Jul 31 2019

I love the LISTAGG function which makes my life a lot easier.

And I would love it even more if it was able to omit duplicates!

So instead of writing

select name, signature, type, listagg(usage,', ') within group (order by usage) usages

from sys.all_identifiers

where owner     = 'ME'

and object_name = 'PKG_EXAMPLE'

and object_type = 'PACKAGE BODY'

group by name, signature, type

which would return something like

'DECLARATION, REFERENCE, REFERENCE, REFERENCE, REFERENCE, REFERENCE'

I would write

select name, signature, type, listagg(usage,', ' DISTINCT) within group (order by usage) usages

from sys.all_identifiers

where owner     = 'ME'

and object_name = 'PKG_EXAMPLE'

and object_type = 'PACKAGE BODY'

group by name, signature, type

and expect

'DECLARATION, REFERENCE'.

What do you think of it? Would it be a useful amendment?

This feature is coming with 19c:

SQL> select deptno, listagg (sal,', ') within group (order by sal)

  2  from scott.emp

  3  group by deptno;

    DEPTNO LISTAGG(SAL,',')WITHINGROUP(ORDERBYSAL)

---------- ---------------------------------------

        10 1300, 2450, 5000

        20 800, 1100, 2975, 3000, 3000

        30 950, 1250, 1250, 1500, 1600, 2850

SQL> select deptno, listagg (distinct sal,', ') within group (order by sal)

  2  from scott.emp

  3  group by deptno;

    DEPTNO LISTAGG(DISTINCTSAL,',')WITHINGROUP(ORDERBYSAL)

---------- -----------------------------------------------

        10 1300, 2450, 5000

        20 800, 1100, 2975, 3000

        30 950, 1250, 1500, 1600, 2850

Comments

DBA2011
Answer
its free
Marked as Answer by happy10319 · Sep 27 2020
506787
please ask your oracle sales representative.

the use of oracle linux is free, the license has different forms, the most simple one grants you access to oracle's up2date facility for updates.
another license form is updates + support, which costs more.
Sergio-Oracle
I'd like to clear up some terminology. Oracle Enterprise Linux is software that's free to download, use and re-distribute. Unbreakable Linux is a support program for Red Hat and Oracle Enterprise Linux which, as Frits points out, is offered in different levels. Details, including pricing, are in this FAQ:

http://www.oracle.com/technologies/linux/ubl-faq.pdf

More details: http://otn.oracle.com/linux

Sergio
happy10319
thanks to all.
What I'm interested in is the price (update+support unbreakable). Since we should decide to buy.
Regards.
Sergio-Oracle
You can see the pricing in the FAQ.

Sergio
694326
Hello,
using Oracle Enterprise Linux is free of charge, but is it also free for commercial use ?

Regards,
Łukasz
Sergio-Oracle
Yes, it's free to install and use for commercial purposes. You may review the license when you download from http://edelivery.oracle.com/linux

Sergio
694326
Cheers Sergio
446566
Hi Sergio,

Just a quick one on patches/updates to OEL. Although the initial distribution is free to download and use which is great, do you have to pay for patches to correct issues in that distribution etc. So not an update for extra functionality but a patch to fix an issue. If they are free do you know how users can obtain them and also know about them.

I guess this may come under support and patches are classed as updates, which of course you may have to pay for but I wanted to clarify it as I use OEL a fair bit and although have support/ metalink access for a db product which I bought, I don't pay for OEL support such as Unbreakable Linux.

Thanks Cel
446566
Hi Sergio, apologies I just read the doc you pointed to earlier:

http://www.oracle.com/technologies/linux/ubl-faq.pdf

It clearly says you need to have support to access patches etc.

Thanks Cel
446566
Hi Sergio (again!! I did not read through the whole document before my last reply).

Having said that the document also says:

"Oracle makes all Linux patches and updates available under GPL and anyone including Red Hat can take those fixes. Oracle plans to synchronize with every major RHEL software release including updates. If Red Hat does not include Oracle fixes in their update releases, Oracle will include the additional fixes at the time of each major synchronization with the current RHEL software release."

Are these patches provided at a source level or in RPMS etc. If in RPMS how can we find out when they are made available under GPL and where could we download them from.

Thanks Cel
Sergio-Oracle
Cel,

We publish the source for Enterprise Linux and subsequent updates and errata we produce here:

http://oss.oracle.com/el5/

and

http://oss.oracle.com/el4/

Sergio
446566
Thanks Sergio, that's great. Will have a look through it today.

Cel
1 - 13

Post Details

Added on Apr 4 2016
48 comments
110,635 views