It is very easy to Format SQL using Third Party tools, Recently I found one source to get the SQL Formatted from the Sql prompt without connecting to any Third Party GUI Tools.
I prefer to use SQLPLUS more than GUI tools., You can see the code formatted beautifully and very readable.
Step 1: Load oracle.dbtools-common.jar into the Database.
Microsoft Windows [Version 10.0.19044.2006]
(c) Microsoft Corporation. All rights reserved.
C:\Users\Dell>loadjava -u sriram/zaqmlp123 C:\app\Dell\product\21c\dbhomeXE\jlib\oracle.dbtools-common.jar
Step 2: Grant required java Privileges to the User and Create Java and Write a wrapper Package using the java
C:\Users\Dell>sqlplus /nolog
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Sep 17 16:52:53 2022
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
SQL> conn sriram@xepdb1
Enter password:
Connected.
SQL>
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'oracle.DbmsJavaScriptUser', '' );
PL/SQL procedure successfully completed.
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.jdk.nashorn.internal.runtime', '' );
PL/SQL procedure successfully completed.
SQL> exec dbms_java.grant_permission( 'SRIRAM', 'SYS:java.lang.reflect.ReflectPermission', 'suppressAccessChecks', '' );
PL/SQL procedure successfully completed.
SQL> select
2
SQL> ed
Wrote file afiedt.buf
1 CREATE or replace AND COMPILE JAVA SOURCE NAMED SQLFormatter AS
2 import oracle.dbtools.app.Format;
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6 import oracle.sql.BLOB;
7 import oracle.sql.CLOB;
8 import java.io.StringWriter;
9 import java.io.PrintWriter;
10 public class SQLFormatter {
11 private static String getStackTrace(Exception e) {
12 StringWriter writer = new StringWriter();
13 PrintWriter printWriter = new PrintWriter( writer );
14 e.printStackTrace( printWriter );
15 printWriter.flush();
16 return writer.toString();
17 }
18 public static Format getFormat() {
19 oracle.dbtools.app.Format format = new oracle.dbtools.app.Format();
20 format.options.put("singleLineComments", Format.InlineComments.CommentsUnchanged);
21 format.options.put("kwCase", Format.Case.UPPER);
22 format.options.put("idCase", Format.Case.NoCaseChange);
23 format.options.put("adjustCaseOnly", false);
24 format.options.put("formatThreshold", 1);
25 format.options.put("alignTabColAliases", false);
26 format.options.put("alignTypeDecl", true);
27 format.options.put("alignNamedArgs", true);
28 format.options.put("alignEquality", false);
29 format.options.put("alignAssignments", true);
30 format.options.put("alignRight", false);
31 format.options.put("identSpaces", 3);
32 format.options.put("useTab", false);
33 format.options.put("breaksComma", Format.Breaks.Before);
34 format.options.put("breaksProcArgs", false);
35 format.options.put("breaksConcat", Format.Breaks.Before);
36 format.options.put("breaksAroundLogicalConjunctions", Format.Breaks.Before);
37 format.options.put("breaksAfterSelect", true);
38 format.options.put("commasPerLine", 1);
39 format.options.put("breakOnSubqueries", true);
40 format.options.put("breakAnsiiJoin", true);
41 format.options.put("breakParenCondition", true);
42 format.options.put("maxCharLineSize", 120);
43 format.options.put("forceLinebreaksBeforeComment", false);
44 format.options.put("extraLinesAfterSignificantStatements", Format.BreaksX2.Keep);
45 //format.options.put("flowControl", Format.FlowControl.IndentedActions);
46 format.options.put("spaceAroundOperators", true);
47 format.options.put("spaceAfterCommas", true);
48 //format.options.put("spaceAroundBrackets", Format.Space.Default);
49 return format;
50 }
51 public static String format(String str)
52 {
53 String res;
54 try {
55 Format f = SQLFormatter.getFormat();
56 res = f.format(str);
57 }
58 catch (Exception e){
59 res = "Error: " + e.getMessage() + " [ " + SQLFormatter.getStackTrace(e) + " ]";
60 }
61 return res;
62 }
63 public static CLOB formatClob(oracle.sql.CLOB clob)
64 throws SQLException
65 {
66 String str = clob.getSubString(1, (int) clob.length());
67 String res = SQLFormatter.format(str);
68 Connection conn = DriverManager.getConnection("jdbc:default:connection:");
69 CLOB resClob = CLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
70 resClob.setString(1L, res);
71 return resClob;
72 }
73* }
74 /
Java created.
SQL> select
2
SQL> ed
Wrote file afiedt.buf
1 create or replace package SQLFormatter as
2 FUNCTION Format(str in varchar2) RETURN VARCHAR2
3 AS LANGUAGE JAVA NAME 'SQLFormatter.format(java.lang.String) return java.lang.String';
4 FUNCTION FormatClob(str in clob) RETURN CLOB
5 AS LANGUAGE JAVA NAME 'SQLFormatter.formatClob(oracle.sql.CLOB) return oracle.sql.CLOB';
6* end;
7 /
Package created.
SQL> desc SQLFormatter
FUNCTION FORMAT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STR VARCHAR2 IN
FUNCTION FORMATCLOB RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STR CLOB IN
SQL> select sql_id||' - '||sql_text as "UnClear" from v$sqlarea where sql_id='gngtvs38t0060'
2 union
3 select sql_id||' - '||SQLFormatter.format(sql_text) "Readable" from v$sqlarea where sql_id='gngtvs38t0060'
4 /
UnClear
------------------------------------------------------------------------------------------------------------------------
gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s CONNECT BY s.grantee# = PRIOR
s.privilege# AND (s.privilege# > 0 OR s.privilege# = -352)
START WITH (s.privilege# > 0 OR s.privilege# = -352) AND s.grantee# IN (SELECT c1.privilege# FROM sys.codeauth$ c1
WHERE c1.obj# = :1) UNION SELECT c2.priv
ilege# FROM sys.codeauth$ c2 WHERE c2.obj# = :2 ORDER BY 1 ASC
gngtvs38t0060 - SELECT /*+ CONNECT_BY_FILTERING */
s.privilege#
FROM
sys.sysauth$ s
CONNECT BY
s.grantee# = PRIOR s.privilege#
AND (
s.privilege# > 0
OR s.privilege# =-352
)
START WITH
(
s.privilege# > 0
OR s.privilege# =-352
)
AND s.grantee# IN (
SELECT
c1.privilege#
FROM
sys.codeauth$ c1
WHERE
c1.obj# =:1
)
UNION
SELECT
c2.privilege#
FROM
sys.codeauth$ c2
WHERE
c2.obj# =:2
ORDER BY 1 ASC
2 rows selected.
Refer http://orasql.org/2020/12/23/format-sql-or-pl-sql-directly-in-oracle-database/ for more info.
https://github.com/xtender/xt_scripts/tree/master/extra/SQLFormatter
Hope this is useful for you as well 🙂
Sriram Sanka - My Experiences with Databases & More 