In-Database SQL formatter

May 14, 2025

1–2 minutes

When I’m troubleshooting, a common workflow for me is

extract a problematic SQL statement from V$SQL or V$SQLSTATS or an AWR report.
drop the SQL text into SQL Developer or VS Code so that I can format it into a more readable format.

That was starting to frustrate me so I thought: “Why not have a SQL Formatter right there inside the database?”

So I headed over to https://github.com/vertical-blank/sql-formatter where you can grab a Java implementation of the popular https://www.npmjs.com/package/sql-formatter.

(For 23ai, I could have used the Javascript implementation directly using our native engine but using the Java version let me load it into earlier versions as well).

Once I’d done “mvn clean install”, I simply loaded by resultant jar file into the database, and put a PL/SQL wrapper around it.


loadjava -user scott/tiger sql-formatter-2.0.5.jar

SQL> create or replace function format_sql(p_sqlin varchar2)
  2  return varchar2 is
  3  language java name 'com.github.vertical_blank.sqlformatter.SqlFormatter.format(java.lang.String) return java.lang.String';
  4  /

Function created.

And voila! A SQL formatter right there in the database


SQL> select format_sql('select * from table1') txt from dual;

TXT
----------------------------------------------------------------------
select
  *
from
  table1

3 responses to “In-Database SQL formatter”

Jared

May 14, 2025 at 11:47 pm

Cool!

Of course the usual Java hell was soon encountered.

This was remedied by deinstalling all non-system Java (probably will break something) and installing maven and jdk 24.

When installing maven, it is probably best to install it your self from the apache binary distribution, as dnf/yum/apt will likely install a version that is too old.

Now this:

1 select format_sql(sql_fulltext)
2 from v$sqlarea
3* where sql_id = ‘0spd9scyf6p39’ and rownum < 2 SQL> / FORMAT_SQL(SQL_FULLTEXT)

select
s.username,
s.sid,
s.serial #,
s.service_name,
s.sql_id,
p.pid ppid,
s.status,
i.block_changes,
s.machine,
s.osuser,
p.spid spid,
substr(s.program, 1, 20) client_program,
s.process client_process,
–substr(p.program,1,20) server_program,
to_char(logon_time, ‘mm/dd/yy hh24:mi:ss’) logon_time,
— idle time
— days added to hours
–( trunc(LAST_CALL_ET/86400) * 24 ) || ‘:’ ||
— days separately
substr(‘0’ || trunc(LAST_CALL_ET / 86400), -2, 2) || ‘:’ || — hours
substr(‘0’ || trunc(mod(LAST_CALL_ET, 86400) / 3600), -2, 2) || ‘:’ || — minutes
substr(
‘0’ || trunc(mod(mod(LAST_CALL_ET, 86400), 3600) / 60),
-2,
2
) || ‘:’ || –seconds
substr(
‘0’ || mod(mod(mod(LAST_CALL_ET, 86400), 3600), 60),
-2,
2
) idle_time
from
v $ session s,
v $ process p,
v $ sess_io i
where
s.username is not null
and s.sid = i.sid — use outer join to show sniped sessions in
— v$session that don’t have an OS process
and p.addr(+) = s.paddr — uncomment to see only your own session
–and userenv(‘SESSIONID’) = s.audsid
order by
username,
sid

Reply
1. Connor McDonald
  
  May 15, 2025 at 10:06 am
  
  You will note that I omitted the hell I went through to get the JAR file created 🙂
  
  Reply
  1. alainlacour
    
    June 3, 2025 at 5:54 pm
    
    No pain, no gain 😀
    
    Reply