Open-source tool: Simple example of syntax conversion for batch SQL code: 'ORACLE START WITH CONNECT' syntax conversion

zgl-20053779 — Sat, 05 Sep 2026 03:09:18 +0000

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered.

Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools?

Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches.

For example：

Suppose 'ORACLE START WITH CONNECT' syntax code（ start_with_connect.sql ）：

SELECT *
FROM tree 
START WITH id = 1
CONNECT BY NOCYCLE PRIOR id = parentid
;

By configuring the conversion rules, the above code can be directly converted into the following code（convert to "with recursive" syntax）：

with recursive wr_tree as 
( 
SELECT id, parentid, 1 as level from tree where id = 1 
union 
SELECT tree.id, tree.parentid, level + 1 from tree, wr_tree where tree.parentid = wr_tree.id 
) 
SELECT * from wr_tree order by id
;

Conversion rule (STATR_WITH_CONNECT_SQL_REPLACE.syn) is as follows：

__DEF_FUZZY__             Y
__DEF_DEBUG__             N
__DEF_CASE_SENSITIVE__    N

__DEF_LINE_COMMENT__      -- 
__DEF_LINES_COMMENT__     /*     */


__DEF_STR__   __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_


__DEF_PATH__    __START_WITH_CONNECT__
1             : sel        @ %__IF_KW__            | select
              : cc         @                       | *
              : frm        @                       | from
              : srctab     @                       | __NAME__
              : sta        @ %__IF_KW__            | start
              : wth        @ %__IF_KW__            | with
              : swp        @                       | __NAME__
              : dy1        @                       | =
              : int        @                       | __INT__
              : str        @                       + __STRING__
              : cnn        @ %__IF_KW__            | connect
              : by         @ %__IF_KW__            | by
              : ncy        @ %__IF_KW__ CAN_SKIP   | nocycle
              : prr1       @ %__IF_KW__ CAN_SKIP   | prior
              : col1       @                       | __NAME__
              : dy         @                       | =
              : col2       @                       | __NAME__
              : end        @                       | ;
-----------------------------------------------------------------------
1             : sel        @                       | with
              : sel        @                       | recursive
              : sel        @                       | wr_
              : srctab     @                       \ __NAME__
              : sel        @ STRING                | as
              : sel        @                       | __\n__
              : sel        @                       | (
              : sel        @                       | __\n__
              : sel        @                       | select
              : col1       @                       / __NAME__
              : col2       @                       \ ,
              : col2       @                       / __NAME__
              : sel        @ STRING                \ , 1 as level from
              : srctab     @                       / __NAME__
              : sta        @                       / where
              : swp        @                       / __NAME__
              : dy1        @                       / =
              : int        @                       / __INT__
              : str        @                       / __STRING__
              : sel        @                       | __\n__
              : sel        @                       | union
              : sel        @                       | __\n__
              : sel        @                       | select
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col1       @                       \ __NAME__
              : col2       @                       \ ,
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col2       @                       \ __NAME__
              : sel        @ STRING                \ , level + 1 from
              : srctab     @                       / __NAME__
              : srctab     @                       \ ,
              : sel        @                       / wr_
              : srctab     @                       \ __NAME__
              : sta        @                       / where
              : srctab     @                       / __NAME__
              : srctab     @                       \ .
              : col2       @                       \ __NAME__
              : dy1        @                       / =
              : sel        @                       / wr_
              : srctab     @                       \ __NAME__
              : srctab     @                       \ .
              : col1       @                       \ __NAME__
              : sel        @                       | __\n__
              : sel        @ STRING                | )
              : sel        @                       | __\n__
              : sel        @                       | select
              : sel        @                       | *
              : sel        @ STRING                | from
              : sel        @                       | wr_
              : srctab     @                       \ __NAME__
              : sel        @                       / order
              : sel        @                       / by
              : col1       @                       / __NAME__
              : end        @                       | ;


__DEF_STR__   __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view 


__DEF_STR__   __INT__
<1,100>
[1,100]0123456789


__DEF_SUB_PATH__   __STRING__
1       : x1                  | '
        : x2                  | __ANY__
        : x3                  | '

Execution command ：

ZGLanguage -e STATR_WITH_CONNECT_SQL_REPLACE.syn -r start_with_connect.sql -o result.sql

DEV Community: zgl-20053779

Open-source tool: Simple example of syntax conversion for batch SQL code: 'ORACLE START WITH CONNECT' syntax conversion

For example：