How to use listagg in sleep mode

Question

I am trying to use the listagg function to create a list of users.

Consider the following table

 ROLE_ID    ENAME
---------- ----------
       4    CLARK
       4    KING
       4    MILLER
       7    ADAMS
       9    FORD
       9    JONES

When using the following query in SQL Developer

SELECT ROLE_ID,
       LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEES 
FROM USERS 
GROUP BY ROLE_ID;

will result in the following console output:

ROLE_ID EMPLOYEES
-----------------------------
4        CLARK,KING,MILLER
7        ADAMS
9        FORD, JONES

My goal is to do this in sleep mode, but I'm not sure how to proceed. Any help is appreciated.

Answer 1

For those who have problems using listagg as part of the formula, registering the keyword “inside” with Hibernate solved the problem for me. (Using Hibernate 5.0.7.)

public class CustomOracleDialect extends Oracle10gDialect {
    public CustomOracleDialect() {
        super();

        registerKeyword("within");
    }
}

Hibernate , "" . , , :

@Formula("(select (listagg(l.serial_number, ', ') within group(order by d.serial_number))\n"
        + "from order_lines l\n"
        + "where l.order_id = id\n"
        + "group by l.order_id)")
private String serialNumbers;

: listagg, , Hibernate . , , , listagg , .

Answer 2

Hibernate , sql . hibernateDialect.

registerFunction("listAgg", new StandardSQLFunction("listAgg", Hibernate.STRING));

Answer 3

listagg . wm_concat, .

..

public class ServiceAppOracle10gDialect extends Oracle10gDialect {
     public ServiceAppOracle10gDialect() {
            super();
            //registerFunction("LISTAGG", new StandardSQLFunction("LISTAGG", StandardBasicTypes.STRING));
            registerFunction("wm_concat", new StandardSQLFunction("wm_concat", StandardBasicTypes.STRING));
        }
}

, hibernate.dialect.

<!--                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop> -->
                <prop key="hibernate.dialect">com.apple.sc.analytics.utils.ServiceAppOracle10gDialect</prop>

Answer 4

.

@Formula("(select (listagg(l.serial_number, ', ') \00within group(order by d.serial_number))\n"
    + "from order_lines l\n"
    + "where l.order_id = id\n"
    + "group by l.order_id)")
private String serialNumbers;

Just add the keyword \ 00 to WITHIN. And you do not need to renew anything.

Greetings.