Error Parsing listagg Function with chr(10) Parameter in jOOQ

Question

When attempting to use the listagg function with the chr(10) parameter as a delimiter in jOOQ, the parser encounters an error and fails to parse the SQL query. The error message received is: String literal expected [*] chr(10). This issue prevents the successful parsing of SQL queries that utilize listagg with newline characters as delimiters.

Reproducer :

public class ListaggReproducer {
    public static void main(String[] args) {
        // JDBC connection parameters
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String username = "TEST";
        String password = "TEST";

        // SQL query with listagg and chr(10) delimiter
        String sql = "SELECT listagg(emp_name, chr(10)) WITHIN GROUP (ORDER BY column_name) AS result FROM employe";

        try (Connection conn = DriverManager.getConnection(url, username, password);
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {

            // Fetch and print the result
            while (rs.next()) {
                String result = rs.getString("result");
                System.out.println(result);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Expected Behavior: jOOQ should successfully parse SQL queries containing the listagg function with chr(10) parameters as delimiters, allowing for the proper generation and execution of SQL statements.

Actual Behavior: The source database dialect is Oracle, while the destination one is PostgreSQL. Both databases can parse chr(10) perfectly fine, but the jOOQ parser fails to recognize it.

Environment:

jOOQ version: 3.19.6
Database dialect: From oracle to PostgreSQL
jOOQ edition: open source

Any guidance or resolution on this matter would be greatly appreciated. Thank you.

Answer 1

You could report a feature request here: https://github.com/jOOQ/jOOQ/issues/new/choose

As a workaround, you can either patch the input SQL using a regex (such as replacing chr(10) by '\n' (the \n being an actual line feed character), or extend the parser using a ParseListener, which makes the replacement