This is an automated email from the ASF dual-hosted git repository.
terrymanu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new b7054656712 Support parsing Oracle CREATE TABLE with ORGANIZATION
EXTERNAL clause (#39524)
b7054656712 is described below
commit b7054656712e4b94541b0a89bf10932b3290766c
Author: Liang Zhang <[email protected]>
AuthorDate: Thu Aug 20 01:06:38 2026 +0800
Support parsing Oracle CREATE TABLE with ORGANIZATION EXTERNAL clause
(#39524)
- add RECORDS, DELIMITED, FIXED, FIELDS keyword tokens with unreservedWord
fallback
- support parenthesized ACCESS PARAMETERS with RECORDS FIXED/DELIMITED BY
and FIELDS RTRIM/LTRIM field lists
- accept quoted location specifier and single location entry in LOCATION
clause
- add 5 parser IT cases for the CREATE TABLE statements from issue #27096
Fixes #27096
---
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 6 +-
.../src/main/antlr4/imports/oracle/DDLStatement.g4 | 27 ++++++-
.../main/antlr4/imports/oracle/OracleKeyword.g4 | 16 +++++
.../src/main/resources/case/ddl/create-table.xml | 83 ++++++++++++++++++++++
.../resources/sql/supported/ddl/create-table.xml | 5 ++
5 files changed, 133 insertions(+), 4 deletions(-)
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
index 0a008e3b204..47b7822adb8 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/BaseRule.g4
@@ -82,7 +82,7 @@ identifier
;
unreservedWord
- : SELECT | INSERT | UPDATE | DELETE | CREATE | ALTER | DROP | TRUNCATE |
SCHEMA | GRANT | REVOKE | ADD | SET | TABLE | COLUMN | INDEX | CONSTRAINT |
PRIMARY | UNIQUE | FOREIGN | KEY | POSITION | PRECISION | FUNCTION |
CONSTRUCTOR | RESULT | TRIGGER | MAP | PROCEDURE | SPECIFICATION | VIEW | INTO
| VALUES | WITH | UNION | DISTINCT | CASE | WHEN | CAST | TRIM | SUBSTRING |
FROM | NATURAL | JOIN | FULL | INNER | OUTER | LEFT | RIGHT | CROSS | USING |
WHERE | AS | ON | IF | ELSE | ELSIF [...]
+ : SELECT | INSERT | UPDATE | DELETE | CREATE | ALTER | DROP | TRUNCATE |
SCHEMA | GRANT | REVOKE | ADD | SET | TABLE | COLUMN | INDEX | CONSTRAINT |
PRIMARY | UNIQUE | FOREIGN | KEY | POSITION | PRECISION | FUNCTION |
CONSTRUCTOR | RESULT | TRIGGER | MAP | PROCEDURE | SPECIFICATION | VIEW | INTO
| VALUES | WITH | UNION | DISTINCT | CASE | WHEN | CAST | TRIM | SUBSTRING |
FROM | NATURAL | JOIN | FULL | INNER | OUTER | LEFT | RIGHT | CROSS | USING |
WHERE | AS | ON | IF | ELSE | ELSIF [...]
| BEQUEATH | BINARY | ESCAPE | MOD | XOR | ROW | ROWS | UNKNOWN | ALWAYS |
CASCADE | CHECK | CONVERSION | GENERATED | PRIVILEGES | READ | WRITE |
REFERENCES | START | TRANSACTION | USER | ROLE | VISIBLE | INVISIBLE | EXECUTE
| USE | DEBUG | UNDER | FLASHBACK | ARCHIVE | REFRESH | QUERY | REWRITE | KEEP
| SEQUENCE | INHERIT | TRANSLATE | MERGE | AT | BITMAP | CACHE | NOCACHE |
CHECKPOINT | CONNECT | CONSTRAINTS | CYCLE | NOCYCLE | DBTIMEZONE | ENCRYPT |
DECRYPT | DEFERRABLE | DEFERRED [...]
| SINGLE_K | SINGLE_M | SINGLE_G | SINGLE_T | SINGLE_P | SINGLE_E |
SINGLE_H | V1
;
@@ -280,7 +280,7 @@ lobSegname
;
locationSpecifier
- : identifier
+ : identifier | STRING_
;
xmlSchemaURLName
@@ -1976,5 +1976,5 @@ optionallyEnclosedBySpec
;
lrtrimSpec
- : LRTRIM
+ : LTRIM | RTRIM | LRTRIM
;
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
index 03186aa0efc..80ea5d2c344 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/DDLStatement.g4
@@ -1096,7 +1096,32 @@ externalTableClause
;
externalTableDataProps
- : (DEFAULT DIRECTORY directoryName)? (ACCESS PARAMETERS ((opaqueFormatSpec
delimSpec)? | USING CLOB subquery))? (LOCATION LP_ (directoryName |
(directoryName COLON_)? locationSpecifier (COMMA_ (directoryName COLON_)?
locationSpecifier)+) RP_)?
+ : (DEFAULT DIRECTORY directoryName)? (ACCESS PARAMETERS
(parenthesizedOpaqueFormatSpec | (opaqueFormatSpec delimSpec)? | USING CLOB
subquery))? (LOCATION LP_ (directoryName | (directoryName COLON_)?
locationSpecifier (COMMA_ (directoryName COLON_)? locationSpecifier)*) RP_)?
+ ;
+
+parenthesizedOpaqueFormatSpec
+ : LP_ recordFormatInfo? fieldList RP_
+ ;
+
+recordFormatInfo
+ : RECORDS (FIXED INTEGER_ | DELIMITED BY STRING_)
+ ;
+
+fieldList
+ : FIELDS delimSpec? (LP_ fieldDefinition (COMMA_ fieldDefinition)* RP_)?
+ ;
+
+fieldDefinition
+ : columnName positionSpecification? dataType?
+ ;
+
+positionSpecification
+ : LP_ positionBound COLON_ positionBound RP_
+ ;
+
+positionBound
+ : ASTERISK_ (PLUS_ | MINUS_)? INTEGER_?
+ | (PLUS_ | MINUS_)? INTEGER_
;
mappingTableClause
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
index d3a0d54efa8..34096b6e987 100644
---
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
+++
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/OracleKeyword.g4
@@ -1623,6 +1623,10 @@ FINISH
: F I N I S H
;
+FIXED
+ : F I X E D
+ ;
+
LOGICAL
: L O G I C A L
;
@@ -2019,6 +2023,10 @@ FETCH
: F E T C H
;
+FIELDS
+ : F I E L D S
+ ;
+
PERCENT
: P E R C E N T
;
@@ -3100,6 +3108,10 @@ RECORD
: R E C O R D
;
+RECORDS
+ : R E C O R D S
+ ;
+
CONSTANT
: C O N S T A N T
;
@@ -3760,6 +3772,10 @@ DELETEXML
:D E L E T E X M L
;
+DELIMITED
+ :D E L I M I T E D
+ ;
+
DENSE_RANKM
:D E N S E UL_ R A N K M
;
diff --git a/test/it/parser/src/main/resources/case/ddl/create-table.xml
b/test/it/parser/src/main/resources/case/ddl/create-table.xml
index bb5bd055b1a..31e8112d5f0 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-table.xml
@@ -2982,6 +2982,89 @@
</column-definition>
</create-table>
+ <create-table sql-case-id="create_table_with_clob" >
+ <table name="docs" start-index="13" stop-index="16" />
+ <column-definition type="NUMBER" start-index="19" stop-index="30">
+ <column name="docid" start-index="19" stop-index="23"/>
+ </column-definition>
+ <column-definition type="DATE" start-index="33" stop-index="45">
+ <column name="pub_date" start-index="33" stop-index="40"/>
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="48" stop-index="66">
+ <column name="author" start-index="48" stop-index="53"/>
+ </column-definition>
+ <column-definition type="VARCHAR2" start-index="69" stop-index="89">
+ <column name="category" start-index="69" stop-index="76"/>
+ </column-definition>
+ <column-definition type="CLOB" start-index="92" stop-index="104">
+ <column name="document" start-index="92" stop-index="99"/>
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_fields_rtrim" >
+ <table name="emp_load" start-index="13" stop-index="20" />
+ <column-definition type="CHAR" start-index="23" stop-index="41">
+ <column name="first_name" start-index="23" stop-index="32"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="44" stop-index="61">
+ <column name="last_name" start-index="44" stop-index="52"/>
+ </column-definition>
+ <column-definition type="INT" start-index="64" stop-index="80">
+ <column name="year_of_birth" start-index="64" stop-index="76"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="83" stop-index="96">
+ <column name="phone" start-index="83" stop-index="87"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="99" stop-index="115">
+ <column name="area_code" start-index="99" stop-index="107"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="118" stop-index="133">
+ <column name="exchange" start-index="118" stop-index="125"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="136" stop-index="152">
+ <column name="extension" start-index="136" stop-index="144"/>
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_fields_ltrim" >
+ <table name="emp_load" start-index="13" stop-index="20" />
+ <column-definition type="CHAR" start-index="23" stop-index="41">
+ <column name="first_name" start-index="23" stop-index="32"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="44" stop-index="61">
+ <column name="last_name" start-index="44" stop-index="52"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="64" stop-index="84">
+ <column name="year_of_birth" start-index="64" stop-index="76"/>
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_records_delimited_by" >
+ <table name="emp_load" start-index="13" stop-index="20" />
+ <column-definition type="CHAR" start-index="23" stop-index="41">
+ <column name="first_name" start-index="23" stop-index="32"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="44" stop-index="61">
+ <column name="last_name" start-index="44" stop-index="52"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="64" stop-index="84">
+ <column name="year_of_birth" start-index="64" stop-index="76"/>
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_with_records_fixed" >
+ <table name="emp_load" start-index="13" stop-index="20" />
+ <column-definition type="CHAR" start-index="23" stop-index="41">
+ <column name="first_name" start-index="23" stop-index="32"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="44" stop-index="61">
+ <column name="last_name" start-index="44" stop-index="52"/>
+ </column-definition>
+ <column-definition type="CHAR" start-index="64" stop-index="84">
+ <column name="year_of_birth" start-index="64" stop-index="76"/>
+ </column-definition>
+ </create-table>
+
<create-table sql-case-id="create_table_with_builtin_function">
<table name="tab" start-index="13" stop-index="15"/>
<select>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
index b97602817f6..d5bca5630c3 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
@@ -354,6 +354,11 @@
<sql-case id="create_table_parallel_with_as_sub_query" value="CREATE TABLE
hr.admin_emp_dept PARALLEL COMPRESS AS SELECT * FROM hr.employees WHERE
department_id = 10;" db-types="Oracle"/>
<sql-case id="create_table_with_edge_constraint" value="CREATE TABLE
bought ( PurchaseCount INT ,CONSTRAINT EC_BOUGHT CONNECTION (Customer TO
Product) ON DELETE NO ACTION );" db-types="SQLServer"/>
<sql-case id="create_table_with_enclosed" value="CREATE TABLE
emp_load(first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS
PARAMETERS FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '(' and ')'LRTRIM
LOCATION (info.dat));" db-types="Oracle"/>
+ <sql-case id="create_table_with_clob" value="CREATE TABLE docs (docid
NUMBER, pub_date DATE, author VARCHAR2(30), category VARCHAR2(30), document
CLOB)" db-types="Oracle"/>
+ <sql-case id="create_table_with_fields_rtrim" value="CREATE TABLE emp_load
(first_name CHAR(15), last_name CHAR(20), year_of_birth INT, phone CHAR(12),
area_code CHAR(3), exchange CHAR(3), extension CHAR(4)) ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (FIELDS
RTRIM (first_name (1:15) CHAR(15), last_name (*:+20), year_of_birth (36:39),
phone (40:52), area_code (*-12: +3), exchange (*+1: +3), extension (*+1: +4)))
LOCATION ('info.dat'))" db [...]
+ <sql-case id="create_table_with_fields_ltrim" value="CREATE TABLE emp_load
(first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4)) ORGANIZATION
EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS
(FIELDS LTRIM) LOCATION ('info.dat'))" db-types="Oracle"/>
+ <sql-case id="create_table_with_records_delimited_by" value="CREATE TABLE
emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS
PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ',' (first_name
CHAR(7), last_name CHAR(8), year_of_birth CHAR(4))) LOCATION ('info.dat'))"
db-types="Oracle"/>
+ <sql-case id="create_table_with_records_fixed" value="CREATE TABLE
emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS
PARAMETERS (RECORDS FIXED 20 FIELDS (first_name CHAR(7), last_name CHAR(8),
year_of_birth CHAR(4))) LOCATION ('info.dat'))" db-types="Oracle"/>
<sql-case id="create_table_with_builtin_function" value="CREATE TABLE tab
AS SELECT DBMS_LOB.GETLENGTH@dbs2(clob_col) len FROM tab@dbs2;"
db-types="Oracle"/>
<sql-case id="create_table_with_autoextend_size" value="CREATE TABLE t1
(c1 INT) AUTOEXTEND_SIZE = 4M" db-types="MySQL"/>
<sql-case id="create_table_with_cast_function1" value="create table
test_schema.tb_ods_bs_class_all as
