如何在Oracle 数据库内格式化SQL 或PL/SQL ？

by

有时在输理复杂的SQL或pl/sql代码逻辑时最好是格式化一下文本，当然我们有toad或plsql developer等其它第三方客户端程序，如果能在数据库内容可以格式化一下SQL更佳，当前数据库12C以后的的软件安装介质中已经包含了Oracle SqlCL或Oracle SQL Developer， 使用这两个自带的任一工具中的java lib的   dbtools-common.jar 中的 oracle.dbtools.app.Format 函数就可以实现。

下面是实际安装步骤，环境19.3

1， 加载java

19.3中自带的发现无法使用

[oracle@oel7db1 lib]$ loadjava -u anbob/anbob@cdb1pdb1 /u01/app/oracle/product/19.2.0/db_1/sqldeveloper/sqldeveloper/lib/dbtools-common.jar
[oracle@oel7db1 lib]$ sqlplus anbob/anbob@cdb1pdb1
创建3#的java函数时会提示下面的错误

Warning: Java created with compilation errors.

SSQL> show error
Errors for JAVA SOURCE "SQLFORMATTER":

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      Note: SQLFORMATTER uses or overrides a deprecated API.
0/0      ORA-29534: referenced object ANBOB.oracle/dbtools/app/Format
         could not be resolved

0/0      Note: Recompile with -Xlint:deprecation for details.

下载新版的SQLCL https://www.oracle.com/tools/downloads/sqlcl-downloads.html 使用目录里的dbtools-common.jar 可以解决。
我这里把sqlcl放到了$ORACLE_HOME下

loadjava -u anbob/anbob@cdb1pdb1 /u01/app/oracle/product/19.2.0/db_1/sqlcl/lib/dbtools-common.jar

2， JAVA授权

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

SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );

SQL> exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );

SQL>exec dbms_java.grant_permission( 'ANBOB', 'SYS:java.util.PropertyPermission', 'polyglot.js.nashorn-compat', 'write' )

注意，如果不授权SYS:java.util.PropertyPermission 在后面执行时会提示无权限。

3, 创建JAVA函数

CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
/* Imports */
import oracle.dbtools.app.Format;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import java.io.StringWriter;
import java.io.PrintWriter;


public class SQLFormatter {

    private static String getStackTrace(Exception e) {
       StringWriter writer = new StringWriter();
       PrintWriter printWriter = new PrintWriter( writer );
       e.printStackTrace( printWriter );
       printWriter.flush();

       return writer.toString();
    }

    public static Format getFormat() {
        oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
        
        format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
        format.options.put("kwCase", Format.Case.UPPER);
        format.options.put("idCase", Format.Case.NoCaseChange);                             // default: Format.Case.lower
        format.options.put("adjustCaseOnly", false);                                        // default: false (set true to skip formatting)
        format.options.put("formatThreshold", 1);                                           // default: 1 (disables deprecated post-processing logic)
        // Alignment
        format.options.put("alignTabColAliases", false);                                    // default: true
        format.options.put("alignTypeDecl", true);
        format.options.put("alignNamedArgs", true);
        format.options.put("alignEquality", false);
        format.options.put("alignAssignments", true);                                       // default: false
        format.options.put("alignRight", false);                                            // default: false
        // Indentation
        format.options.put("identSpaces", 3);                                               // default: 4
        format.options.put("useTab", false);
        // Line Breaks
        format.options.put("breaksComma", Format.Breaks.Before);                            // default: Format.Breaks.After
        format.options.put("breaksProcArgs", false);
        format.options.put("breaksConcat", Format.Breaks.Before);
        format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
        format.options.put("breaksAfterSelect", true);                                      // default: true
        format.options.put("commasPerLine", 1);                                             // default: 5
        format.options.put("breakOnSubqueries", true);
        format.options.put("breakAnsiiJoin", true);                                         // default: false
        format.options.put("breakParenCondition", true);                                    // default: false
        format.options.put("maxCharLineSize", 120);                                         // default: 128
        format.options.put("forceLinebreaksBeforeComment", false);                          // default: false
        format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);   // default: Format.BreaksX2.X2
        format.options.put("flowControl", Format.FlowControl.IndentedActions);
        // White Space
        format.options.put("spaceAroundOperators", true);
        format.options.put("spaceAfterCommas", true);
        format.options.put("spaceAroundBrackets", Format.Space.Default);
        //format.options.put("formatProgramURL", "default");
        
        return format;
    }
    
  public static String format(String str) 
  {
    String res;
    try {
       //res = new Format().format(str);
       Format f = SQLFormatter.getFormat();
       res = f.format(str);
       }
    catch (Exception e){
       res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
    }
    return res;
  }

  public static CLOB formatClob(oracle.sql.CLOB clob) 
  throws SQLException
  {
    String str = clob.getSubString(1, (int) clob.length());
    String res = SQLFormatter.format(str);
    Connection conn = DriverManager.getConnection("jdbc:default:connection:");
    CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
    resClob.setString(1L, res);
    
    return resClob;
  }
}
/

注： 也可以从https://github.com/xtender/xt_scripts/blob/master/extra/SQLFormatte 下载最新版

4, 创建PL/SQL PACKAGE

create or replace package SQLFormatter as

  FUNCTION Format(str in varchar2) RETURN VARCHAR2
  AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';

  FUNCTION FormatClob(str in clob) RETURN CLOB
  AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
  
end;
/

 

5, 测试

SQL> select anbob.SQLFormatter.formatclob(to_clob('select 1 a from dual')) qtext from dual;

QTEXT
---------------------------------------------------------------------------------------
SELECT
   1 a
FROM
   dual


SQL> select anbob.SQLFormatter.format('select 1 a,2 /*123 */ b,3 c, d from dual, dual d2') qtext from dual;

QTEXT
----------------------------------------------------------------------------------------
SELECT
   1 a
 , 2 /*123 */ b
 , 3 c
 , d
FROM
   dual
 , dual d2

— enjob —