Obviously we can format/beautify SQL or PL/SQL code using external tools, but sometimes it would be more convenient to format it directly in database, for example if we want to see few different sql_text’s from v$sqlarea. And thanks to Oracle SqlCL and Oracle SQL Developer, we can easily use oracle.dbtools.app.Format function from their Java library dbtools-common.jar, so if you use SqlCL or SQL Developer, you can use the same formatting options.
1. load appropriate java library into Oracle
You may have already installed SQLDeveloper or SqlCL on your database server, just check $ORACLE_HOME/sqldeveloper or $ORACLE_HOME/sqcl directories. If – not, you need to download appropriate SqlCL version that matches your java version in Oracle. For 12.2 – 19.8 you can download latest SqlCL 20.3. In fact we need just dbtools-common.jar from lib directory. I put it into $ORACLE_HOME/sqlcl/lib directory on the server and load it using loadjava:
loadjava -u login/pass@pdb1 $ORACLE_HOME/sqlcl/lib/dbtools-common.jar
Just change your login credentials and dbname.
2. set java permissions
Then you need to grant required Java permissions:
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );
exec dbms_java.grant_permission( '{USERNAME}', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
3. create java functions
As you can see, in the code below you can specify own formatting options (function getFormat()). For example, formatting options from Trivadis: https://github.com/Trivadis/plsql-formatter-settings/blob/main/settings/sql_developer/trivadis_advanced_format.xml
You can copy the code below, but it would be better to take latest code from https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter
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;
}
}
/
4. Create PL/SQL package for it
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;
/
Now we can test it:
SQL> select 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
SQL> select sql_id,SQLFormatter.format(sql_text) qtext from v$sqlarea where rownum<3 and sql_text is not null;
SQL_ID QTEXT
------------- -------------------------------------
fbbm59qban13m DELETE FROM idl_sb4$
WHERE
obj# = :1
AND part = :2
AND version <> :3
1gfaj4z5hn1kf DELETE FROM dependency$
WHERE
d_obj# = :1
