This is an automated email from the ASF dual-hosted git repository.
FlyingZC 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 2c62d065b08 Improve Oracle PL/SQL parsing and SQL binding (#40207)
2c62d065b08 is described below
commit 2c62d065b0898d9c85a233716c51ad51c316f3b6
Author: Zhengqiang Duan <[email protected]>
AuthorDate: Fri Oct 16 18:39:14 2026 +0800
Improve Oracle PL/SQL parsing and SQL binding (#40207)
* Improve Oracle PL/SQL parsing and SQL binding
* Add release note for Oracle PL/SQL parsing and SQL binding
* Fix Oracle declaration metadata and database link handling
* Fix Oracle local type attribute metadata
* Remove change marker comments
---
RELEASE-NOTES.md | 1 +
.../dml/from/type/SimpleTableSegmentBinder.java | 4 +
.../segment/dml/order/GroupBySegmentBinder.java | 4 +
.../from/type/SimpleTableSegmentBinderTest.java | 5 +-
.../src/main/antlr4/imports/oracle/BaseRule.g4 | 5 +-
.../oracle/src/main/antlr4/imports/oracle/PLSQL.g4 | 6 +-
.../statement/type/OracleDDLStatementVisitor.java | 228 +++++++++++++++++++--
.../statement/type/OracleDMLStatementVisitor.java | 8 +-
.../oracle/ddl/OraclePLSQLBlockStatement.java | 20 +-
.../sql/binder/dialect/oracle/OracleBinderIT.java | 79 +++++++
.../asserts/segment/table/TableAssert.java | 12 ++
.../statement/plsql/PLSQLStatementAssert.java | 3 +
.../segment/impl/table/ExpectedSimpleTable.java | 12 ++
.../plsql/OraclePLSQLBlockStatementTestCase.java | 14 ++
.../src/main/resources/case/ddl/create-package.xml | 92 +++++++++
.../main/resources/case/ddl/create-procedure.xml | 8 +
.../src/main/resources/case/ddl/plsql-block.xml | 115 ++++++++++-
.../parser/src/main/resources/case/dml/delete.xml | 2 +-
.../parser/src/main/resources/case/dml/insert.xml | 18 +-
.../parser/src/main/resources/case/dml/select.xml | 20 ++
.../parser/src/main/resources/case/dml/update.xml | 4 +-
.../main/resources/case/plsql/create-function.xml | 1 +
.../main/resources/case/plsql/create-procedure.xml | 18 ++
.../resources/sql/supported/ddl/create-package.xml | 9 +
.../sql/supported/ddl/create-procedure.xml | 1 +
.../resources/sql/supported/ddl/plsql-block.xml | 21 ++
.../main/resources/sql/supported/dml/insert.xml | 1 +
.../main/resources/sql/supported/dml/select.xml | 2 +
.../sql/supported/plsql/create-function.xml | 1 +
.../sql/supported/plsql/create-procedure.xml | 3 +
30 files changed, 688 insertions(+), 29 deletions(-)
diff --git a/RELEASE-NOTES.md b/RELEASE-NOTES.md
index 7530cf7f48b..e59ea5f87ab 100644
--- a/RELEASE-NOTES.md
+++ b/RELEASE-NOTES.md
@@ -160,6 +160,7 @@
1. SQL Parser: Support Oracle pseudo-record collection index parsing -
[#39566](https://github.com/apache/shardingsphere/pull/39566)
1. SQL Parser: Support Oracle DML error logging parser model and binder -
[#39711](https://github.com/apache/shardingsphere/pull/39711)
1. SQL Parser: Support Oracle PL/SQL syntax parsing and binding -
[#39723](https://github.com/apache/shardingsphere/pull/39723)
+1. SQL Parser: Enhance Oracle PL/SQL and database-link parsing and SQL binding
- [#40207](https://github.com/apache/shardingsphere/pull/40207)
1. SQL Parser: Support scalar expressions in Oracle `IN` predicates -
[#39876](https://github.com/apache/shardingsphere/pull/39876)
1. SQL Parser: Support Oracle `ORDER BY` after `FOR UPDATE` and `HAVING`
without `GROUP BY` -
[#39877](https://github.com/apache/shardingsphere/pull/39877)
1. SQL Parser: Support Oracle parenthesized `WITH` queries and qualified
PL/SQL collection types -
[#39914](https://github.com/apache/shardingsphere/pull/39914)
diff --git
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
index c4fc8227a30..418dfce2d50 100644
---
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
+++
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinder.java
@@ -112,6 +112,8 @@ public final class SimpleTableSegmentBinder {
SimpleTableSegment result = new SimpleTableSegment(tableNameSegment);
segment.getOwner().ifPresent(result::setOwner);
segment.getAliasSegment().ifPresent(result::setAlias);
+ segment.getAt().ifPresent(result::setAt);
+ segment.getDbLink().ifPresent(result::setDbLink);
result.setTableSampled(segment.isTableSampled());
segment.getTableSampleExpression().map(optional ->
ExpressionSegmentBinder.bind(optional, SegmentType.JOIN_ON, binderContext,
tableBinderContexts, LinkedHashMultimap.create()))
.ifPresent(result::setTableSampleExpression);
@@ -286,6 +288,8 @@ public final class SimpleTableSegmentBinder {
SimpleTableSegment result = new SimpleTableSegment(tableNameSegment);
fromTableOwner.ifPresent(result::setOwner);
result.setAlias(segment.getAliasSegment().orElseGet(() -> new
AliasSegment(segment.getTableName().getStartIndex(),
segment.getTableName().getStopIndex(), tableName)));
+ segment.getAt().ifPresent(result::setAt);
+ segment.getDbLink().ifPresent(result::setDbLink);
result.setTableSampled(segment.isTableSampled());
segment.getTableSampleExpression().map(optional ->
ExpressionSegmentBinder.bind(optional, SegmentType.JOIN_ON, binderContext,
tableBinderContexts, LinkedHashMultimap.create()))
.ifPresent(result::setTableSampleExpression);
diff --git
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/order/GroupBySegmentBinder.java
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/order/GroupBySegmentBinder.java
index ea70853af76..a6045e1df37 100644
---
a/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/order/GroupBySegmentBinder.java
+++
b/infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/order/GroupBySegmentBinder.java
@@ -27,6 +27,7 @@ import
org.apache.shardingsphere.infra.binder.engine.segment.dml.order.item.Orde
import
org.apache.shardingsphere.infra.binder.engine.statement.SQLStatementBinderContext;
import
org.apache.shardingsphere.infra.exception.kernel.metadata.ColumnNotFoundException;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.GroupBySegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.ColumnOrderByItemSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.order.item.OrderByItemSegment;
import java.util.Collection;
@@ -62,6 +63,9 @@ public final class GroupBySegmentBinder {
private static OrderByItemSegment bind(final SQLStatementBinderContext
binderContext, final Multimap<CaseInsensitiveString, TableSegmentBinderContext>
currentTableBinderContexts,
final
Multimap<CaseInsensitiveString, TableSegmentBinderContext> tableBinderContexts,
final
Multimap<CaseInsensitiveString, TableSegmentBinderContext>
outerTableBinderContexts, final OrderByItemSegment groupByItemSegment) {
+ if (groupByItemSegment instanceof ColumnOrderByItemSegment &&
((ColumnOrderByItemSegment)
groupByItemSegment).getColumn().getOwner().isPresent()) {
+ return OrderByItemSegmentBinder.bind(groupByItemSegment,
binderContext, tableBinderContexts, outerTableBinderContexts,
SegmentType.GROUP_BY);
+ }
try {
return OrderByItemSegmentBinder.bind(groupByItemSegment,
binderContext, currentTableBinderContexts, outerTableBinderContexts,
SegmentType.GROUP_BY);
} catch (final ColumnNotFoundException ignored) {
diff --git
a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinderTest.java
b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinderTest.java
index cbf5df4e600..4b7e845f7e4 100644
---
a/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinderTest.java
+++
b/infra/binder/core/src/test/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/from/type/SimpleTableSegmentBinderTest.java
@@ -84,11 +84,14 @@ class SimpleTableSegmentBinderTest {
@Test
void assertBindWithDBLinkContainsDBLink() {
SimpleTableSegment simpleTableSegment = new SimpleTableSegment(new
TableNameSegment(0, 10, new IdentifierValue("t_not_exists")));
+ simpleTableSegment.setAt(new IdentifierValue("@"));
simpleTableSegment.setDbLink(new IdentifierValue("foo_db_link"));
ShardingSphereMetaData metaData = createMetaData();
Multimap<CaseInsensitiveString, TableSegmentBinderContext>
tableBinderContexts = LinkedHashMultimap.create();
- SimpleTableSegmentBinder.bind(simpleTableSegment,
+ SimpleTableSegment actual =
SimpleTableSegmentBinder.bind(simpleTableSegment,
new SQLStatementBinderContext(metaData, "foo_db", new
HintValueContext(),
SelectStatement.builder().databaseType(databaseType).build()),
tableBinderContexts);
+ assertThat(actual.getAt().get().getValue(), is("@"));
+ assertThat(actual.getDbLink().get().getValue(), is("foo_db_link"));
SimpleTableSegmentBinderContext tableSegmentBinderContext =
(SimpleTableSegmentBinderContext)
tableBinderContexts.values().iterator().next();
assertTrue(tableSegmentBinderContext.isContainsDBLink());
}
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 316dbf6ba60..7243b03fb49 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
@@ -108,7 +108,7 @@ materializedViewName
;
columnName
- : (owner? DOT_)? name (DOT_ nestedItem)*
+ : (owner? DOT_)? name (DOT_ nestedItem)* (AT_ dbLink)?
;
objectName
@@ -145,6 +145,7 @@ rollbackSegmentName
typeName
: (owner DOT_)? name
+ | schemaName DOT_ owner DOT_ name
;
indexTypeName
@@ -670,7 +671,7 @@ extractFunction
;
regularFunction
- : (owner DOT_)* regularFunctionName LP_ (expr (COMMA_ expr)* | ASTERISK_)?
RP_
+ : (owner DOT_)* regularFunctionName (AT_ dbLink)? LP_ (expr (COMMA_ expr)*
| ASTERISK_)? RP_
;
regularFunctionName
diff --git
a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
index e8e90c0dd44..66ec1d1f07e 100644
--- a/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
+++ b/parser/sql/engine/dialect/oracle/src/main/antlr4/imports/oracle/PLSQL.g4
@@ -463,7 +463,7 @@ plsqlBlock
;
procedureCall
- : (packageName DOT_)? procedureName (LP_ (procedureCallParameter (COMMA_
procedureCallParameter)*)? RP_)? SEMI_
+ : (packageName DOT_)? procedureName (AT_ dbLink)? (LP_
(procedureCallParameter (COMMA_ procedureCallParameter)*)? RP_)? SEMI_
;
procedureCallParameter
@@ -651,10 +651,10 @@ typeDefinition
;
recordTypeDefinition
- : TYPE typeName IS RECORD LP_ fieldDefinition (COMMA_ fieldDefinition)*
RP_ SEMI_
+ : TYPE typeName IS RECORD LP_ recordFieldDefinition (COMMA_
recordFieldDefinition)* RP_ SEMI_
;
-fieldDefinition
+recordFieldDefinition
: typeName dataType ((NOT NULL)? (ASSIGNMENT_OPERATOR_ | DEFAULT) expr)?
;
diff --git
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
index c9ece94fcec..db0adc40a0f 100644
---
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
+++
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDDLStatementVisitor.java
@@ -20,7 +20,10 @@ package
org.apache.shardingsphere.sql.parser.engine.oracle.visitor.statement.typ
import org.antlr.v4.runtime.ParserRuleContext;
import org.antlr.v4.runtime.misc.Interval;
import org.antlr.v4.runtime.tree.ParseTree;
+import org.antlr.v4.runtime.tree.RuleNode;
+import
org.apache.shardingsphere.database.connector.core.metadata.database.enums.QuoteCharacter;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
+import org.apache.shardingsphere.infra.annotation.HighFrequencyInvocation;
import org.apache.shardingsphere.sql.parser.api.ASTNode;
import
org.apache.shardingsphere.sql.parser.api.visitor.statement.type.DDLStatementVisitor;
import org.apache.shardingsphere.sql.parser.autogen.OracleStatementBaseVisitor;
@@ -408,12 +411,18 @@ import
org.apache.shardingsphere.sql.parser.statement.oracle.ddl.view.OracleDrop
import
org.apache.shardingsphere.sql.parser.statement.oracle.ddl.zone.OracleAlterMaterializedZoneMapStatement;
import
org.apache.shardingsphere.sql.parser.statement.oracle.ddl.zone.OracleDropMaterializedZoneMapStatement;
+import java.util.ArrayDeque;
+import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
+import java.util.Deque;
+import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.LinkedList;
import java.util.List;
+import java.util.Locale;
+import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;
@@ -1039,11 +1048,11 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
}
private TypeAttributeSegments extractTypeAttributeSegments(final
Collection<? extends ParseTree> contexts) {
- TypeAttributeSegments result = new TypeAttributeSegments();
+ TypeAttributeExtractor extractor = new TypeAttributeExtractor();
for (ParseTree each : contexts) {
- result.addAll(extractTypeAttributeSegments(each));
+ extractor.visit(each);
}
- return result;
+ return extractor.getResult();
}
private SimpleTableSegment createTypeAttributeTable(final
ObjectNameContext ctx) {
@@ -1053,7 +1062,11 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
private SimpleTableSegment createTypeAttributeTable(final TypeNameContext
ctx) {
OwnerContext tableName = ctx.owner();
- return new SimpleTableSegment(new
TableNameSegment(tableName.start.getStartIndex(),
tableName.stop.getStopIndex(), (IdentifierValue)
visit(tableName.identifier())));
+ SimpleTableSegment result = new SimpleTableSegment(new
TableNameSegment(tableName.start.getStartIndex(),
tableName.stop.getStopIndex(), (IdentifierValue)
visit(tableName.identifier())));
+ if (null != ctx.schemaName()) {
+ result.setOwner(new
OwnerSegment(ctx.schemaName().start.getStartIndex(),
ctx.schemaName().stop.getStopIndex(), (IdentifierValue)
visit(ctx.schemaName().identifier())));
+ }
+ return result;
}
private ColumnSegment createTypeAttributeColumn(final ObjectNameContext
ctx) {
@@ -2193,6 +2206,7 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
return result;
}
+ @HighFrequencyInvocation
@Override
public ASTNode visitPlsqlBlock(final PlsqlBlockContext ctx) {
if (null != ctx.declareSection()) {
@@ -2204,8 +2218,17 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
visitPlsqlStatementList(each.plsqlStatements());
}
}
+
getSqlStatementsInPlsql().sort(Comparator.comparingInt(SQLStatementSegment::getStartIndex));
getProcedureCallNames().sort(Comparator.comparingInt(ProcedureCallNameSegment::getStartIndex));
- return new OraclePLSQLBlockStatement(getDatabaseType(),
getProcedureCallNames());
+ OraclePLSQLBlockStatement result = new
OraclePLSQLBlockStatement(getDatabaseType(), getProcedureCallNames());
+ TypeAttributeSegments typeAttributeSegments =
extractTypeAttributeSegments(ctx);
+ result.getTables().addAll(typeAttributeSegments.getTables());
+ result.getColumns().addAll(typeAttributeSegments.getColumns());
+ result.getSqlStatements().addAll(getSqlStatementsInPlsql());
+ result.getVariableNames().addAll(getVariableNames());
+ getSqlStatementsInPlsql().forEach(each ->
each.getSqlStatement().getVariableNames().addAll(getVariableNames()));
+
result.getCursorForLoopStatements().addAll(getCursorForLoopStatementSegments());
+ return result;
}
@Override
@@ -2351,9 +2374,162 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
private final TypeAttributeSegments result = new
TypeAttributeSegments();
+ private final Deque<Collection<List<String>>> variableScopes = new
ArrayDeque<>();
+
+ private final Map<List<String>, Integer> variableNames = new
HashMap<>();
+
+ private TypeAttributeExtractor() {
+ variableScopes.push(new LinkedList<>());
+ }
+
+ @Override
+ public Void visitChildren(final RuleNode node) {
+ boolean variableScope = isVariableScope(node);
+ if (variableScope) {
+ variableScopes.push(new LinkedList<>());
+ }
+ if (node instanceof CursorForLoopStatementContext) {
+ registerVariable(((CursorForLoopStatementContext)
node).record().getText(), node);
+ }
+ super.visitChildren(node);
+ if (variableScope) {
+ for (List<String> each : variableScopes.pop()) {
+ variableNames.compute(each, (key, value) -> 1 == value ?
null : value - 1);
+ }
+ }
+ return null;
+ }
+
+ private boolean isVariableScope(final RuleNode node) {
+ return node instanceof PlsqlBlockContext || node instanceof
PlsqlProcedureSourceContext || node instanceof PlsqlFunctionSourceContext ||
node instanceof PlsqlTriggerSourceContext
+ || node instanceof PlsqlPackageSourceContext || node
instanceof PlsqlPackageBodySourceContext || node instanceof
FunctionDefinitionContext
+ || node instanceof ProcedureDefinitionContext || node
instanceof FunctionDeclarationContext || node instanceof
CursorForLoopStatementContext
+ || node instanceof ProcedureDeclarationContext &&
!(node.getParent() instanceof ProcedureDefinitionContext);
+ }
+
+ private void registerVariable(final String identifier, final RuleNode
declaration) {
+ String variableName = normalizeVariableName(identifier);
+ registerVariable(Collections.singletonList(variableName));
+ RuleNode scope = findVariableScope(declaration);
+ for (ParseTree each : getVariableScopeNames(scope)) {
+ String scopeName = normalizeVariableName(each.getText());
+ registerVariable(Arrays.asList(scopeName, variableName));
+ registerQualifiedVariable(scope, scopeName, variableName);
+ }
+ }
+
+ private void registerVariable(final List<String> variableName) {
+ variableScopes.peek().add(variableName);
+ variableNames.merge(variableName, 1, Integer::sum);
+ }
+
+ private RuleNode findVariableScope(final RuleNode node) {
+ RuleNode result = node;
+ while (!isVariableScope(result) && null != result.getParent()) {
+ result = (RuleNode) result.getParent();
+ }
+ return result;
+ }
+
+ private void registerQualifiedVariable(final RuleNode scope, final
String scopeName, final String variableName) {
+ ParseTree schemaName = scope instanceof PlsqlFunctionSourceContext
? ((PlsqlFunctionSourceContext) scope).function().owner()
+ : ((ParserRuleContext)
scope).getRuleContext(SchemaNameContext.class, 0);
+ if (null != schemaName) {
+
registerVariable(Arrays.asList(normalizeVariableName(schemaName.getText()),
scopeName, variableName));
+ }
+ if (null != scope.getParent()) {
+ RuleNode outerScope = findVariableScope((RuleNode)
scope.getParent());
+ for (ParseTree each : getVariableScopeNames(outerScope)) {
+
registerVariable(Arrays.asList(normalizeVariableName(each.getText()),
scopeName, variableName));
+ }
+ }
+ }
+
+ private Collection<? extends ParseTree> getVariableScopeNames(final
RuleNode scope) {
+ if (scope instanceof PlsqlBlockContext) {
+ Collection<ParseTree> result = new
LinkedList<>(((PlsqlBlockContext) scope).label());
+ if (scope.getParent() instanceof StatementContext) {
+ result.addAll(((StatementContext)
scope.getParent()).label());
+ }
+ return result;
+ }
+ if (scope instanceof PlsqlProcedureSourceContext) {
+ return
Collections.singletonList(((PlsqlProcedureSourceContext)
scope).procedureName());
+ }
+ if (scope instanceof PlsqlFunctionSourceContext) {
+ return Collections.singletonList(((PlsqlFunctionSourceContext)
scope).function().name());
+ }
+ if (scope instanceof PlsqlTriggerSourceContext) {
+ return Collections.singletonList(((PlsqlTriggerSourceContext)
scope).triggerName());
+ }
+ if (scope instanceof PlsqlPackageSourceContext) {
+ return Collections.singletonList(((PlsqlPackageSourceContext)
scope).packageName(0));
+ }
+ if (scope instanceof PlsqlPackageBodySourceContext) {
+ return
Collections.singletonList(((PlsqlPackageBodySourceContext)
scope).packageName(0));
+ }
+ if (scope instanceof FunctionDefinitionContext) {
+ return Collections.singletonList(((FunctionDefinitionContext)
scope).functionHeading().functionName());
+ }
+ if (scope instanceof FunctionDeclarationContext) {
+ return Collections.singletonList(((FunctionDeclarationContext)
scope).functionHeading().functionName());
+ }
+ if (scope instanceof ProcedureDefinitionContext) {
+ return Collections.singletonList(((ProcedureDefinitionContext)
scope).procedureDeclaration().procedureHeading().procedureName());
+ }
+ if (scope instanceof ProcedureDeclarationContext) {
+ return
Collections.singletonList(((ProcedureDeclarationContext)
scope).procedureHeading().procedureName());
+ }
+ return scope instanceof CursorForLoopStatementContext ?
((StatementContext) scope.getParent()).label() : Collections.emptyList();
+ }
+
+ private String normalizeVariableName(final String identifier) {
+ IdentifierValue identifierValue = new IdentifierValue(identifier);
+ return QuoteCharacter.NONE == identifierValue.getQuoteCharacter()
? identifierValue.getValue().toUpperCase(Locale.ENGLISH) :
identifierValue.getValue();
+ }
+
+ @Override
+ public Void visitDeclareItem(final DeclareItemContext ctx) {
+ super.visitDeclareItem(ctx);
+ if (null == ctx.itemDeclaration()) {
+ registerDeclaredVariable(ctx);
+ }
+ return null;
+ }
+
+ private void registerDeclaredVariable(final ParserRuleContext
declaration) {
+ VariableNameContext variableName = ((ParserRuleContext)
declaration.getChild(0)).getRuleContext(VariableNameContext.class, 0);
+ if (null != variableName) {
+ registerVariable(variableName.getText(), declaration);
+ }
+ }
+
+ @Override
+ public Void visitItemDeclaration(final ItemDeclarationContext ctx) {
+ super.visitItemDeclaration(ctx);
+ registerDeclaredVariable(ctx);
+ return null;
+ }
+
+ @Override
+ public Void visitPackageSpecificationItem(final
PackageSpecificationItemContext ctx) {
+ super.visitPackageSpecificationItem(ctx);
+ if (null == ctx.itemDeclaration()) {
+ registerDeclaredVariable(ctx);
+ }
+ return null;
+ }
+
+ @Override
+ public Void visitParameterDeclaration(final
ParameterDeclarationContext ctx) {
+ super.visitParameterDeclaration(ctx);
+ registerVariable(ctx.parameterName().getText(), ctx);
+ return null;
+ }
+
@Override
public Void visitTypeAttribute(final TypeAttributeContext ctx) {
- if (null != ctx.objectName() && null != ctx.objectName().owner()) {
+ if (null != ctx.objectName() && null != ctx.objectName().owner()
&& !isLocalTypeAttribute(ctx.objectName().owner().getText(),
ctx.objectName().name().getText())) {
result.getTables().add(createTypeAttributeTable(ctx.objectName()));
result.getColumns().add(createTypeAttributeColumn(ctx.objectName()));
}
@@ -2362,27 +2538,53 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
@Override
public Void visitRowtypeAttribute(final RowtypeAttributeContext ctx) {
- if (null != ctx.objectName()) {
+ if (null != ctx.objectName() &&
!isLocalRowtypeAttribute(ctx.objectName())) {
result.getTables().add(createRowtypeTable(ctx.objectName()));
}
return null;
}
+ private boolean isLocalRowtypeAttribute(final ObjectNameContext ctx) {
+ return null == ctx.owner() ?
variableNames.containsKey(Collections.singletonList(normalizeVariableName(ctx.name().getText())))
+ :
variableNames.containsKey(Arrays.asList(normalizeVariableName(ctx.owner().getText()),
normalizeVariableName(ctx.name().getText())));
+ }
+
@Override
public Void visitRowtype(final RowtypeContext ctx) {
- if (null != ctx.ROWTYPE()) {
+ if (null != ctx.ROWTYPE() && !isLocalVariable(ctx.typeName())) {
result.getTables().add(createRowtypeTable(ctx.typeName()));
- } else if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
ctx.typeName().owner()) {
+ } else if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
ctx.typeName().owner() && !isLocalTypeAttribute(ctx.typeName())) {
result.getTables().add(createTypeAttributeTable(ctx.typeName()));
result.getColumns().add(createTypeAttributeColumn(ctx.typeName()));
}
return null;
}
+ private boolean isLocalVariable(final TypeNameContext ctx) {
+ if (null == ctx.owner()) {
+ return
variableNames.containsKey(Collections.singletonList(normalizeVariableName(ctx.name().getText())));
+ }
+ if (null == ctx.schemaName()) {
+ return
variableNames.containsKey(Arrays.asList(normalizeVariableName(ctx.owner().getText()),
normalizeVariableName(ctx.name().getText())));
+ }
+ return
variableNames.containsKey(Arrays.asList(normalizeVariableName(ctx.schemaName().getText()),
normalizeVariableName(ctx.owner().getText()),
+ normalizeVariableName(ctx.name().getText())));
+ }
+
+ private boolean isLocalTypeAttribute(final TypeNameContext ctx) {
+ return isLocalVariable(ctx) || (null == ctx.schemaName() ?
isLocalTypeAttribute(ctx.owner().getText(), ctx.name().getText())
+ : isLocalTypeAttribute(ctx.schemaName().getText(),
ctx.owner().getText()));
+ }
+
+ private boolean isLocalTypeAttribute(final String owner, final String
name) {
+ String normalizedOwner = normalizeVariableName(owner);
+ return
variableNames.containsKey(Collections.singletonList(normalizedOwner)) ||
variableNames.containsKey(Arrays.asList(normalizedOwner,
normalizeVariableName(name)));
+ }
+
@Override
public Void visitCollectionVariableDecl(final
CollectionVariableDeclContext ctx) {
TypeNameContext typeName = null == ctx.typeName() ?
ctx.collectionTypeName().typeName() : ctx.typeName();
- if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
typeName.owner()) {
+ if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
typeName.owner() && !isLocalTypeAttribute(typeName)) {
result.getTables().add(createTypeAttributeTable(typeName));
result.getColumns().add(createTypeAttributeColumn(typeName));
}
@@ -2391,7 +2593,7 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
@Override
public Void visitRecordVariableDeclaration(final
RecordVariableDeclarationContext ctx) {
- if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
ctx.typeName().owner()) {
+ if (null != ctx.TYPE() && null != ctx.MOD_() && null !=
ctx.typeName().owner() && !isLocalTypeAttribute(ctx.typeName())) {
result.getTables().add(createTypeAttributeTable(ctx.typeName()));
result.getColumns().add(createTypeAttributeColumn(ctx.typeName()));
}
@@ -2402,9 +2604,9 @@ public final class OracleDDLStatementVisitor extends
OracleStatementVisitor impl
public Void visitRefCursorTypeDefinition(final
RefCursorTypeDefinitionContext ctx) {
if (null != ctx.RETURN() && ctx.typeName().size() > 1) {
TypeNameContext returnTypeName = ctx.typeName(1);
- if (null != ctx.ROWTYPE()) {
+ if (null != ctx.ROWTYPE() && !isLocalVariable(returnTypeName))
{
result.getTables().add(createRowtypeTable(returnTypeName));
- } else if (null != ctx.MOD_() && ctx.TYPE().size() > 1 && null
!= returnTypeName.owner()) {
+ } else if (null != ctx.MOD_() && ctx.TYPE().size() > 1 && null
!= returnTypeName.owner() && !isLocalTypeAttribute(returnTypeName)) {
result.getTables().add(createTypeAttributeTable(returnTypeName));
result.getColumns().add(createTypeAttributeColumn(returnTypeName));
}
diff --git
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index 94bbd20b147..fbe5d8665ae 100644
---
a/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/engine/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/engine/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -675,7 +675,8 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitDmlTableClause(final DmlTableClauseContext ctx) {
if (null != ctx.AT_() && null != ctx.dbLink()) {
- SimpleTableSegment result = new SimpleTableSegment(new
TableNameSegment(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), new
IdentifierValue(ctx.tableName().name().getText())));
+ SimpleTableSegment result = new SimpleTableSegment(new
TableNameSegment(ctx.tableName().name().start.getStartIndex(),
ctx.tableName().name().stop.getStopIndex(),
+ new IdentifierValue(ctx.tableName().name().getText())));
if (null != ctx.tableName().owner()) {
result.setOwner(
new
OwnerSegment(ctx.tableName().owner().start.getStartIndex(),
ctx.tableName().owner().stop.getStopIndex(), (IdentifierValue)
visit(ctx.tableName().owner().identifier())));
@@ -1526,6 +1527,11 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
TableSegment result;
if (null != ctx.queryTableExprSampleClause()) {
result = (SimpleTableSegment)
visit(ctx.queryTableExprSampleClause().queryTableExprTableClause().tableName());
+ if (null !=
ctx.queryTableExprSampleClause().queryTableExprTableClause().AT_()
+ && null !=
ctx.queryTableExprSampleClause().queryTableExprTableClause().dbLink()) {
+ ((SimpleTableSegment) result).setAt(new
IdentifierValue(ctx.queryTableExprSampleClause().queryTableExprTableClause().AT_().getText()));
+ ((SimpleTableSegment) result).setDbLink(new
IdentifierValue(ctx.queryTableExprSampleClause().queryTableExprTableClause().dbLink().getText()));
+ }
} else if (null != ctx.lateralClause()) {
SelectStatement subquery = (SelectStatement)
visit(ctx.lateralClause().selectSubquery());
SubquerySegment subquerySegment = new
SubquerySegment(ctx.lateralClause().selectSubquery().start.getStartIndex(),
ctx.lateralClause().selectSubquery().stop.getStopIndex(), subquery,
diff --git
a/parser/sql/statement/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/statement/oracle/ddl/OraclePLSQLBlockStatement.java
b/parser/sql/statement/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/statement/oracle/ddl/OraclePLSQLBlockStatement.java
index 922a4cfc7b3..12454cb4d21 100644
---
a/parser/sql/statement/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/statement/oracle/ddl/OraclePLSQLBlockStatement.java
+++
b/parser/sql/statement/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/statement/oracle/ddl/OraclePLSQLBlockStatement.java
@@ -19,11 +19,20 @@ package
org.apache.shardingsphere.sql.parser.statement.oracle.ddl;
import lombok.Getter;
import org.apache.shardingsphere.database.connector.core.type.DatabaseType;
+import org.apache.shardingsphere.infra.annotation.HighFrequencyInvocation;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.column.ColumnSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SimpleTableSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.procedure.CursorForLoopStatementSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.procedure.ProcedureCallNameSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.procedure.SQLStatementSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.attribute.SQLStatementAttributes;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.attribute.type.ProcedureCallNamesSQLStatementAttribute;
+import
org.apache.shardingsphere.sql.parser.statement.core.statement.attribute.type.TableSQLStatementAttribute;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.ddl.DDLStatement;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.LinkedList;
import java.util.List;
/**
@@ -34,11 +43,20 @@ public final class OraclePLSQLBlockStatement extends
DDLStatement {
private final List<ProcedureCallNameSegment> procedureCallNames;
+ private final List<SQLStatementSegment> sqlStatements = new ArrayList<>();
+
+ private final List<CursorForLoopStatementSegment> cursorForLoopStatements
= new ArrayList<>();
+
+ private final Collection<SimpleTableSegment> tables = new LinkedList<>();
+
+ private final Collection<ColumnSegment> columns = new LinkedList<>();
+
private final SQLStatementAttributes attributes;
+ @HighFrequencyInvocation
public OraclePLSQLBlockStatement(final DatabaseType databaseType, final
List<ProcedureCallNameSegment> procedureCallNames) {
super(databaseType);
this.procedureCallNames = procedureCallNames;
- attributes = new SQLStatementAttributes(new
ProcedureCallNamesSQLStatementAttribute(procedureCallNames));
+ attributes = new SQLStatementAttributes(new
ProcedureCallNamesSQLStatementAttribute(procedureCallNames), new
TableSQLStatementAttribute(tables));
}
}
diff --git
a/test/it/binder/src/test/java/org/apache/shardingsphere/test/it/sql/binder/dialect/oracle/OracleBinderIT.java
b/test/it/binder/src/test/java/org/apache/shardingsphere/test/it/sql/binder/dialect/oracle/OracleBinderIT.java
index 10c8bc75e03..a5082a0b56e 100644
---
a/test/it/binder/src/test/java/org/apache/shardingsphere/test/it/sql/binder/dialect/oracle/OracleBinderIT.java
+++
b/test/it/binder/src/test/java/org/apache/shardingsphere/test/it/sql/binder/dialect/oracle/OracleBinderIT.java
@@ -17,9 +17,12 @@
package org.apache.shardingsphere.test.it.sql.binder.dialect.oracle;
+import
org.apache.shardingsphere.infra.binder.context.statement.type.CommonSQLStatementContext;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ColumnProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.dml.item.ProjectionSegment;
import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.bound.ColumnSegmentBoundInfo;
+import
org.apache.shardingsphere.sql.parser.statement.core.segment.generic.table.SimpleTableSegment;
+import
org.apache.shardingsphere.sql.parser.statement.core.statement.SQLStatement;
import
org.apache.shardingsphere.sql.parser.statement.core.statement.type.dml.SelectStatement;
import org.apache.shardingsphere.test.it.sql.binder.SQLBinderIT;
import org.apache.shardingsphere.test.it.sql.binder.SQLBinderITSettings;
@@ -30,6 +33,7 @@ import java.util.List;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.is;
import static org.hamcrest.Matchers.isA;
+import static org.junit.jupiter.api.Assertions.assertTrue;
@SQLBinderITSettings("Oracle")
class OracleBinderIT extends SQLBinderIT {
@@ -65,4 +69,79 @@ class OracleBinderIT extends SQLBinderIT {
assertThat(actual.getOriginalTable().getValue(), is(expectedTable));
assertThat(actual.getOriginalColumn().getValue(), is(expectedColumn));
}
+
+ @Test
+ void assertBindDottedDatabaseLink() {
+ SelectStatement actual = (SelectStatement) bindSQLStatement("Oracle",
"SELECT 1 FROM foo_table@foo_link.example.com");
+ SimpleTableSegment actualTable = (SimpleTableSegment)
actual.getFrom().get();
+ assertThat(actualTable.getDbLink().get().getValue(),
is("foo_link.example.com"));
+ assertThat(actualTable.getAt().get().getValue(), is("@"));
+ }
+
+ @Test
+ void assertBindBlockWithLocalRecordFieldTypeAttribute() {
+ String sql = "DECLARE TYPE foo_rec IS RECORD (order_id NUMBER);
t_order foo_rec; foo_copy t_order.order_id%TYPE; BEGIN NULL; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindBlockWithLocalCursorRowtypeAttribute() {
+ String sql = "DECLARE CURSOR foo_cursor IS SELECT 1 foo_id FROM dual;
TYPE foo_ref IS REF CURSOR RETURN foo_cursor%ROWTYPE; BEGIN NULL; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindPackageWithLocalRecordFieldTypeAttribute() {
+ String sql = "CREATE PACKAGE foo_pkg AS TYPE foo_rec IS RECORD (foo_id
NUMBER); foo_record foo_rec; foo_copy foo_pkg.foo_record.foo_id%TYPE; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindPackageWithSchemaQualifiedCursorRowtypeAttribute() {
+ String sql = "CREATE PACKAGE foo_schema.foo_pkg AS TYPE foo_rec IS
RECORD (foo_id NUMBER); CURSOR foo_cursor RETURN foo_rec;"
+ + " TYPE foo_ref IS REF CURSOR RETURN
foo_schema.foo_pkg.foo_cursor%ROWTYPE; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindPackageWithNestedLocalCursorRowtypeAttribute() {
+ String sql = "CREATE PACKAGE BODY foo_pkg AS PROCEDURE foo_proc IS
CURSOR foo_cursor IS SELECT 1 foo_id FROM dual;"
+ + " TYPE foo_ref IS REF CURSOR RETURN
foo_pkg.foo_proc.foo_cursor%ROWTYPE; BEGIN NULL; END; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindBlockWithNestedLocalCursorRowtypeAttribute() {
+ String sql = "<<foo_block>> DECLARE PROCEDURE foo_proc IS CURSOR
foo_cursor IS SELECT 1 foo_id FROM dual;"
+ + " TYPE foo_ref IS REF CURSOR RETURN
foo_block.foo_proc.foo_cursor%ROWTYPE; BEGIN NULL; END; BEGIN NULL; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindPackageWithSchemaQualifiedScalarTypeAttribute() {
+ String sql = "CREATE PACKAGE foo_schema.foo_pkg AS foo_var NUMBER;
foo_copy foo_schema.foo_pkg.foo_var%TYPE; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindPackageWithNestedScalarTypeAttribute() {
+ String sql = "CREATE PACKAGE BODY foo_pkg AS PROCEDURE foo_proc IS
foo_var NUMBER; foo_copy foo_pkg.foo_proc.foo_var%TYPE; BEGIN NULL; END; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
+
+ @Test
+ void assertBindFunctionWithSchemaQualifiedCursorRowtypeAttribute() {
+ String sql = "CREATE FUNCTION foo_schema.foo_func RETURN NUMBER AS
CURSOR foo_cursor IS SELECT 1 foo_id FROM dual;"
+ + " TYPE foo_ref IS REF CURSOR RETURN
foo_schema.foo_func.foo_cursor%ROWTYPE; BEGIN RETURN 1; END;";
+ SQLStatement actual = bindSQLStatement("Oracle", sql);
+ assertTrue(new
CommonSQLStatementContext(actual).getTablesContext().getTableNames().isEmpty());
+ }
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
index 5f432b6959c..8be818cddea 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/table/TableAssert.java
@@ -131,6 +131,18 @@ public final class TableAssert {
public static void assertIs(final SQLCaseAssertContext assertContext,
final SimpleTableSegment actual, final ExpectedSimpleTable expected) {
assertTableNameSegment(assertContext, actual, expected);
assertThat(assertContext.getText("Table alias assertion error: "),
actual.getAliasName().orElse(null), is(expected.getAlias()));
+ if (null != expected.getDbLink()) {
+ assertThat(assertContext.getText("Database link assertion error:
"), actual.getDbLink().map(each -> each.getValue()).orElse(null),
is(expected.getDbLink()));
+ }
+ if (null != expected.getAt()) {
+ assertThat(assertContext.getText("Database link separator
assertion error: "), actual.getAt().map(each -> each.getValue()).orElse(null),
is(expected.getAt()));
+ }
+ if (null != expected.getTableNameStartIndex()) {
+ assertThat(assertContext.getText("Table name start index assertion
error: "), actual.getTableName().getStartIndex(),
is(expected.getTableNameStartIndex()));
+ }
+ if (null != expected.getTableNameStopIndex()) {
+ assertThat(assertContext.getText("Table name stop index assertion
error: "), actual.getTableName().getStopIndex(),
is(expected.getTableNameStopIndex()));
+ }
if (null == expected.getOwner()) {
assertFalse(actual.getOwner().isPresent(),
assertContext.getText("Actual owner should not exist."));
} else {
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/plsql/PLSQLStatementAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/plsql/PLSQLStatementAssert.java
index 3d50019bbca..b4849d036cf 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/plsql/PLSQLStatementAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/plsql/PLSQLStatementAssert.java
@@ -92,6 +92,9 @@ public final class PLSQLStatementAssert {
}
if (actual instanceof OraclePLSQLBlockStatement && expected instanceof
OraclePLSQLBlockStatementTestCase) {
assertProcedureCallNames(assertContext,
((OraclePLSQLBlockStatement) actual).getProcedureCallNames(),
((OraclePLSQLBlockStatementTestCase) expected).getProcedureCalls());
+ assertSQLStatements(assertContext, ((OraclePLSQLBlockStatement)
actual).getSqlStatements(), ((OraclePLSQLBlockStatementTestCase)
expected).getSqlStatements());
+ TableAssert.assertIs(assertContext, ((OraclePLSQLBlockStatement)
actual).getTables(), ((OraclePLSQLBlockStatementTestCase)
expected).getTables());
+ ColumnAssert.assertIs(assertContext, ((OraclePLSQLBlockStatement)
actual).getColumns(), ((OraclePLSQLBlockStatementTestCase)
expected).getColumns());
}
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSimpleTable.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSimpleTable.java
index d707839c7e0..201556ff0c8 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSimpleTable.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/table/ExpectedSimpleTable.java
@@ -37,6 +37,18 @@ public final class ExpectedSimpleTable extends
AbstractExpectedIdentifierSQLSegm
@XmlAttribute
private String alias;
+ @XmlAttribute(name = "db-link")
+ private String dbLink;
+
+ @XmlAttribute
+ private String at;
+
+ @XmlAttribute(name = "table-name-start-index")
+ private Integer tableNameStartIndex;
+
+ @XmlAttribute(name = "table-name-stop-index")
+ private Integer tableNameStopIndex;
+
@XmlElement
private ExpectedOwner owner;
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/plsql/OraclePLSQLBlockStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/plsql/OraclePLSQLBlockStatementTestCase.java
index 76f76ae67b6..db23533adc8 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/plsql/OraclePLSQLBlockStatementTestCase.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/plsql/OraclePLSQLBlockStatementTestCase.java
@@ -19,9 +19,13 @@ package
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.
import lombok.Getter;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.SQLParserTestCase;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.column.ExpectedColumn;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.plsql.ExpectedProcedureCallNameSegment;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.plsql.ExpectedSQLStatementSegment;
+import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.table.ExpectedSimpleTable;
import javax.xml.bind.annotation.XmlElement;
+import javax.xml.bind.annotation.XmlElementWrapper;
import java.util.LinkedList;
import java.util.List;
@@ -33,4 +37,14 @@ public final class OraclePLSQLBlockStatementTestCase extends
SQLParserTestCase {
@XmlElement(name = "procedure-call")
private final List<ExpectedProcedureCallNameSegment> procedureCalls = new
LinkedList<>();
+
+ @XmlElementWrapper(name = "sql-statements")
+ @XmlElement(name = "sql-statement")
+ private final List<ExpectedSQLStatementSegment> sqlStatements = new
LinkedList<>();
+
+ @XmlElement(name = "table")
+ private final List<ExpectedSimpleTable> tables = new LinkedList<>();
+
+ @XmlElement(name = "column")
+ private final List<ExpectedColumn> columns = new LinkedList<>();
}
diff --git a/test/it/parser/src/main/resources/case/ddl/create-package.xml
b/test/it/parser/src/main/resources/case/ddl/create-package.xml
index a01dd2f0d42..a0a43d88576 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-package.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-package.xml
@@ -81,4 +81,96 @@
<package-routine-name name="INSERT_ROW" start-index="64"
stop-index="73" />
<package-routine-name name="READ_PASSWORD" start-index="301"
stop-index="313" />
</create-package>
+ <create-package
sql-case-id="create_package_local_record_field_type_attribute_oracle">
+ <package name="foo_pkg" start-index="15" stop-index="21" />
+ <table name="foo_table" start-index="136" stop-index="155"
table-name-start-index="147" table-name-stop-index="155">
+ <owner name="foo_schema" start-index="136" stop-index="145" />
+ </table>
+ <column name="foo_id" start-index="157" stop-index="162">
+ <owner name="foo_table" start-index="147" stop-index="155" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_body_local_record_field_type_attribute_oracle"
body="true">
+ <package name="foo_pkg" start-index="20" stop-index="26" />
+ <table name="foo_table" start-index="233" stop-index="252"
table-name-start-index="244" table-name-stop-index="252">
+ <owner name="foo_schema" start-index="233" stop-index="242" />
+ </table>
+ <column name="foo_id" start-index="254" stop-index="259">
+ <owner name="foo_table" start-index="244" stop-index="252" />
+ </column>
+ <package-routine-name name="foo_proc" start-index="101"
stop-index="108" />
+ </create-package>
+ <create-package
sql-case-id="create_package_quoted_local_record_field_type_attribute_oracle">
+ <package name="foo_pkg" start-delimiter="""
end-delimiter=""" start-index="15" stop-index="23" />
+ <table name="foo_table" start-index="144" stop-index="163"
table-name-start-index="155" table-name-stop-index="163">
+ <owner name="foo_schema" start-index="144" stop-index="153" />
+ </table>
+ <column name="foo_id" start-index="165" stop-index="170">
+ <owner name="foo_table" start-index="155" stop-index="163" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_schema_local_cursor_rowtype_oracle">
+ <package name="foo_pkg" start-index="26" stop-index="32">
+ <owner name="foo_schema" start-index="15" stop-index="24" />
+ </package>
+ <table name="foo_table" start-index="193" stop-index="212"
table-name-start-index="204" table-name-stop-index="212">
+ <owner name="foo_schema" start-index="193" stop-index="202" />
+ </table>
+ <column name="foo_id" start-index="214" stop-index="219">
+ <owner name="foo_table" start-index="204" stop-index="212" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_body_schema_local_cursor_rowtype_oracle"
body="true">
+ <package name="foo_pkg" start-index="31" stop-index="37">
+ <owner name="foo_schema" start-index="20" stop-index="29" />
+ </package>
+ <table name="foo_table" start-index="172" stop-index="191"
table-name-start-index="183" table-name-stop-index="191">
+ <owner name="foo_schema" start-index="172" stop-index="181" />
+ </table>
+ <column name="foo_id" start-index="193" stop-index="198">
+ <owner name="foo_table" start-index="183" stop-index="191" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_quoted_schema_local_cursor_rowtype_oracle">
+ <package name="foo.pkg" start-delimiter="""
end-delimiter=""" start-index="28" stop-index="36">
+ <owner name="foo.schema" start-delimiter="""
end-delimiter=""" start-index="15" stop-index="26" />
+ </package>
+ <table name="foo_table" start-index="205" stop-index="224"
table-name-start-index="216" table-name-stop-index="224">
+ <owner name="foo_schema" start-index="205" stop-index="214" />
+ </table>
+ <column name="foo_id" start-index="226" stop-index="231">
+ <owner name="foo_table" start-index="216" stop-index="224" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_body_nested_local_cursor_rowtype_oracle"
body="true">
+ <package name="foo_pkg" start-index="20" stop-index="26" />
+ <table name="foo_table" start-index="198" stop-index="217"
table-name-start-index="209" table-name-stop-index="217">
+ <owner name="foo_schema" start-index="198" stop-index="207" />
+ </table>
+ <column name="foo_id" start-index="219" stop-index="224">
+ <owner name="foo_table" start-index="209" stop-index="217" />
+ </column>
+ <package-routine-name name="foo_proc" start-index="41" stop-index="48"
/>
+ </create-package>
+ <create-package
sql-case-id="create_package_schema_local_scalar_type_oracle">
+ <package name="foo_pkg" start-index="26" stop-index="32">
+ <owner name="foo_schema" start-index="15" stop-index="24" />
+ </package>
+ <table name="foo_table" start-index="104" stop-index="123"
table-name-start-index="115" table-name-stop-index="123">
+ <owner name="foo_schema" start-index="104" stop-index="113" />
+ </table>
+ <column name="foo_id" start-index="125" stop-index="130">
+ <owner name="foo_table" start-index="115" stop-index="123" />
+ </column>
+ </create-package>
+ <create-package
sql-case-id="create_package_body_nested_scalar_type_oracle" body="true">
+ <package name="foo_pkg" start-index="20" stop-index="26" />
+ <table name="foo_table" start-index="135" stop-index="154"
table-name-start-index="146" table-name-stop-index="154">
+ <owner name="foo_schema" start-index="135" stop-index="144" />
+ </table>
+ <column name="foo_id" start-index="156" stop-index="161">
+ <owner name="foo_table" start-index="146" stop-index="154" />
+ </column>
+ <package-routine-name name="foo_proc" start-index="41" stop-index="48"
/>
+ </create-package>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
index 301f6ead50b..038dcfee8c0 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-procedure.xml
@@ -368,4 +368,12 @@
<valid-statement type="RollbackStatement" />
</create-procedure>
<create-procedure sql-case-id="create_procedure_flush_kill" />
+ <plsql-block
sql-case-id="create_plsql_block_with_schema_qualified_type_attribute">
+ <table name="foo_table" start-index="18" stop-index="37"
table-name-start-index="29" table-name-stop-index="37">
+ <owner name="foo_schema" start-index="18" stop-index="27" />
+ </table>
+ <column name="foo_id" start-index="39" stop-index="44">
+ <owner name="foo_table" start-index="29" stop-index="37" />
+ </column>
+ </plsql-block>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/plsql-block.xml
b/test/it/parser/src/main/resources/case/ddl/plsql-block.xml
index 963e92011cb..88105e43dcf 100644
--- a/test/it/parser/src/main/resources/case/ddl/plsql-block.xml
+++ b/test/it/parser/src/main/resources/case/ddl/plsql-block.xml
@@ -34,13 +34,120 @@
<plsql-block sql-case-id="plsql_block_record_constructor_empty_oracle" />
- <plsql-block sql-case-id="plsql_block_forall_insert_oracle" />
+ <plsql-block sql-case-id="plsql_block_forall_insert_oracle">
+ <sql-statements>
+ <sql-statement start-index="23" stop-index="57"
statement-class-simple-name="InsertStatement" />
+ </sql-statements>
+ </plsql-block>
- <plsql-block sql-case-id="plsql_block_forall_update_oracle" />
+ <plsql-block sql-case-id="plsql_block_forall_update_oracle">
+ <sql-statements>
+ <sql-statement start-index="23" stop-index="52"
statement-class-simple-name="UpdateStatement" />
+ </sql-statements>
+ </plsql-block>
- <plsql-block sql-case-id="plsql_block_forall_delete_oracle" />
+ <plsql-block sql-case-id="plsql_block_forall_delete_oracle">
+ <sql-statements>
+ <sql-statement start-index="23" stop-index="59"
statement-class-simple-name="DeleteStatement" />
+ </sql-statements>
+ </plsql-block>
- <plsql-block sql-case-id="plsql_block_forall_merge_oracle" />
+ <plsql-block sql-case-id="plsql_block_forall_merge_oracle">
+ <sql-statements>
+ <sql-statement start-index="23" stop-index="117"
statement-class-simple-name="MergeStatement" />
+ </sql-statements>
+ </plsql-block>
<plsql-block
sql-case-id="plsql_block_forall_execute_immediate_save_exceptions_oracle" />
+ <plsql-block
sql-case-id="plsql_block_with_insert_select_in_while_if_oracle">
+ <sql-statements>
+ <sql-statement start-index="183" stop-index="325"
statement-class-simple-name="InsertStatement" />
+ <sql-statement start-index="333" stop-index="458"
statement-class-simple-name="InsertStatement" />
+ <sql-statement start-index="499" stop-index="504"
statement-class-simple-name="CommitStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block sql-case-id="plsql_block_record_field_defaults_oracle">
+ <procedure-call name="RAISE_APPLICATION_ERROR" />
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_local_record_field_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_quoted_local_record_field_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_schema_table_type_attribute_with_local_record_oracle">
+ <table name="foo_table" start-index="76" stop-index="95"
table-name-start-index="87" table-name-stop-index="95">
+ <owner name="foo_schema" start-index="76" stop-index="85" />
+ </table>
+ <column name="foo_id" start-index="97" stop-index="102">
+ <owner name="foo_table" start-index="87" stop-index="95" />
+ </column>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_table_type_attribute_before_record_declaration_oracle">
+ <table name="foo_table" start-index="17" stop-index="25"
table-name-start-index="17" table-name-stop-index="25" />
+ <column name="foo_id" start-index="27" stop-index="32">
+ <owner name="foo_table" start-index="17" stop-index="25" />
+ </column>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_table_type_attribute_after_local_procedure_oracle">
+ <table name="foo_table" start-index="147" stop-index="155"
table-name-start-index="147" table-name-stop-index="155" />
+ <column name="foo_id" start-index="157" stop-index="162">
+ <owner name="foo_table" start-index="147" stop-index="155" />
+ </column>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_table_type_attribute_with_quoted_record_oracle">
+ <table name="foo_table" start-index="78" stop-index="86"
table-name-start-index="78" table-name-stop-index="86" />
+ <column name="foo_id" start-index="88" stop-index="93">
+ <owner name="foo_table" start-index="78" stop-index="86" />
+ </column>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_shadowed_record_field_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_cursor_record_field_type_attribute_oracle">
+ <sql-statements>
+ <sql-statement start-index="22" stop-index="49"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_labeled_record_field_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_nested_labeled_record_field_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_procedure_qualified_record_field_type_attribute_oracle"
/>
+ <plsql-block
sql-case-id="plsql_block_labeled_scalar_type_attribute_oracle" />
+ <plsql-block
sql-case-id="plsql_block_quoted_dotted_table_type_attribute_oracle">
+ <table name="foo_block.foo_table" start-delimiter="""
end-delimiter=""" start-index="90" stop-index="110"
table-name-start-index="90" table-name-stop-index="110" />
+ <column name="foo_id" start-index="112" stop-index="117">
+ <owner name="foo_block.foo_table" start-delimiter="""
end-delimiter=""" start-index="90" stop-index="110" />
+ </column>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_ref_cursor_return_local_cursor_rowtype_oracle">
+ <sql-statements>
+ <sql-statement start-index="29" stop-index="53"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block sql-case-id="plsql_block_labeled_local_cursor_rowtype_oracle">
+ <sql-statements>
+ <sql-statement start-index="43" stop-index="67"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_ref_cursor_return_table_rowtype_oracle">
+ <table name="foo_table" start-index="42" stop-index="50"
table-name-start-index="42" table-name-stop-index="50" />
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_ref_cursor_return_schema_table_with_local_cursor_oracle">
+ <table name="foo_cursor" start-index="90" stop-index="110"
table-name-start-index="101" table-name-stop-index="110">
+ <owner name="foo_schema" start-index="90" stop-index="99" />
+ </table>
+ <sql-statements>
+ <sql-statement start-index="29" stop-index="53"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block
sql-case-id="plsql_block_cursor_return_local_cursor_rowtype_oracle">
+ <sql-statements>
+ <sql-statement start-index="29" stop-index="53"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
+ <plsql-block sql-case-id="plsql_block_nested_local_cursor_rowtype_oracle">
+ <table name="foo_table" start-index="191" stop-index="210"
table-name-start-index="202" table-name-stop-index="210">
+ <owner name="foo_schema" start-index="191" stop-index="200" />
+ </table>
+ <column name="foo_id" start-index="212" stop-index="217">
+ <owner name="foo_table" start-index="202" stop-index="210" />
+ </column>
+ <sql-statements>
+ <sql-statement start-index="65" stop-index="89"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </plsql-block>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/delete.xml
b/test/it/parser/src/main/resources/case/dml/delete.xml
index 823872eb65a..463f81efbf7 100644
--- a/test/it/parser/src/main/resources/case/dml/delete.xml
+++ b/test/it/parser/src/main/resources/case/dml/delete.xml
@@ -733,7 +733,7 @@
</delete>
<delete sql-case-id="delete_with_remote_database_table">
- <table name="locations" start-index="12" stop-index="30">
+ <table name="locations" start-index="12" stop-index="23"
db-link="remote" at="@" table-name-start-index="15" table-name-stop-index="23">
<owner name="hr" start-index="12" stop-index="13" />
</table>
<where start-index="32" stop-index="55">
diff --git a/test/it/parser/src/main/resources/case/dml/insert.xml
b/test/it/parser/src/main/resources/case/dml/insert.xml
index 45a56d9ef72..a6d385d6839 100644
--- a/test/it/parser/src/main/resources/case/dml/insert.xml
+++ b/test/it/parser/src/main/resources/case/dml/insert.xml
@@ -2344,7 +2344,7 @@
</insert>
<insert sql-case-id="insert_into_table_with_dblink">
- <table name="remote_table" start-index="12" stop-index="28" />
+ <table name="remote_table" start-index="12" stop-index="23"
db-link="link" at="@" table-name-start-index="12" table-name-stop-index="23" />
<columns start-index="29" stop-index="29" />
<select>
<from>
@@ -5925,4 +5925,20 @@
</assignment>
</on-duplicate-key-columns>
</insert>
+ <insert sql-case-id="insert_with_schema_database_link_oracle"
parameters="1">
+ <table name="USER_CONSIS_LIST" db-link="pjzw" at="@" start-index="12"
stop-index="36" table-name-start-index="21" table-name-stop-index="36">
+ <owner name="userinfo" start-index="12" stop-index="19" />
+ </table>
+ <columns start-index="43" stop-index="50">
+ <column name="telnum" start-index="44" stop-index="49" />
+ </columns>
+ <values>
+ <value>
+ <assignment-value>
+ <parameter-marker-expression parameter-index="0"
start-index="60" stop-index="60" />
+ <literal-expression value="1" start-index="60"
stop-index="60" />
+ </assignment-value>
+ </value>
+ </values>
+ </insert>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/select.xml
b/test/it/parser/src/main/resources/case/dml/select.xml
index 6ad1d998049..d949c3c4ff1 100644
--- a/test/it/parser/src/main/resources/case/dml/select.xml
+++ b/test/it/parser/src/main/resources/case/dml/select.xml
@@ -15625,4 +15625,24 @@
</expr>
</where>
</select>
+ <select sql-case-id="select_with_schema_database_link_and_alias_oracle">
+ <from>
+ <simple-table name="subs_rateplan" alias="x" db-link="PJZW" at="@"
start-index="14" stop-index="42" table-name-start-index="23"
table-name-stop-index="35">
+ <owner name="userinfo" start-index="14" stop-index="21" />
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ </select>
+ <select sql-case-id="select_with_dotted_database_link_oracle">
+ <from>
+ <simple-table name="subs_rateplan" alias="x"
db-link="PJZW.EXAMPLE.COM" at="@" start-index="14" stop-index="54"
table-name-start-index="23" table-name-stop-index="35">
+ <owner name="userinfo" start-index="14" stop-index="21" />
+ </simple-table>
+ </from>
+ <projections start-index="7" stop-index="7">
+ <shorthand-projection start-index="7" stop-index="7" />
+ </projections>
+ </select>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/dml/update.xml
b/test/it/parser/src/main/resources/case/dml/update.xml
index 5fbb8e0ed26..93898159bb0 100644
--- a/test/it/parser/src/main/resources/case/dml/update.xml
+++ b/test/it/parser/src/main/resources/case/dml/update.xml
@@ -323,8 +323,8 @@
</update>
<update sql-case-id="update_with_dblink_oracle">
- <table start-index="7" stop-index="22">
- <simple-table name="employees" start-index="7" stop-index="22" />
+ <table start-index="7" stop-index="15">
+ <simple-table name="employees" start-index="7" stop-index="15"
db-link="remote" at="@" table-name-start-index="7" table-name-stop-index="15" />
</table>
<set start-index="24" stop-index="42">
<assignment start-index="24" stop-index="42">
diff --git a/test/it/parser/src/main/resources/case/plsql/create-function.xml
b/test/it/parser/src/main/resources/case/plsql/create-function.xml
index 25c85ada135..e65cf6316ca 100644
--- a/test/it/parser/src/main/resources/case/plsql/create-function.xml
+++ b/test/it/parser/src/main/resources/case/plsql/create-function.xml
@@ -18,4 +18,5 @@
<sql-parser-test-cases>
<create-function sql-case-id="pl_sql_create_function" />
+ <create-function sql-case-id="pl_sql_create_function_with_in_variable" />
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
b/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
index 841ddc38f9c..fc6106c294e 100644
--- a/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
+++ b/test/it/parser/src/main/resources/case/plsql/create-procedure.xml
@@ -91,4 +91,22 @@
</sql-statements>
</create-procedure>
+ <create-procedure
sql-case-id="pl_sql_create_procedure_with_remote_procedure_call">
+ <procedure-name name="foo_proc" />
+ <procedure-calls>
+ <procedure-call name="foo_remote_proc" />
+ </procedure-calls>
+ </create-procedure>
+ <create-procedure
sql-case-id="pl_sql_create_procedure_with_remote_function_call">
+ <procedure-name name="foo_proc" />
+ <sql-statements>
+ <sql-statement start-index="64" stop-index="131"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-procedure>
+ <create-procedure
sql-case-id="pl_sql_create_procedure_with_remote_sequence_nextval">
+ <procedure-name name="foo_proc" />
+ <sql-statements>
+ <sql-statement start-index="64" stop-index="120"
statement-class-simple-name="SelectStatement" />
+ </sql-statements>
+ </create-procedure>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-package.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-package.xml
index 7b9f34d8694..13acd5e8720 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-package.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-package.xml
@@ -23,4 +23,13 @@
<sql-case id="create_package_visitor_oracle" value="CREATE OR REPLACE
EDITIONABLE PACKAGE IF NOT EXISTS foo_schema.foo_pkg AUTHID CURRENT_USER AS
g_public NUMBER := 1; PROCEDURE set_value(p_value IN T_USER.USER_ID%TYPE);
FUNCTION get_value RETURN T_USER.PASSWORD%TYPE; END foo_pkg;" db-types="Oracle"
/>
<sql-case id="create_package_body_visitor_oracle" value="CREATE OR REPLACE
NONEDITIONABLE PACKAGE BODY foo_schema.foo_pkg AS PROCEDURE set_value(p_value
IN T_USER.USER_ID%TYPE) AS BEGIN INSERT INTO t_order VALUES (p_value); END;
FUNCTION read_value RETURN T_USER.PASSWORD%TYPE AS BEGIN RETURN NULL; END;
BEGIN UPDATE t_order SET order_id = 1; END foo_pkg;" db-types="Oracle" />
<sql-case id="create_package_body_with_type_attributes_visitor_oracle"
value="CREATE OR REPLACE PACKAGE BODY PKG_E2E_RDL_CONVERT AS PROCEDURE
INSERT_ROW(P_ID IN T_E2E_RDL_CONVERT.ID%TYPE, P_PASSWORD IN VARCHAR2, P_STATUS
IN T_E2E_RDL_CONVERT.STATUS%TYPE) IS BEGIN INSERT INTO T_E2E_RDL_CONVERT (ID,
PASSWORD, STATUS) VALUES (P_ID, P_PASSWORD, P_STATUS); END INSERT_ROW; FUNCTION
READ_PASSWORD(P_ID IN T_E2E_RDL_CONVERT.ID%TYPE) RETURN VARCHAR2 IS V_PASSWORD
VARCHAR2(50); BEGIN SELECT PAS [...]
+ <sql-case id="create_package_local_record_field_type_attribute_oracle"
value="CREATE PACKAGE foo_pkg AS TYPE foo_rec IS RECORD (foo_id NUMBER);
foo_record foo_rec; foo_copy foo_pkg.foo_record.foo_id%TYPE; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case
id="create_package_body_local_record_field_type_attribute_oracle" value="CREATE
PACKAGE BODY foo_pkg AS TYPE foo_rec IS RECORD (foo_id NUMBER); foo_record
foo_rec; PROCEDURE foo_proc IS foo_record foo_rec; foo_copy
foo_record.foo_id%TYPE; BEGIN NULL; END; foo_copy
foo_pkg.foo_record.foo_id%TYPE; foo_real foo_schema.foo_table.foo_id%TYPE;
END;" db-types="Oracle" />
+ <sql-case
id="create_package_quoted_local_record_field_type_attribute_oracle"
value="CREATE PACKAGE "foo_pkg" AS TYPE foo_rec IS RECORD (foo_id
NUMBER); "foo_record" foo_rec; foo_copy
"foo_pkg"."foo_record".foo_id%TYPE; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case id="create_package_schema_local_cursor_rowtype_oracle"
value="CREATE PACKAGE foo_schema.foo_pkg AS TYPE foo_rec IS RECORD (foo_id
NUMBER); CURSOR foo_cursor RETURN foo_rec; TYPE foo_ref IS REF CURSOR RETURN
foo_schema.foo_pkg.foo_cursor%ROWTYPE; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case id="create_package_body_schema_local_cursor_rowtype_oracle"
value="CREATE PACKAGE BODY foo_schema.foo_pkg AS CURSOR foo_cursor IS SELECT 1
foo_id FROM dual; TYPE foo_ref IS REF CURSOR RETURN
foo_schema.foo_pkg.foo_cursor%ROWTYPE; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case id="create_package_quoted_schema_local_cursor_rowtype_oracle"
value="CREATE PACKAGE "foo.schema"."foo.pkg" AS TYPE
foo_rec IS RECORD (foo_id NUMBER); CURSOR "foo.cursor" RETURN
foo_rec; TYPE foo_ref IS REF CURSOR RETURN
"foo.schema"."foo.pkg"."foo.cursor"%ROWTYPE;
foo_real foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case id="create_package_body_nested_local_cursor_rowtype_oracle"
value="CREATE PACKAGE BODY foo_pkg AS PROCEDURE foo_proc IS CURSOR foo_cursor
IS SELECT 1 foo_id FROM dual; TYPE foo_ref IS REF CURSOR RETURN
foo_pkg.foo_proc.foo_cursor%ROWTYPE; BEGIN NULL; END; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
+ <sql-case id="create_package_schema_local_scalar_type_oracle"
value="CREATE PACKAGE foo_schema.foo_pkg AS foo_var NUMBER; foo_copy
foo_schema.foo_pkg.foo_var%TYPE; foo_real foo_schema.foo_table.foo_id%TYPE;
END;" db-types="Oracle" />
+ <sql-case id="create_package_body_nested_scalar_type_oracle" value="CREATE
PACKAGE BODY foo_pkg AS PROCEDURE foo_proc IS foo_var NUMBER; foo_copy
foo_pkg.foo_proc.foo_var%TYPE; BEGIN NULL; END; foo_real
foo_schema.foo_table.foo_id%TYPE; END;" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
index be68e0e9de1..60524eb9897 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-procedure.xml
@@ -129,4 +129,5 @@
<sql-case id="create_procedure_with_cursor_fetch_and_package_call"
value="CREATE OR REPLACE PROCEDURE CRM_CONTACTINFOR is v_count number; v_SEQ
VARCHAR2(200); v_mobileno VARCHAR2(20); v_CUSTID VARCHAR2(200); cursor
c_contactinfor is select con.SEQ_CONTACT_INFO, con.mobileno, sub.userid from
subscriber sub, t_cm_contact_info con, customer cus where sub.servnumber =
con.mobileno and cus.custid = con.custid; BEGIN OPEN c_contactinfor; LOOP FETCH
c_contactinfor INTO v_SEQ, v_mobileno, v_ [...]
<sql-case id="create_procedure_flush_kill" value="CREATE PROCEDURE p()
BEGIN FLUSH STATUS; KILL @connection_id; END" db-types="MySQL,Doris" />
+ <sql-case id="create_plsql_block_with_schema_qualified_type_attribute"
value="DECLARE foo_value foo_schema.foo_table.foo_id%TYPE; BEGIN NULL; END;"
db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/plsql-block.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/plsql-block.xml
index dc582be3007..cf566e1447b 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/plsql-block.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/plsql-block.xml
@@ -29,4 +29,25 @@
<sql-case id="plsql_block_forall_delete_oracle" value="BEGIN FORALL i IN
1..2 DELETE FROM foo_table WHERE id = v(i); END;" db-types="Oracle" />
<sql-case id="plsql_block_forall_merge_oracle" value="BEGIN FORALL i IN
1..2 MERGE INTO foo_table t USING bar_table s ON (t.id=s.id) WHEN MATCHED THEN
UPDATE SET t.val=v(i); END;" db-types="Oracle" />
<sql-case id="plsql_block_forall_execute_immediate_save_exceptions_oracle"
value="BEGIN FORALL i IN 1..v.COUNT SAVE EXCEPTIONS EXECUTE IMMEDIATE 'DELETE
FROM foo_table WHERE id=:1' USING v(i); END;" db-types="Oracle" />
+ <sql-case id="plsql_block_with_insert_select_in_while_if_oracle"
value="DECLARE foo_begin NUMBER; foo_end NUMBER; foo_num NUMBER; BEGIN
foo_begin := 0; foo_end := 1; foo_num := foo_begin; WHILE 1=1 LOOP foo_num :=
foo_num + 500; IF foo_num < foo_end THEN INSERT INTO foo_target (id, phone,
name) SELECT id, phone, CONCAT(SUBSTR(name,1,1),'**') FROM foo_source WHERE id
>= foo_begin AND id < foo_num; ELSE INSERT INTO foo_target (id, phone,
name) SELECT id, phone, CONCAT(SUBSTR(n [...]
+ <sql-case id="plsql_block_record_field_defaults_oracle" value="DECLARE
TYPE foo_record IS RECORD(foo_id NUMBER DEFAULT 0, foo_name VARCHAR2(20)
DEFAULT NULL); foo_value foo_record; BEGIN IF foo_value.foo_id <> 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'record default mismatch'); END IF; END;"
db-types="Oracle" />
+ <sql-case id="plsql_block_local_record_field_type_attribute_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); t_order foo_rec;
foo_copy T_ORDER.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_quoted_local_record_field_type_attribute_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); "foo_record"
foo_rec; foo_copy "foo_record".foo_id%TYPE; BEGIN NULL; END;"
db-types="Oracle" />
+ <sql-case
id="plsql_block_schema_table_type_attribute_with_local_record_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); foo_table foo_rec;
foo_copy foo_schema.foo_table.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle"
/>
+ <sql-case
id="plsql_block_table_type_attribute_before_record_declaration_oracle"
value="DECLARE foo_copy foo_table.foo_id%TYPE; TYPE foo_rec IS RECORD (foo_id
NUMBER); foo_table foo_rec; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case
id="plsql_block_table_type_attribute_after_local_procedure_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); PROCEDURE foo_proc IS
foo_table foo_rec; foo_copy foo_table.foo_id%TYPE; BEGIN NULL; END; foo_copy
foo_table.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_table_type_attribute_with_quoted_record_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); "foo_table"
foo_rec; foo_copy foo_table.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_shadowed_record_field_type_attribute_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); foo_table foo_rec;
PROCEDURE foo_proc IS foo_table foo_rec; foo_copy foo_table.foo_id%TYPE; BEGIN
NULL; END; foo_copy foo_table.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle"
/>
+ <sql-case id="plsql_block_cursor_record_field_type_attribute_oracle"
value="BEGIN FOR foo_row IN (SELECT foo_id FROM foo_table) LOOP DECLARE
foo_copy foo_row.foo_id%TYPE; BEGIN NULL; END; END LOOP; END;"
db-types="Oracle" />
+ <sql-case id="plsql_block_labeled_record_field_type_attribute_oracle"
value="<<foo_block>> DECLARE TYPE foo_rec IS RECORD (foo_id
NUMBER); foo_record foo_rec; foo_copy foo_block.foo_record.foo_id%TYPE; BEGIN
NULL; END;" db-types="Oracle" />
+ <sql-case
id="plsql_block_nested_labeled_record_field_type_attribute_oracle" value="BEGIN
<<foo_block>> DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER);
foo_record foo_rec; foo_copy foo_block.foo_record.foo_id%TYPE; BEGIN NULL; END;
END;" db-types="Oracle" />
+ <sql-case
id="plsql_block_procedure_qualified_record_field_type_attribute_oracle"
value="DECLARE TYPE foo_rec IS RECORD (foo_id NUMBER); PROCEDURE foo_proc IS
foo_record foo_rec; foo_copy foo_proc.foo_record.foo_id%TYPE; BEGIN NULL; END;
BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_labeled_scalar_type_attribute_oracle"
value="<<foo_block>> DECLARE foo_id NUMBER; foo_copy
foo_block.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_quoted_dotted_table_type_attribute_oracle"
value="<<foo_block>> DECLARE TYPE foo_rec IS RECORD (foo_id
NUMBER); foo_table foo_rec; foo_copy
"foo_block.foo_table".foo_id%TYPE; BEGIN NULL; END;"
db-types="Oracle" />
+ <sql-case id="plsql_block_ref_cursor_return_local_cursor_rowtype_oracle"
value="DECLARE CURSOR foo_cursor IS SELECT 1 foo_id FROM dual; TYPE foo_ref IS
REF CURSOR RETURN foo_cursor%ROWTYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_labeled_local_cursor_rowtype_oracle"
value="<<foo_block>> DECLARE CURSOR foo_cursor IS SELECT 1 foo_id
FROM dual; foo_record foo_block.foo_cursor%ROWTYPE; BEGIN NULL; END;"
db-types="Oracle" />
+ <sql-case id="plsql_block_ref_cursor_return_table_rowtype_oracle"
value="DECLARE TYPE foo_ref IS REF CURSOR RETURN foo_table%ROWTYPE; BEGIN NULL;
END;" db-types="Oracle" />
+ <sql-case
id="plsql_block_ref_cursor_return_schema_table_with_local_cursor_oracle"
value="DECLARE CURSOR foo_cursor IS SELECT 1 foo_id FROM dual; TYPE foo_ref IS
REF CURSOR RETURN foo_schema.foo_cursor%ROWTYPE; BEGIN NULL; END;"
db-types="Oracle" />
+ <sql-case id="plsql_block_cursor_return_local_cursor_rowtype_oracle"
value="DECLARE CURSOR foo_cursor IS SELECT 1 foo_id FROM dual; CURSOR foo_other
RETURN foo_cursor%ROWTYPE; BEGIN NULL; END;" db-types="Oracle" />
+ <sql-case id="plsql_block_nested_local_cursor_rowtype_oracle"
value="<<foo_block>> DECLARE PROCEDURE foo_proc IS CURSOR
foo_cursor IS SELECT 1 foo_id FROM dual; TYPE foo_ref IS REF CURSOR RETURN
foo_block.foo_proc.foo_cursor%ROWTYPE; BEGIN NULL; END; foo_real
foo_schema.foo_table.foo_id%TYPE; BEGIN NULL; END;" db-types="Oracle" />
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
index 9eb21b62b64..fbffa3cde33 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/insert.xml
@@ -212,4 +212,5 @@
<sql-case id="insert_table_collection_oracle" value="INSERT INTO
TABLE(SELECT t.nested_col FROM t_nested t) VALUES (1)" db-types="Oracle" />
<sql-case id="insert_returning_into_parameter_marker_oracle" value="INSERT
INTO t_user (user_id, user_name) VALUES (?, ?) RETURNING user_id INTO ?"
db-types="Oracle" />
<sql-case id="insert_on_conflit_do_update" value="INSERT INTO t_order (
c1, c2, c3 ) VALUES ( ?, now(), ? ) ON CONFLICT ( c1, c3 ) DO UPDATE set c2 =
now() WHERE user_id= ?" db-types="PostgreSQL" />
+ <sql-case id="insert_with_schema_database_link_oracle" value="INSERT INTO
userinfo.USER_CONSIS_LIST@pjzw (telnum) VALUES (?)" db-types="Oracle" />
</sql-cases>
diff --git a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
index 7b3545c8011..3ecfcd5360c 100644
--- a/test/it/parser/src/main/resources/sql/supported/dml/select.xml
+++ b/test/it/parser/src/main/resources/sql/supported/dml/select.xml
@@ -591,4 +591,6 @@
<sql-case id="select_where_starting_with_firebird" value="SELECT order_id
FROM t_order WHERE status STARTING WITH ?" db-types="Firebird" />
<sql-case id="select_where_similar_to_escape_firebird" value="SELECT
order_id FROM t_order WHERE status SIMILAR TO ? ESCAPE ?" db-types="Firebird" />
<sql-case id="select_where_is_distinct_from_and_equal_firebird"
value="SELECT order_id FROM t_order WHERE status IS DISTINCT FROM ? AND
order_id = ?" db-types="Firebird" />
+ <sql-case id="select_with_schema_database_link_and_alias_oracle"
value="SELECT * FROM userinfo.subs_rateplan@PJZW x" db-types="Oracle" />
+ <sql-case id="select_with_dotted_database_link_oracle" value="SELECT *
FROM [email protected] x" db-types="Oracle" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/plsql/create-function.xml
b/test/it/parser/src/main/resources/sql/supported/plsql/create-function.xml
index 6346f011538..4b6232a1009 100644
--- a/test/it/parser/src/main/resources/sql/supported/plsql/create-function.xml
+++ b/test/it/parser/src/main/resources/sql/supported/plsql/create-function.xml
@@ -18,4 +18,5 @@
<sql-cases>
<sql-case id="pl_sql_create_function" db-types="Oracle"
case-types="LITERAL" value="CREATE OR REPLACE EDITIONABLE FUNCTION
"TEST"."test_function" ( v_orgid IN NUMBER)
RETURN NUMBER AS v_partAmount NUMBER(9,2);--兼岗人数
v_factnums NUMBER(9,2);--实际人数 v_cnt NUMBER;
BEGIN v_factnums:=0;
v_partAmount:=0; BEGIN--1 --- 部门实际的兼岗人数
[...]
+ <sql-case id="pl_sql_create_function_with_in_variable" db-types="Oracle"
value="CREATE OR REPLACE FUNCTION check_privilege(v_privid VARCHAR2) RETURN
NUMBER AS v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM
subs_privilege WHERE privid IN v_privid; RETURN v_count; END;" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
b/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
index 9068b96f327..c3b7de552e3 100644
--- a/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
+++ b/test/it/parser/src/main/resources/sql/supported/plsql/create-procedure.xml
@@ -20,4 +20,7 @@
<sql-case id="pl_sql_create_procedure" db-types="Oracle" value="create or
replace procedure test_procedure(P_RETVAL out varchar2) is E_EXCEPTION
EXCEPTION; v_bendate date; v_enddate date; v_day varchar2(30); begin
SAVEPOINT cp_SHOPBudget_Gy; P_RETVAL := 0; select upper(trim(to_char(sysdate,
'day'))) into v_day from dual; if v_day = 'MONDAY' then return; else
case when v_day = 'MONDAY' then v_bendate := TRUNC(SYSDATE) - 7;
v_enddate := TRUNC( [...]
<sql-case id="pl_sql_create_procedure_not_end_with_semi" db-types="Oracle"
value="create or replace procedure test_procedure(P_RETVAL out varchar2) is
E_EXCEPTION EXCEPTION; v_bendate date; v_enddate date; v_day
varchar2(30); begin SAVEPOINT cp_SHOPBudget_Gy; P_RETVAL := 0; select
upper(trim(to_char(sysdate, 'day'))) into v_day from dual; if v_day = 'MONDAY'
then return; else case when v_day = 'MONDAY' then
v_bendate := TRUNC(SYSDATE) - 7; v [...]
<sql-case id="pl_sql_create_procedure_with_loop_dml" db-types="Oracle"
value="create or replace procedure proc_multi as begin update t set a = 1;
end;"/>
+ <sql-case id="pl_sql_create_procedure_with_remote_procedure_call"
db-types="Oracle" value="CREATE OR REPLACE PROCEDURE foo_proc IS BEGIN
foo_remote_proc@foo_link(1); END;"/>
+ <sql-case id="pl_sql_create_procedure_with_remote_function_call"
db-types="Oracle" value="CREATE OR REPLACE PROCEDURE foo_proc IS foo_value
NUMBER; BEGIN SELECT foo_pkg.foo_remote_func@foo_link(1) INTO foo_value FROM
dual; END;"/>
+ <sql-case id="pl_sql_create_procedure_with_remote_sequence_nextval"
db-types="Oracle" value="CREATE OR REPLACE PROCEDURE foo_proc IS foo_value
NUMBER; BEGIN SELECT foo_seq.NEXTVAL@foo_link INTO foo_value FROM dual; END;"/>
</sql-cases>
