Oracle SQL

    Tag Archives: sql format

    Format SQL or PL/SQL directly in Oracle database

    Posted on December 23, 2020 by Sayan Malakshinov Posted in java, oracle, PL/SQL, SQL, SQL*Plus, SQL*PLus tips Leave a comment

    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
    
    
    beautifier pl/sql formatter sql format SQL*Plus sqlformatter

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    UK / Aylesbury / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 5 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Format SQL or PL/SQL directly in Oracle database
    • Funny friday Oracle SQL quiz: query running N seconds
    • Simple function returning Parallel slave info
    • Android Oracle Client 2.0
    • PL/SQL functions and statement level consistency

    Email Subscription

    Recent Comments

    • Oracle SYSTPH* Type – oraerr.com on A funny fact about collect
    • Is the use of SELECT COUNT(*) before SELECT INTO slower than using Exceptions? – oraerr.com on About the performance of exception handling
    • Oracle SQL | Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions on PRECOMPUTE_SUBQUERY hint
    • Oracle SQL | Deterministic function vs scalar subquery caching. Part 3 on Deterministic function vs scalar subquery caching. Part 2
    • Index Bouncy Scan 2 | Oracle Scratchpad on Amazing optimization of getting distinct values from the index, and TopN for each of them
    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries feed
    • Comments feed
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL