ORDER BY oracle database

Camunda 7 Topics Discussion & Questions
1

Hello,
I need to do order by in my camunda database but I would like to use different sort,
e.g. instead of a classic
ORDER BY

I would like to use the next function
ORDER BY NLSSORT((name),‘NLS_SORT=Xczech’);

Here is the java code which I use for sorting:

identityService.createGroupQuery()
                .groupMember(userId.toLowerCase())
                .orderByGroupName()
                .asc()
                .list();`

The reason I use NLSSORT function is that I cannot change NLS_SORT property in oracle database
it’s read-only.
ALTER SYSTEM SET NLS_SORT = CZECH;

I can change the session but that does not help me, changes work only for one session
ALTER SESSION SET NLS_SORT = CZECH;

Is there a way how I can do this via Java Query API? Or if there is some property in Camunda configuration for language-specific sort it would be great.

2

@Jovan_Zoric Camunda sort the results in either asc for ascending order or desc for descending order.

NLSSORT function was not supported in built in Java/Rest Api’s.

Maybe you can give a try with native queries where you can supply the plain SQL queries. But providing NLSSORT will become performance bottleneck because camunda table indexes doesn’t include the NLSSORT so it might result in scanning entire table.

You can add additional index to those tables for which you’re going to use NLSSORT function.

SQL> SHOW PARAMETER NLS_SORT;
SQL> SHOW PARAMETER NLS_COMP;
SQL> ALTER SESSION SET NLS_COMP = 'LINGUISTIC';
SQL> ALTER SESSION SET NLS_SORT = 'CZECH';

create unique index idx_<tablename>_<nls_sort_param>
      on <tablename>(nlssort(name, 'nls_sort=CZECH'));

http://www.adp-gmbh.ch/ora/sql/nlssort.html

You can try below options:

Option 1: Using the default jdbc pool in Spring Boot (Tomcat):

spring.datasource.tomcat.init-sql = ALTER SESSION SET NLS_SORT = CZECH;

Option 2: Connecting to the database as your user, you can create a trigger that will change the schema each time you login:

CREATE OR REPLACE TRIGGER LOGON_TRG 
  AFTER LOGON ON SCHEMA
BEGIN
     EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT = CZECH';
EXCEPTION 
  when others 
    then null;
END;
/

Option 3: Using spring boot java config:

@Bean
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource(@Value("${spring.datasource.schema}") String schema) {
    DataSource datasource = DataSourceBuilder.create().build();
    if(!schema.isEmpty() && datasource instanceof org.apache.tomcat.jdbc.pool.DataSource){
            ((org.apache.tomcat.jdbc.pool.DataSource) datasource).setInitSQL("ALTER SESSION SET NLS_SORT = CZECH");
    }
    return datasource;
}

Option 4: Using a script in spring.datasource.schema :

spring.datasource.schema = schema.sql

And then a file schema.sql with the following:

ALTER SESSION SET NLS_SORT = CZECH

Option 5: Using CallableStatement :

CallableStatement callableStatement = connection.prepareCall("{call dbms_session.set_nls('NLS_SORT','CZECH')}");

callableStatement.execute();

Option 6: If you are using spring and hikari datasource (Best Approach):

@Bean
public DataSource getDataSource() throws SQLException {
    OracleDataSource oracleDataSource = new OracleDataSource();
    oracleDataSource.setURL(Secrets.get("DB_URL"));
    oracleDataSource.setUser(Secrets.get("DB_USER"));
    oracleDataSource.setPassword(Secrets.get("DB_PASS"));
    // other Oracle related settings...

    HikariDataSource hikariDatasource = new HikariDataSource();
    hikariDatasource.setDataSource(oracleDataSource);
    hikariDatasource.setConnectionInitSql("ALTER SESSION SET NLS_SORT = CZECH");
    return hikariDatasource ;
}

Oracle Database InitSql:

SQL statement to execute that will initialize newly created physical database connections. Start the statement with SQL followed by a space.

If the Init SQL value begins with "SQL " , then the rest of the string following that leading token will be taken as a literal SQL statement that will be used to initialize database connections. If the Init SQL value does not begin with "SQL ", the value will be treated as the name of a table and the following SQL statement will be used to initialize connections: "select count(*) from InitSQL"

The table InitSQL must exist and be accessible to the database user for the connection. Most database servers optimize this SQL to avoid a table scan, but it is still a good idea to set InitSQL to the name of a table that is known to have few rows, or even no rows.

3

@aravindhrs
Thanks a lot for such a good and detailed answer,

I tried to create a trigger on logon but without success i.e.
the property NLS_SORT is changed by my trigger but after that,
it is overridden and set back to the default value.