{"id":10395,"date":"2020-08-09T20:42:32","date_gmt":"2020-08-09T18:42:32","guid":{"rendered":"https:\/\/www.salvis.com\/blog\/?p=10395"},"modified":"2023-11-12T13:51:37","modified_gmt":"2023-11-12T12:51:37","slug":"formatting-sql-scripts-in-a-directory-tree-with-sqlcl","status":"publish","type":"post","link":"https:\/\/www.salvis.com\/blog\/2020\/08\/09\/formatting-sql-scripts-in-a-directory-tree-with-sqlcl\/","title":{"rendered":"Formatting SQL Scripts in a Directory Tree with SQLcl"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

Oracle’s SQL Developer can format code in any worksheet and PL\/SQL editor. The formatter is highly configurable and the default formatting results are becoming better with every version. Oracle’s SQLcl is a command-line tool. It’s a stripped-down version of SQL Developer and is known as a user-friendly alternative for SQL*Plus.<\/p>\n\n\n\n

But SQLcl is more. It can execute JavaScript and access any Java class distributed with SQLcl. Through JavaScript, you can access local and remote resources easily. In this blog post, I show how you can format all your SQL scripts with a few lines of JavaScript.<\/p>\n\n\n\n

Demo Setup<\/h2>\n\n\n\n

I re-formatted the following three SQL scripts by hand. The first two are ugly. In the end, I want to show that the formatter is an improvement, even if you do not agree with the applied style guideline. I think it is important to know how the formatter deals with syntax errors. That’s why I’ve added one to the last script.<\/p>\n\n\n\n

query.sql<\/span><\/path><\/path><\/svg><\/span>
Select<\/span> d.department_name,v.  employee_id <\/span><\/span>\n,v <\/span><\/span>\n. last_name <\/span>frOm<\/span> departments d <\/span>CROSS<\/span> <\/span>APPLY<\/span>(<\/span>select<\/span>*<\/span>from<\/span> employees e<\/span><\/span>\n  <\/span>wHERE<\/span> e.department_id=d.department_id) v <\/span>WHeRE<\/span> <\/span><\/span>\nd.department_name <\/span>in<\/span> (<\/span>'Marketing'<\/span><\/span>\n,<\/span>'Operations'<\/span>,<\/span><\/span>\n'Public Relations'<\/span>) <\/span>Order By<\/span> d.<\/span><\/span>\ndepartment_name,v.employee_id;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/div>\n\n\n\n
package_body.sql<\/span><\/path><\/path><\/svg><\/span>
create or replace<\/span> <\/span>package body<\/span> <\/span>the_api.math<\/span> <\/span>as<\/span> function to_int_table(in_integers<\/span><\/span>\nin<\/span> <\/span>varchar2<\/span>,in_pattern <\/span>in<\/span> <\/span>varchar2<\/span> <\/span>default<\/span> <\/span>'[0-9]+'<\/span>)<\/span>return<\/span> sys.ora_mining_number_nt <\/span>deterministic<\/span> accessible<\/span><\/span>\nby(<\/span>package<\/span> <\/span>the_api.math,package<\/span> the_api.test_math)<\/span>is<\/span> <\/span>l_result<\/span> sys<\/span><\/span>\n.ora_mining_number_nt:=sys.ora_mining_number_nt();<\/span>l_pos<\/span> <\/span>integer<\/span>:= <\/span>1<\/span>;<\/span>l_int<\/span> <\/span>integer<\/span>;<\/span><\/span>\nbegin<\/span><<integer_tokens>><\/span>loop<\/span> <\/span>l_int<\/span>:=<\/span>to_number<\/span>(regexp_substr(in_integers,in_pattern,<\/span>1<\/span>,<\/span>l_pos<\/span>));<\/span><\/span>\nexit<\/span> integer_tokens <\/span>when<\/span> <\/span>l_int<\/span> <\/span>is null<\/span>;<\/span>l_result<\/span>.extend<\/span>;<\/span>l_result<\/span>(<\/span>l_pos<\/span>):= <\/span>l_int<\/span>;<\/span>l_pos<\/span>:=<\/span>l_pos<\/span>+<\/span>1<\/span>;<\/span><\/span>\nend loop<\/span> integer_tokens;<\/span>return<\/span> <\/span>l_result<\/span>;<\/span>end<\/span> to_int_table;<\/span>end<\/span> math;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
<\/div>\n\n\n\n
syntax_error.sql<\/span><\/path><\/path><\/svg><\/span>
declare<\/span><\/span>\n   <\/span>l_var1<\/span>  <\/span>integer<\/span>;<\/span><\/span>\n   <\/span>l_var2<\/span>  <\/span>varchar2<\/span>(<\/span>20<\/span>);<\/span><\/span>\nbegin<\/span><\/span>\n   <\/span>for<\/span> r <\/span>in<\/span> <\/span>\/*(*\/<\/span> <\/span>select<\/span> x.* <\/span>from<\/span> x <\/span>join<\/span> y <\/span>on<\/span> y.a = x.a)<\/span><\/span>\n   <\/span>loop<\/span><\/span>\n      p(r.a, r.b, r.<\/span>c<\/span>);<\/span><\/span>\n   <\/span>end loop<\/span>;<\/span><\/span>\nend<\/span>;<\/span><\/span>\n\/<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
I committed these files to my sandbox GitHub repository<\/a>. This way I can compare the formatting results with the committed version and I can easily revert the changes.<\/p>\n\n\n\n
Running the Formatter with Default Settings<\/h2>\n\n\n\n
The following JavaScript queries all .sql files in a directory tree, applies the default formatter settings and replaces the original content with the formatted version.<\/p>\n\n\n\n
JavaScript<\/span><\/path><\/path><\/svg><\/span>
var<\/span> <\/span>getFiles<\/span> = <\/span>function<\/span> (<\/span>rootPath<\/span>) {<\/span><\/span>\n    <\/span>var<\/span> <\/span>Collectors<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>"java.util.stream.Collectors"<\/span>);<\/span><\/span>\n    <\/span>var<\/span> <\/span>Files<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>"java.nio.file.Files"<\/span>);<\/span><\/span>\n    <\/span>var<\/span> <\/span>Paths<\/span> = <\/span>Java<\/span>.<\/span>type<\/span>(<\/span>"java.nio.file.Paths"<\/span>);<\/span><\/span>\n    <\/span>var<\/span> <\/span>files<\/span> = <\/span>Files<\/span>.<\/span>walk<\/span>(<\/span>Paths<\/span>.<\/span>get<\/span>(<\/span>rootPath<\/span>))<\/span><\/span>\n        .<\/span>filter<\/span>(<\/span>function<\/span> (<\/span>f<\/span>) <\/span>Files<\/span>.<\/span>isRegularFile<\/span>(<\/span>f<\/span>) && <\/span>f<\/span>.<\/span>toString<\/span>().<\/span>endsWith<\/span>(<\/span>".sql"<\/span>))<\/span><\/span>\n        .<\/span>collect<\/span>(Collectors.toList()); <\/span><\/span>\n    <\/span>return<\/span> <\/span>files<\/span>;<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nif<\/span> (<\/span>args<\/span>[<\/span>1<\/span>] == <\/span>null<\/span>) {<\/span><\/span>\n    ctx.write(<\/span>"<\/span>\\n<\/span>please provide the root path to a directory with .sql files.<\/span>\\n\\n<\/span>"<\/span>);<\/span><\/span>\n} else {<\/span><\/span>\n    ctx.write(<\/span>"<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n    var Files = Java.type(<\/span>"java.nio.file.Files"<\/span>);<\/span><\/span>\n    var files = getFiles(args[1]);<\/span><\/span>\n    var Format = Java.type(<\/span>"oracle.dbtools.app.Format"<\/span>);<\/span><\/span>\n    var formatter = new Format();<\/span><\/span>\n    for (var i in files) {<\/span><\/span>\n        ctx.write(<\/span>"Formatting file "<\/span> + (i+1) + <\/span>" of "<\/span> + files.length + <\/span>": "<\/span> + files[i].toString() + <\/span>"... "<\/span>);<\/span><\/span>\n        ctx.getOutputStream().flush();<\/span><\/span>\n        var original = Files.readString(files[i]);<\/span><\/span>\n        var result = formatter.format(original);<\/span><\/span>\n        Files.writeString(files[i], result);<\/span><\/span>\n        ctx.write(<\/span>"done.<\/span>\\n<\/span>"<\/span>);<\/span><\/span>\n        ctx.getOutputStream().flush();<\/span><\/span>\n    }<\/span><\/span>\n}<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
I’d like to focus in this blog post on the formatter. The formatter is instantiated with default settings on line 18. On line 23 the original file content is passed to the formatter and the formatted result is returned. The `ctx.getOutputStream().flush();` is a trick to force SQLcl to flush output on the console. This improves the user experience when processing a lot of files (see the video at the end of this blog post).<\/p>\n\n\n\n
You can store this JavaScript file along with the three example files in a directory of your choice. Then change to this directory and start SQLcl and execute the highlighted commands below (use host dir<\/code> when you are using Windows):<\/p>\n\n\n\n
run default_format.js<\/span><\/path><\/path><\/svg><\/span>
sql<\/span> <\/span>\/nolog<\/span><\/span>\n<\/span>\nSQLcl:<\/span> <\/span>Release<\/span> <\/span>20.2<\/span> <\/span>Production<\/span> <\/span>on<\/span> <\/span>Sun<\/span> <\/span>Aug<\/span> <\/span>09<\/span> <\/span>16<\/span>:16:19<\/span> <\/span>2020<\/span><\/span>\n<\/span>\nCopyright<\/span> (c) 1982, 2020, Oracle.  All rights reserved.<\/span><\/span>\n<\/span>\n<\/span>\nSQL><\/span> <\/span>host<\/span> <\/span>ls<\/span><\/span>\ndefault_format.js<\/span>\t<\/span>package_body.sql<\/span>\t<\/span>query.sql<\/span>\t\t<\/span>syntax_error.sql<\/span><\/span>\n<\/span>\nSQL><\/span> <\/span>script<\/span> <\/span>default_format.js<\/span><\/span>\n<\/span>\nplease<\/span> <\/span>provide<\/span> <\/span>the<\/span> <\/span>root<\/span> <\/span>path<\/span> <\/span>to<\/span> <\/span>a<\/span> <\/span>directory<\/span> <\/span>with<\/span> <\/span>.sql<\/span> <\/span>files.<\/span><\/span>\n<\/span>\nSQL><\/span> <\/span>script<\/span> <\/span>default_format.js<\/span> <\/span>.<\/span><\/span>\n<\/span>\nFormatting<\/span> <\/span>file<\/span> <\/span>1<\/span> <\/span>of<\/span> <\/span>3<\/span>:<\/span> <\/span>.\/query.sql...<\/span> <\/span>done.<\/span><\/span>\nFormatting<\/span> <\/span>file<\/span> <\/span>2<\/span> <\/span>of<\/span> <\/span>3<\/span>:<\/span> <\/span>.\/syntax_error.sql...<\/span> <\/span>done.<\/span><\/span>\nFormatting<\/span> <\/span>file<\/span> <\/span>3<\/span> <\/span>of<\/span> <\/span>3<\/span>:<\/span> <\/span>.\/package_body.sql...<\/span> <\/span>done.<\/span><\/span>\nSQL><\/span> <\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
Here are the original and formatted versions side-by-side:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
The first two files are certainly easier to read now. However, the syntax_error.sql<\/code> looks strange. The reason is, that the formatter is designed for interactive use and the SQL Developer team decided to format with a best effort approach, even if syntax errors are found. It’s important to note that a detected syntax error does not necessarily mean that the code is incorrect. It just means that the parser does not understand the code. This may happen due to bugs or because grammar changes are not (yet) supported by the parser.<\/p>\n\n\n\n
Shortcomings to Address<\/h2>\n\n\n\n
You’ve seen that applying the formatter is quite easy. However, there are some shortcomings:<\/p>\n\n\n\n
SQLcl 20.2 uses the Nashorn JavaScript engine. This works also with Java 11. If you are interested in writing JavaScript scripts for SQLcl I recommend having a look at Menno Hoogendijk’s GitHub repo<\/a> and the examples in Oracle’s GitHub repo<\/a>.<\/p>\n\n\n\n