Re: 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 16:56:17 +0300
Message-ID: <CAOVevU79ShEPzMURov7n5CrMWctX6D6Pbn=m4ESaCMn+Phwdcw_at_mail.gmail.com>



Thanks to Mikhail Velikikh, fixed it with

exec dbms_java.grant_permission( 'XTENDER',
'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
exec dbms_java.grant_permission( 'XTENDER',
'SYS:java.lang.RuntimePermission',

'accessClassInPackage.jdk.nashorn.internal.runtime', '' ); exec dbms_java.grant_permission( 'XTENDER', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE Associate
http://orasql.org

вт, 22 дек. 2020 г., 14:42 Sayan Malakshinov <xt.and.r_at_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 - 14:56:17 CET

Original text of this message