如何在Oracle数据库内格式化SQL或PL/SQL ？
在一些情况下，梳理复杂的SQL或PL/SQL代码逻辑时最好是格式化一下文本。
当然我们有Toad或PL/SQL Developer等其它第三方客户端程序作为辅助，但如果在数据库内容方面可以将SQL格式化则更佳。
当前数据库12c以后的软件安装介质中已经包含了Oracle/ SQLcl或Oracle SQL Developer，使用这两者任一自带工具中的JavaLib的dbtools-common.jar中的oracle.dbtools.app.Format函数就可以实现。
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