Strange formatting using from SQLCl in Oracle

From: Sayan Malakshinov <>
Date: Tue, 22 Dec 2020 14:42:47 +0300
Message-ID: <>

Hi listers,

I loaded dbtools-common.jar from SqlCL 20.3 into Oracle 19: loadjava -u login/pass_at_pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar

created very simple function:

create or replace and compile java source named Formatter as

public class Formatter
  public static String format (String str) {     String res;
    try { f = new;
       //f.setDefaultOptions(); судя по коду Format он сам вызывает
setDefaultOptions() при инициализации
       res = f.format(str);
    catch (Exception e){
       res = e.getMessage();

    return res;
/create or replace package Formatter is

  function format(pCommand varchar2)
    return varchar2
    name 'Formatter.format(java.lang.String) return java.lang.String'; end Formatter;

All is fine, no errors, but formatted results are pretty strange - no newline characters where they should be by default as in SqlCL:

SQL> select Formatter.format('select a,b,c,d,e,123 z from dual cross apply dual d2') xx from dual;


SELECT A , B , C , D , E , 123 Z FROM DUAL CROSS APPLY DUAL D2 Compare with SqlCl default formatting:
SQL> select a,b,c,d,e,123 z from dual cross apply dual d2   2 .

SQL> format buffer

  2      a,
  3      b,
  4      c,
  5      d,
  6      e,
  7      123 z
  8  FROM
  9*     dual CROSS APPLY dual d2

What's wrong here? and how can I fix it?

I've posted extended version with setOptions for troubleshooting on gist.github:

Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate

Received on Tue Dec 22 2020 - 12:42:47 CET

Original text of this message