Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package plsql
import (
"fmt"
"log/slog"
"strconv"
"strings"
"unicode"
oracleast "github.com/bytebase/omni/oracle/ast"
oracleparser "github.com/bytebase/omni/oracle/parser"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/common/log"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/utils"
)
const dbVersion12 = 12
func init() {
base.RegisterResultLimitFunc(storepb.Engine_ORACLE, statementWithResultLimit)
}
// statementWithResultLimit implements base.ResultLimitFunc for Oracle.
// engineVersion picks the clause: ROWNUM before version 12, FETCH NEXT from
// 12c on — see isOracle11gOrEarlier.
func statementWithResultLimit(statement string, limit int, engineVersion string) string {
return addResultLimit(statement, limit, engineVersion)
}
// addResultLimit adds a limit clause to the statement based on Oracle version
func addResultLimit(stmt string, limit int, engineVersion string) string {
// Check if we should skip adding limit (e.g., for simple DUAL queries)
if shouldSkipLimit(stmt) {
return stmt
}
// Determine Oracle version
if isOracle11gOrEarlier(engineVersion) {
return addLimitFor11g(stmt, limit)
}
return addLimitFor12cAndLater(stmt, limit)
}
// shouldSkipLimit checks if the statement needs a limit clause
func shouldSkipLimit(stmt string) bool {
ok, err := skipAddLimit(stmt)
return err == nil && ok
}
// isOracle11gOrEarlier checks if the Oracle version is 11g or earlier
func isOracle11gOrEarlier(engineVersion string) bool {
versionIdx := strings.Index(engineVersion, ".")
if versionIdx < 0 {
return true // Default to 11g behavior for invalid version
}
versionNumber, err := strconv.Atoi(engineVersion[:versionIdx])
if err != nil {
return true // Default to 11g behavior for parsing errors
}
return versionNumber < dbVersion12
}
// addLimitFor11g adds a ROWNUM-based limit for Oracle 11g and earlier versions.
// Uses the legacy approach with subquery and ROWNUM.
func addLimitFor11g(statement string, limitCount int) string {
if !isSelectOrWithStatement(statement) {
return statement
}
return fmt.Sprintf("SELECT * FROM (%s) WHERE ROWNUM <= %d", base.TrimStatement(statement), limitCount)
}
// isSelectOrWithStatement checks if the statement is a query: SELECT, WITH, or
// a parenthesized query.
func isSelectOrWithStatement(statement string) bool {
trimmedStatement := strings.ToLower(strings.TrimLeftFunc(statement, unicode.IsSpace))
return strings.HasPrefix(trimmedStatement, "select") || strings.HasPrefix(trimmedStatement, "with") || strings.HasPrefix(trimmedStatement, "(")
}
// addLimitFor12cAndLater adds a FETCH NEXT clause for Oracle 12c and later versions.
// Uses the modern SQL standard approach, falling back to 11g approach on error.
func addLimitFor12cAndLater(statement string, limit int) string {
if !isSelectOrWithStatement(statement) {
return statement
}
stmt, err := addFetchNextClause(statement, limit)
if err != nil {
slog.Error("failed to add FETCH NEXT clause, falling back to ROWNUM",
slog.String("statement", statement), log.BBError(err))
return addLimitFor11g(statement, limit)
}
return stmt
}
// addFetchNextClause adds a FETCH NEXT clause to a SELECT statement using AST parsing.
// This provides more precise placement of the limit clause compared to simple string wrapping.
func addFetchNextClause(statement string, limitCount int) (string, error) {
list, err := ParsePLSQL(statement)
if err != nil {
return "", err
}
if list == nil || len(list.Items) == 0 {
return "", errors.New("no parse results")
}
if len(list.Items) > 1 {
return "", errors.Errorf("expected single statement, got %d statements", len(list.Items))
}
raw, ok := list.Items[0].(*oracleast.RawStmt)
if !ok {
return "", errors.Errorf("expected raw statement, got %T", list.Items[0])
}
selectStmt, ok := raw.Stmt.(*oracleast.SelectStmt)
if !ok {
return statement, nil
}
res, err := rewriteOracleSelectFetch(statement, selectStmt, limitCount)
if err != nil {
return "", err
}
// https://stackoverflow.com/questions/27987882/how-can-i-solve-ora-00911-invalid-character-error
res = strings.TrimRightFunc(res, utils.IsSpaceOrSemicolon)
return res, nil
}
func rewriteOracleSelectFetch(sql string, selectStmt *oracleast.SelectStmt, limitCount int) (string, error) {
target, parenthesized := rightmostOracleSetSelect(sql, selectStmt)
if !parenthesized {
if target.FetchFirst != nil {
return rewriteOracleFetchClause(sql, target.FetchFirst, limitCount)
}
if target.ForUpdate != nil && target.ForUpdate.Loc.Start > 0 && target.ForUpdate.Loc.Start <= len(sql) {
return sql[:target.ForUpdate.Loc.Start] + fmt.Sprintf("FETCH NEXT %d ROWS ONLY ", limitCount) + sql[target.ForUpdate.Loc.Start:], nil
}
}
loc := oracleast.NodeLoc(target)
if loc.End < 0 || loc.End > len(sql) {
return "", errors.Errorf("invalid SELECT end position %d", loc.End)
}
return sql[:loc.End] + fmt.Sprintf(" FETCH NEXT %d ROWS ONLY", limitCount) + sql[loc.End:], nil
}
// rightmostOracleSetSelect returns the operand that omni attaches the ORDER
// BY, row limit, and FOR UPDATE ending a set operation to: its last operand.
// omni chains operands through Rarg, and only the text tells a parenthesized
// operand from the rest of the chain. The walk stops at a parenthesized one
// and reports it, as the clauses inside it limit that operand alone.
func rightmostOracleSetSelect(sql string, selectStmt *oracleast.SelectStmt) (*oracleast.SelectStmt, bool) {
for selectStmt.Op != oracleast.SETOP_NONE && selectStmt.Rarg != nil {
selectStmt = selectStmt.Rarg
if isParenthesizedOracleQuery(sql, oracleast.NodeLoc(selectStmt)) {
return selectStmt, true
}
}
return selectStmt, false
}
// isParenthesizedOracleQuery reports whether one pair of parentheses encloses
// all of sql[loc.Start:loc.End].
func isParenthesizedOracleQuery(sql string, loc oracleast.Loc) bool {
if loc.Start < 0 || loc.End > len(sql) || loc.Start >= loc.End || sql[loc.Start] != '(' {
return false
}
lexer := oracleparser.NewLexerRange(sql, loc.Start, loc.End)
depth := 0
for {
tok := lexer.NextToken()
if tok.End <= tok.Loc {
return false
}
switch tok.Type {
case '(':
depth++
case ')':
depth--
if depth == 0 {
return tok.End == loc.End
}
default:
}
}
}
func rewriteOracleFetchClause(sql string, fetch *oracleast.FetchFirstClause, limitCount int) (string, error) {
if fetch.Count == nil {
if fetch.Loc.Start < 0 || fetch.Loc.End < fetch.Loc.Start || fetch.Loc.End > len(sql) {
return "", errors.Errorf("invalid FETCH position %d:%d", fetch.Loc.Start, fetch.Loc.End)
}
if hasOracleFetchKeyword(sql, fetch.Loc) {
return sql, nil
}
return sql[:fetch.Loc.End] + fmt.Sprintf(" FETCH NEXT %d ROWS ONLY", limitCount) + sql[fetch.Loc.End:], nil
}
if fetch.Percent {
return "", errors.Errorf("cannot rewrite PERCENT FETCH expression")
}
existingLimit := extractOracleFetchCount(fetch.Count)
if existingLimit > 0 && existingLimit <= limitCount {
return sql, nil
}
loc := oracleast.NodeLoc(fetch.Count)
loc = trimOracleLocSpace(sql, loc)
if loc.Start >= 0 && loc.End > loc.Start && loc.End <= len(sql) {
if existingLimit <= 0 {
return "", errors.Errorf("cannot rewrite non-constant FETCH expression")
}
return sql[:loc.Start] + fmt.Sprintf("%d", limitCount) + sql[loc.End:], nil
}
return "", errors.Errorf("cannot rewrite FETCH expression")
}
func hasOracleFetchKeyword(sql string, loc oracleast.Loc) bool {
segment := sql[loc.Start:loc.End]
lexer := oracleparser.NewLexer(segment)
for {
tok := lexer.NextToken()
if tok.Loc == tok.End && tok.End >= len(segment) {
return false
}
if tok.Loc < 0 || tok.End > len(segment) || tok.End <= tok.Loc {
return false
}
if strings.EqualFold(segment[tok.Loc:tok.End], "FETCH") {
return true
}
}
}
func extractOracleFetchCount(node oracleast.Node) int {
limit, ok := node.(*oracleast.NumberLiteral)
if !ok || limit.IsFloat || limit.Ival <= 0 {
return 0
}
return int(limit.Ival)
}
func trimOracleLocSpace(sql string, loc oracleast.Loc) oracleast.Loc {
for loc.Start < loc.End && loc.Start < len(sql) && unicode.IsSpace(rune(sql[loc.Start])) {
loc.Start++
}
for loc.End > loc.Start && loc.End <= len(sql) && unicode.IsSpace(rune(sql[loc.End-1])) {
loc.End--
}
return loc
}
// skipAddLimit checks if the statement needs a limit clause.
// For Oracle, we think the statement like "SELECT xxx FROM DUAL" does not need a limit clause.
// More details, xxx can not be a subquery.
func skipAddLimit(stmt string) (bool, error) {
list, err := ParsePLSQL(stmt)
if err != nil {
return false, err
}
if list == nil || len(list.Items) == 0 {
return false, nil
}
// Multiple statements should not skip limit
if len(list.Items) > 1 {
return false, nil
}
raw, ok := list.Items[0].(*oracleast.RawStmt)
if !ok {
return false, nil
}
selectStmt, ok := raw.Stmt.(*oracleast.SelectStmt)
if !ok {
return false, nil
}
if !isSimpleOracleSelect(selectStmt) {
return false, nil
}
if !isOracleSelectFromDual(selectStmt) {
return false, nil
}
return !hasOracleSubqueriesInSelection(selectStmt), nil
}
func isSimpleOracleSelect(selectStmt *oracleast.SelectStmt) bool {
return selectStmt.WithClause == nil &&
!selectStmt.Distinct &&
!selectStmt.UniqueKw &&
!selectStmt.All &&
selectStmt.Into == nil &&
selectStmt.IntoVars == nil &&
selectStmt.WhereClause == nil &&
selectStmt.Hierarchical == nil &&
selectStmt.GroupClause == nil &&
selectStmt.HavingClause == nil &&
selectStmt.ModelClause == nil &&
len(selectStmt.WindowDefs) == 0 &&
selectStmt.QualifyClause == nil &&
selectStmt.OrderBy == nil &&
selectStmt.ForUpdate == nil &&
selectStmt.FetchFirst == nil &&
selectStmt.Op == oracleast.SETOP_NONE
}
func isOracleSelectFromDual(selectStmt *oracleast.SelectStmt) bool {
if selectStmt.FromClause == nil || len(selectStmt.FromClause.Items) != 1 {
return false
}
tableRef, ok := selectStmt.FromClause.Items[0].(*oracleast.TableRef)
if !ok || tableRef.Name == nil {
return false
}
return tableRef.Alias == nil &&
tableRef.Name.Schema == "" &&
tableRef.Name.DBLink == "" &&
strings.EqualFold(tableRef.Name.Name, "DUAL")
}
func hasOracleSubqueriesInSelection(selectStmt *oracleast.SelectStmt) bool {
if selectStmt.TargetList == nil || len(selectStmt.TargetList.Items) == 0 {
return true
}
for _, item := range selectStmt.TargetList.Items {
target, ok := item.(*oracleast.ResTarget)
if !ok || target.Expr == nil {
return true
}
if _, ok := target.Expr.(*oracleast.Star); ok {
return true
}
hasSubquery := false
oracleast.Inspect(target.Expr, func(node oracleast.Node) bool {
if _, ok := node.(*oracleast.SubqueryExpr); ok {
hasSubquery = true
return false
}
return true
})
if hasSubquery {
return true
}
}
return false
}