By Sriram Sanka

  • Total Blog Hits

    • 95,570 hits
  • $riram $anka


    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

  • Flag Counter
  • Enter your email address to follow this blog and receive notifications of new posts by email.

SQL Formatter – Using Java/PL/SQL by Sayan Malakshinov

Posted by Sriram Sanka on September 17, 2022


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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more .... ¯\_(ツ)_/¯

Thinking Out Loud

Michael T. Dinh, Oracle DBA

Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Anand's Data Stories

Learn. Share. Repeat.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

%d bloggers like this: