PL/SQL无语句分隔符文件拆分解决方案问询

Great question! Dealing with PL/SQL code where statements are merged without proper separators (like the / you mentioned) is a common headache from human error, but there are several reliable ways to auto-identify, split, and format these correctly:

1. Oracle SQL Developer (Built-in Formatting) #

Oracle's official SQL Developer has robust PL/SQL parsing logic that can handle this seamlessly:

• Paste your merged code into the editor, then use the Format button (or Ctrl+F7 shortcut). It will automatically detect the boundaries between statements like ALTER TABLE and CREATE INDEX, split them, and append the standard / separator to each.
• Bonus: It handles complex PL/SQL blocks (stored procs, triggers, nested logic) perfectly, so you won’t get false splits inside nested structures.

2. PL/SQL Developer’s Format SQL Feature #

If you use the popular PL/SQL Developer client, its formatting tool works just as well:

• Paste the merged text, right-click, and select Format → Format SQL. The tool will parse the syntax, split the merged statements, and add the required separators while cleaning up whitespace (like the extra spaces inside your index definition).

3. Open-Source SQL Parsers (For Custom Workflows) #

If you need to build this into your own application or script, use dedicated SQL parsing libraries:

• ANTLR: There’s a pre-built PL/SQL grammar (PLSQL.g4) you can use to build a parser. It will parse your code into an abstract syntax tree (AST), letting you extract individual statements and append separators programmatically.
• SQLGlot: A Python-based library with full Oracle PL/SQL support. You can parse the merged text, iterate over the AST to pull out each top-level statement, then re-output them with proper / separators.

Quick Manual Fix (For Simple DDL Only) #

For small batches of simple DDL statements (no nested PL/SQL), you can use a regex as a quick fix (note: this won’t work for complex blocks):

• Use a regex to match the start of new DDL statements and insert a separator before them. Here’s a Python example:

import re
merged_sql = "ALTER TABLE XXX ADD XX NUMBER(4) CREATE INDEX XXX ON XX( XXX ASC ) TABLESPACE %IDX%"
# Insert / before new DDL keywords, then add final /
formatted = re.sub(r'(?i)(ALTER|CREATE|DROP|TRUNCATE)', r'\n/\n\1', merged_sql).strip()
formatted += "\n/"
print(formatted)

• This will output exactly the formatted code you’re looking for, but avoid it for anything beyond basic DDL—it can’t handle nested logic.

内容的提问来源于stack exchange，提问作者Mohamad Sefati