Strange formatting using oracle.dbtools.app.Format from SQLCl in Oracle

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 22 Dec 2020 14:42:47 +0300
Message-ID: <CAOVevU59KZ7=8PE-PrOFfW6_KKc-3K4-jQSfb140zXGk3=hVjw_at_mail.gmail.com>

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

import oracle.dbtools.app.Format;
public class Formatter
{
  public static String format (String str) {     String res;
    try {

       oracle.dbtools.app.Format f = new oracle.dbtools.app.Format();
       //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
    IS LANGUAGE JAVA
    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;

XX

---

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
  1 SELECT

  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:
https://gist.github.com/xtender/77e877eebb552b92f83a4ce3d9f0479a

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l

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