Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

204 KB
Raw Blame
package main

import (
	"bufio"
	"context"
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"math"
	"net"
	"net/url"
	"os"
	"regexp"
	"sort"
	"strconv"
	"strings"
	"sync"
	"time"

	_ "github.com/sijms/go-ora/v2"
	go_ora "github.com/sijms/go-ora/v2"
	"github.com/sijms/go-ora/v2/converters"
	"golang.org/x/text/encoding/simplifiedchinese"
	"golang.org/x/text/transform"
)

const protocolVersion = 1
const multiSessionProtocolVersion = 2
const defaultMaxRows = 1000

// A normal data-grid page contains 100 rows. Prefetching a little more than
// one page reduces round trips while reading subsequent pages without
// buffering the much larger export limit.
const oracleDefaultPrefetchRows = "256"
const oracleCharsetZHS32GB18030 = 854
const legacyAgentSessionID = "__legacy__"
const maxAgentSessions = 256
const oracleLegacyLOBMaxMajorVersion = 10
const oracleDatabaseVersionProbeTimeout = 3 * time.Second

const oracleDatabaseVersionSQL = `
SELECT VERSION
FROM PRODUCT_COMPONENT_VERSION
WHERE PRODUCT LIKE 'Oracle%Database%'
  AND ROWNUM = 1`

const oracleDisableSegmentAttributesSQL = `BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE); END;`
const oracleEnableSegmentAttributesSQL = `BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', TRUE); END;`

var (
	oraclePlSQLBlockStartRegexp          = regexp.MustCompile(`(?is)^\s*(?:DECLARE|BEGIN|CREATE\s+(?:OR\s+REPLACE\s+)?(?:(?:EDITIONABLE|NONEDITIONABLE)\s+)?(?:FUNCTION|PROCEDURE|TRIGGER|PACKAGE(?:\s+BODY)?|TYPE(?:\s+BODY)?))\b`)
	oraclePlSQLBlockEndRegexp            = regexp.MustCompile(`(?is)\bEND\s*;\s*$`)
	oracleNamedPlSQLBlockEndRegexp       = regexp.MustCompile(`(?is)\bEND\s+([A-Z0-9_$#]+)\s*;\s*$`)
	oracleUnsupportedServerCharsetRegexp = regexp.MustCompile(`server use charset with id: ([0-9]+).*not supported by the driver`)
	oracleVersionNumberRegexp            = regexp.MustCompile(`(?:^|[^0-9])([0-9]+)\.[0-9]+`)
	oracleNotNullConstraintRegexp        = regexp.MustCompile(`(?i)^\s*\(*\s*(?:"((?:[^"]|"")*)"|([A-Z0-9_$#]+))\s+IS\s+NOT\s+NULL\s*\)*\s*$`)
	oracleDatabaseVersionQueries         = []string{
		oracleDatabaseVersionSQL,
		`SELECT BANNER FROM V$VERSION WHERE BANNER LIKE 'Oracle%Database%' AND ROWNUM = 1`,
	}
	oracleStringConverters = map[int]converters.IStringConverter{
		oracleCharsetZHS32GB18030: oracleGB18030Converter{},
	}
)

const oracleListDatabasesSQL = `
SELECT username AS owner
FROM all_users
WHERE username IS NOT NULL
  AND username NOT IN (
    'SYS','SYSTEM','SYSMAN','DBSNMP','SYSBACKUP','SYSDG','SYSKM','SYSRAC','OUTLN',
    'AUDSYS','LBACSYS','DVF','DVSYS','APPQOSSYS','CTXSYS','MDSYS','MDDATA',
    'ORDSYS','ORDDATA','ORDPLUGINS','XDB','ANONYMOUS','EXFSYS',
    'GSMADMIN_INTERNAL','GSMCATUSER','GSMROOTUSER','GSMUSER','OJVMSYS','OLAPSYS',
    'ORACLE_OCM','SI_INFORMTN_SCHEMA','WMSYS','XS$NULL','DBSFWUSER',
    'REMOTE_SCHEDULER_AGENT','PDBADMIN','DGPDB_INT','OPS$ORACLE',
    'GGSYS','FLOWS_FILES','APEX_PUBLIC_USER'
  )
  AND username NOT LIKE 'APEX_%'
  AND username NOT LIKE 'FLOWS_%'
  AND username NOT LIKE '%$%'
ORDER BY CASE
  WHEN username = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') THEN 0
  WHEN username = SYS_CONTEXT('USERENV', 'SESSION_USER') THEN 1
  ELSE 2
END, username`

// Oracle schemas are users, so expose every user visible through ALL_USERS; system filtering remains database-picker behavior.
const oracleListSchemasSQL = `
SELECT username AS owner
FROM all_users
WHERE username IS NOT NULL
ORDER BY CASE
  WHEN username = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') THEN 0
  WHEN username = SYS_CONTEXT('USERENV', 'SESSION_USER') THEN 1
  ELSE 2
END, username`

// Sorting SYS views through ALL_OBJECTS can take over a minute on Oracle 11g; use the specialized dictionaries.
const oracleListTablesBaseSQL = `
SELECT OBJECT_NAME, TABLE_TYPE, COMMENTS
FROM (
SELECT t.TABLE_NAME AS OBJECT_NAME,
       'TABLE' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM ALL_TABLES t
WHERE t.OWNER = :1
  AND t.NESTED = 'NO'
  AND NOT EXISTS (
    SELECT 1
    FROM ALL_MVIEWS mv
    WHERE mv.OWNER = t.OWNER
      AND mv.MVIEW_NAME = t.TABLE_NAME
  )
UNION ALL
SELECT v.VIEW_NAME AS OBJECT_NAME,
       'VIEW' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM ALL_VIEWS v
WHERE v.OWNER = :2
UNION ALL
SELECT mv.MVIEW_NAME AS OBJECT_NAME,
       'MATERIALIZED_VIEW' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM ALL_MVIEWS mv
WHERE mv.OWNER = :3
)`
const oracleListTablesSessionUserBaseSQL = `
SELECT OBJECT_NAME, TABLE_TYPE, COMMENTS
FROM (
SELECT t.TABLE_NAME AS OBJECT_NAME,
       'TABLE' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM USER_TABLES t
WHERE t.NESTED = 'NO'
  AND NOT EXISTS (
    SELECT 1
    FROM USER_MVIEWS mv
    WHERE mv.MVIEW_NAME = t.TABLE_NAME
  )
UNION ALL
SELECT v.VIEW_NAME AS OBJECT_NAME,
       'VIEW' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM USER_VIEWS v
UNION ALL
SELECT mv.MVIEW_NAME AS OBJECT_NAME,
       'MATERIALIZED_VIEW' AS TABLE_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS
FROM USER_MVIEWS mv
)`
const oracleListTablesOrderSQL = `ORDER BY OBJECT_NAME`
const oracleListTablesSQL = oracleListTablesBaseSQL + "\n" + oracleListTablesOrderSQL
const oracleListObjectsBaseSQL = `
SELECT OBJECT_NAME, OBJECT_TYPE, COMMENTS, PARENT_SCHEMA, PARENT_NAME
FROM (
SELECT t.TABLE_NAME AS OBJECT_NAME,
       'TABLE' AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_SCHEMA,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM ALL_TABLES t
WHERE t.OWNER = :1
  AND t.NESTED = 'NO'
  AND NOT EXISTS (
    SELECT 1
    FROM ALL_OBJECTS mv
    WHERE mv.OWNER = t.OWNER
      AND mv.OBJECT_NAME = t.TABLE_NAME
      AND mv.OBJECT_TYPE = 'MATERIALIZED VIEW'
  )
UNION ALL
SELECT o.OBJECT_NAME,
       CASE o.OBJECT_TYPE
         WHEN 'PACKAGE BODY' THEN 'PACKAGE_BODY'
         WHEN 'MATERIALIZED VIEW' THEN 'MATERIALIZED_VIEW'
         ELSE o.OBJECT_TYPE
       END AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       i.TABLE_OWNER AS PARENT_SCHEMA,
       i.TABLE_NAME AS PARENT_NAME
FROM ALL_OBJECTS o
LEFT JOIN ALL_INDEXES i
  ON o.OBJECT_TYPE = 'INDEX' AND i.OWNER = o.OWNER AND i.INDEX_NAME = o.OBJECT_NAME
WHERE o.OWNER = :2
  AND o.OBJECT_TYPE IN ('VIEW', 'MATERIALIZED VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE', 'PACKAGE', 'PACKAGE BODY', 'INDEX')
UNION ALL
SELECT s.SYNONYM_NAME AS OBJECT_NAME,
       'SYNONYM' AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_SCHEMA,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM ALL_SYNONYMS s
WHERE s.OWNER = :3
)`
const oracleListObjectsSessionUserBaseSQL = `
SELECT OBJECT_NAME, OBJECT_TYPE, COMMENTS, PARENT_SCHEMA, PARENT_NAME
FROM (
SELECT t.TABLE_NAME AS OBJECT_NAME,
       'TABLE' AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_SCHEMA,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM USER_TABLES t
WHERE t.NESTED = 'NO'
  AND NOT EXISTS (
    SELECT 1
    FROM USER_OBJECTS mv
    WHERE mv.OBJECT_NAME = t.TABLE_NAME
      AND mv.OBJECT_TYPE = 'MATERIALIZED VIEW'
  )
UNION ALL
SELECT o.OBJECT_NAME,
       CASE o.OBJECT_TYPE
         WHEN 'PACKAGE BODY' THEN 'PACKAGE_BODY'
         WHEN 'TYPE BODY' THEN 'TYPE_BODY'
         WHEN 'MATERIALIZED VIEW' THEN 'MATERIALIZED_VIEW'
         ELSE o.OBJECT_TYPE
       END AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       i.TABLE_OWNER AS PARENT_SCHEMA,
       i.TABLE_NAME AS PARENT_NAME
FROM USER_OBJECTS o
LEFT JOIN USER_INDEXES i ON o.OBJECT_TYPE = 'INDEX' AND i.INDEX_NAME = o.OBJECT_NAME
WHERE o.OBJECT_TYPE IN ('VIEW', 'MATERIALIZED VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE', 'PACKAGE', 'PACKAGE BODY', 'INDEX', 'TYPE', 'TYPE BODY')
UNION ALL
SELECT s.SYNONYM_NAME AS OBJECT_NAME,
       'SYNONYM' AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_SCHEMA,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM USER_SYNONYMS s
)`
const oracleListObjectsOrderSQL = `ORDER BY CASE OBJECT_TYPE
  WHEN 'TABLE' THEN 0
  WHEN 'VIEW' THEN 1
  WHEN 'MATERIALIZED_VIEW' THEN 2
  WHEN 'PROCEDURE' THEN 3
  WHEN 'FUNCTION' THEN 4
  WHEN 'SEQUENCE' THEN 5
  WHEN 'SYNONYM' THEN 6
  WHEN 'PACKAGE' THEN 7
  WHEN 'TYPE' THEN 8
  WHEN 'TYPE_BODY' THEN 9
  ELSE 10
END, OBJECT_NAME, OBJECT_TYPE`
const oracleListTriggersSQL = `
SELECT t.TRIGGER_NAME,
       t.TRIGGERING_EVENT,
       t.TRIGGER_TYPE,
       t.DESCRIPTION,
       t.STATUS,
       t.WHEN_CLAUSE,
       t.REFERENCING_NAMES,
       s.LINE,
       s.TEXT
FROM ALL_TRIGGERS t
LEFT JOIN ALL_SOURCE s
  ON s.OWNER = t.OWNER
 AND s.NAME = t.TRIGGER_NAME
 AND s.TYPE = 'TRIGGER'
WHERE t.OWNER = :1
  AND t.TABLE_NAME = :2
  AND t.BASE_OBJECT_TYPE IN ('TABLE', 'VIEW')
ORDER BY t.TRIGGER_NAME, s.LINE`

type request struct {
	ID     json.RawMessage            `json:"id"`
	Method string                     `json:"method"`
	Params map[string]json.RawMessage `json:"params"`
}

type response struct {
	JSONRPC string          `json:"jsonrpc,omitempty"`
	ID      json.RawMessage `json:"id,omitempty"`
	Result  any             `json:"result,omitempty"`
	Error   *rpcError       `json:"error,omitempty"`
}

type rpcError struct {
	Code    int           `json:"code"`
	Message string        `json:"message"`
	Data    *rpcErrorData `json:"data,omitempty"`
}

// oracleDriverPanicError keeps a third-party driver panic on the request path
// so the agent can try a safe projection rewrite instead of terminating the
// process and breaking the RPC stream.
type oracleDriverPanicError struct {
	value any
}

func (e oracleDriverPanicError) Error() string {
	return fmt.Sprintf("oracle driver panic: %v", e.value)
}

type connectParams struct {
	Host             string `json:"host"`
	Port             int    `json:"port"`
	Database         string `json:"database"`
	Username         string `json:"username"`
	Password         string `json:"password"`
	SysDBA           bool   `json:"sysdba"`
	URLParams        string `json:"url_params"`
	ConnectionString string `json:"connection_string"`
	// DriverProfile 由 DBX 下传;"oci" 走 OCI(thick)驱动,其余走内置的 thin 驱动。
	DriverProfile string `json:"driver_profile"`
}

// usesOCIProfile 判断本次连接是否选择了 OCI(thick)驱动。
func usesOCIProfile(params connectParams) bool {
	return strings.EqualFold(strings.TrimSpace(params.DriverProfile), "oci")
}

type completionAssistantRequest struct {
	ConnectionID  string   `json:"connection_id"`
	Database      string   `json:"database"`
	Schema        string   `json:"schema"`
	ObjectKinds   []string `json:"object_kinds"`
	Mask          string   `json:"mask"`
	CaseSensitive bool     `json:"case_sensitive"`
	GlobalSearch  bool     `json:"global_search"`
	MaxResults    int      `json:"max_results"`
	ParentSchema  string   `json:"parent_schema"`
	ParentName    string   `json:"parent_name"`
	MatchMode     string   `json:"match_mode"`
}

type completionAssistantCandidate struct {
	Name         string  `json:"name"`
	Kind         string  `json:"kind"`
	Database     *string `json:"database"`
	Schema       *string `json:"schema"`
	ParentSchema *string `json:"parent_schema"`
	ParentName   *string `json:"parent_name"`
	Comment      *string `json:"comment"`
	DataType     *string `json:"data_type"`
	Signature    *string `json:"signature"`
}

type completionAssistantResponse struct {
	Candidates   []completionAssistantCandidate `json:"candidates"`
	Incomplete   bool                           `json:"incomplete"`
	FallbackUsed bool                           `json:"fallback_used"`
}

type queryOptions struct {
	SQL         string `json:"sql"`
	Database    string `json:"database"`
	Schema      string `json:"schema"`
	MaxRows     int    `json:"maxRows"`
	FetchSize   int    `json:"fetchSize"`
	TimeoutSecs int    `json:"timeoutSecs"`
	DeferLOBs   bool   `json:"deferLobs"`
}

const largeValueBytesColumnPrefix = "__DBX_LARGE_VALUE_BYTES_"

type queryResult struct {
	Columns         []string       `json:"columns"`
	ColumnTypes     []string       `json:"column_types"`
	Rows            [][]any        `json:"rows"`
	AffectedRows    int64          `json:"affected_rows"`
	ExecutionTimeMS int64          `json:"execution_time_ms"`
	Truncated       bool           `json:"truncated"`
	Messages        []queryMessage `json:"messages,omitempty"`
}

func (r queryResult) MarshalJSON() ([]byte, error) {
	type alias queryResult
	value := alias(r)
	if value.Columns == nil {
		value.Columns = []string{}
	}
	if value.ColumnTypes == nil {
		value.ColumnTypes = []string{}
	}
	if value.Rows == nil {
		value.Rows = [][]any{}
	}
	data, err := json.Marshal(value)
	if err == nil {
		return data, nil
	}
	rows, changed := normalizeNonFiniteQueryRows(value.Rows)
	if !changed {
		return nil, err
	}
	value.Rows = rows
	return json.Marshal(value)
}

type queryPageResult struct {
	Columns         []string       `json:"columns"`
	ColumnTypes     []string       `json:"column_types"`
	Rows            [][]any        `json:"rows"`
	AffectedRows    int64          `json:"affected_rows"`
	ExecutionTimeMS int64          `json:"execution_time_ms"`
	Truncated       bool           `json:"truncated"`
	SessionID       *string        `json:"session_id"`
	HasMore         bool           `json:"has_more"`
	Messages        []queryMessage `json:"messages,omitempty"`
}

func (r queryPageResult) MarshalJSON() ([]byte, error) {
	type alias queryPageResult
	value := alias(r)
	if value.Columns == nil {
		value.Columns = []string{}
	}
	if value.ColumnTypes == nil {
		value.ColumnTypes = []string{}
	}
	if value.Rows == nil {
		value.Rows = [][]any{}
	}
	data, err := json.Marshal(value)
	if err == nil {
		return data, nil
	}
	rows, changed := normalizeNonFiniteQueryRows(value.Rows)
	if !changed {
		return nil, err
	}
	value.Rows = rows
	return json.Marshal(value)
}

func normalizeNonFiniteQueryRows(rows [][]any) ([][]any, bool) {
	result := rows
	changed := false
	for rowIndex, row := range rows {
		var normalizedRow []any
		for columnIndex, value := range row {
			floatValue, ok := value.(float64)
			if !ok || (!math.IsNaN(floatValue) && !math.IsInf(floatValue, 0)) {
				continue
			}
			if normalizedRow == nil {
				normalizedRow = append([]any(nil), row...)
			}
			normalizedRow[columnIndex] = fmt.Sprint(floatValue)
		}
		if normalizedRow != nil {
			if !changed {
				result = append([][]any(nil), rows...)
				changed = true
			}
			result[rowIndex] = normalizedRow
		}
	}
	return result, changed
}

type querySession struct {
	rows           *sql.Rows
	conn           *sql.Conn
	columns        []string
	columnTypes    []string
	pending        []any
	remaining      int
	outputIdentity any
}

type oracleColumnMeta struct {
	Name          string
	DataType      string
	DataTypeOwner string
}

type oracleColumnMetaLoader func(schema, table string) ([]oracleColumnMeta, error)

type databaseInfo struct {
	Name string `json:"name"`
}

type tableInfo struct {
	Name      string  `json:"name"`
	TableType string  `json:"table_type"`
	Comment   *string `json:"comment"`
}

type metadataListConstraints struct {
	Filter        string
	Limit         int
	Offset        int
	ObjectTypes   []string
	Exact         bool
	ExactIdentity bool
}

type objectInfo struct {
	Name         string       `json:"name"`
	ObjectType   string       `json:"object_type"`
	Schema       string       `json:"schema"`
	Comment      *string      `json:"comment"`
	Valid        *bool        `json:"valid,omitempty"`
	ParentSchema *string      `json:"parent_schema,omitempty"`
	ParentName   *string      `json:"parent_name,omitempty"`
	Trigger      *triggerInfo `json:"trigger,omitempty"`
}

type columnInfo struct {
	Name                   string  `json:"name"`
	DataType               string  `json:"data_type"`
	IsNullable             bool    `json:"is_nullable"`
	ColumnDefault          *string `json:"column_default"`
	IsPrimaryKey           bool    `json:"is_primary_key"`
	Extra                  *string `json:"extra"`
	Comment                *string `json:"comment"`
	NumericPrecision       *int    `json:"numeric_precision"`
	NumericScale           *int    `json:"numeric_scale"`
	DataLength             *int    `json:"-"`
	CharacterMaximumLength *int    `json:"character_maximum_length"`
	CharacterLengthUnit    *string `json:"-"`
}

type indexInfo struct {
	Owner               *string  `json:"owner,omitempty"`
	Name                string   `json:"name"`
	Columns             []string `json:"columns"`
	IsUnique            bool     `json:"is_unique"`
	IsPrimary           bool     `json:"is_primary"`
	Filter              *string  `json:"filter"`
	IndexType           *string  `json:"index_type"`
	IncludedColumns     []string `json:"included_columns"`
	Comment             *string  `json:"comment"`
	KeyIsExpression     []bool   `json:"key_is_expression"`
	KeyDirections       []string `json:"key_directions"`
	Visibility          *string  `json:"visibility"`
	KeyMetadataComplete bool     `json:"key_metadata_complete"`
}

func (i indexInfo) MarshalJSON() ([]byte, error) {
	type alias indexInfo
	value := alias(i)
	if value.Columns == nil {
		value.Columns = []string{}
	}
	if value.IncludedColumns == nil {
		value.IncludedColumns = []string{}
	}
	if value.KeyIsExpression == nil {
		value.KeyIsExpression = []bool{}
	}
	if value.KeyDirections == nil {
		value.KeyDirections = []string{}
	}
	return json.Marshal(value)
}

type foreignKeyInfo struct {
	Name      string `json:"name"`
	Column    string `json:"column"`
	RefSchema string `json:"ref_schema"`
	RefTable  string `json:"ref_table"`
	RefColumn string `json:"ref_column"`
	OnDelete  string `json:"on_delete"`
}

type triggerInfo struct {
	Name           string                 `json:"name"`
	Event          string                 `json:"event"`
	Timing         string                 `json:"timing"`
	Statement      *string                `json:"statement,omitempty"`
	Enabled        *bool                  `json:"enabled,omitempty"`
	Valid          *bool                  `json:"valid,omitempty"`
	Condition      *string                `json:"condition,omitempty"`
	OracleMetadata *oracleTriggerMetadata `json:"oracle_metadata,omitempty"`
}

// constraintInfo represents primary key, unique, and check constraints for a
// table. Foreign keys are served separately by listForeignKeys, so this only
// covers constraint types 'P', 'U', and 'C'.
type constraintInfo struct {
	Name              string                    `json:"name"`
	ConstraintType    string                    `json:"constraint_type"`
	Definition        string                    `json:"definition"`
	Columns           []string                  `json:"columns"`
	RefSchema         *string                   `json:"ref_schema,omitempty"`
	RefTable          *string                   `json:"ref_table,omitempty"`
	RefColumns        []string                  `json:"ref_columns"`
	MatchType         *string                   `json:"match_type,omitempty"`
	OnUpdate          *string                   `json:"on_update,omitempty"`
	OnDelete          *string                   `json:"on_delete,omitempty"`
	Deferrable        bool                      `json:"deferrable"`
	InitiallyDeferred bool                      `json:"initially_deferred"`
	Enabled           bool                      `json:"enabled"`
	Valid             bool                      `json:"valid"`
	OracleMetadata    *oracleConstraintMetadata `json:"oracle_metadata,omitempty"`
}

type oracleConstraintMetadata struct {
	MetadataComplete bool                           `json:"metadataComplete"`
	Owner            string                         `json:"owner"`
	ConstraintType   *string                        `json:"constraintType"`
	Status           *string                        `json:"status"`
	Validated        *string                        `json:"validated"`
	Deferrable       *string                        `json:"deferrable"`
	Deferred         *string                        `json:"deferred"`
	Rely             *string                        `json:"rely"`
	IndexOwner       *string                        `json:"indexOwner"`
	IndexName        *string                        `json:"indexName"`
	IndexSharing     *string                        `json:"indexSharing"`
	Index            *oracleConstraintIndexMetadata `json:"index"`
}

type oracleConstraintIndexMetadata struct {
	MetadataComplete bool     `json:"metadataComplete"`
	Owner            string   `json:"owner"`
	TableOwner       string   `json:"tableOwner"`
	TableName        string   `json:"tableName"`
	IndexType        string   `json:"indexType"`
	Uniqueness       string   `json:"uniqueness"`
	Status           string   `json:"status"`
	Partitioned      bool     `json:"partitioned"`
	Visibility       string   `json:"visibility"`
	Secondary        *string  `json:"secondary"`
	Columns          []string `json:"columns"`
	Directions       []string `json:"directions"`
	KeyIsExpression  []bool   `json:"keyIsExpression"`
	IncludedColumns  []string `json:"includedColumns"`
}

func (c constraintInfo) MarshalJSON() ([]byte, error) {
	type alias constraintInfo
	value := alias(c)
	if value.Columns == nil {
		value.Columns = []string{}
	}
	if value.RefColumns == nil {
		value.RefColumns = []string{}
	}
	return json.Marshal(value)
}

type server struct {
	db                     *sql.DB
	params                 connectParams
	legacyLOBFetchDeferred bool
	// manualConn + manualTx pin one physical Oracle session for interactive
	// commit/rollback control across multiple execute_query RPCs.
	manualConn             *sql.Conn
	manualTx               *sql.Tx
	legacyJob              *legacyJobSession
	sessions               map[string]*querySession
	tableReadSessions      map[string]*querySession
	nextSessionID          int64
	nextTableReadSessionID int64
	activeCancelMu         sync.Mutex
	activeCancel           context.CancelFunc
	activeRows             map[*sql.Rows]context.CancelFunc
	activeTimer            *time.Timer
	activeTimedOut         bool
	dbmsOutput             dbmsOutputTracker
	dbmsOutputBinding      dbmsOutputBinding
	statementCtx           context.Context
	statementCancel        context.CancelFunc
	statementConn          *sql.Conn
	statementSchema        string
	repinnedStatementConn  *sql.Conn
	plDebugMu              sync.Mutex
	plDebugSessions        map[string]*plDebugSession
	plAllocation           *plDebugAllocation
	plConnection           *sql.Conn
	plConnector            func(connectParams) (driver.Connector, error)
	plDebuggeeAllocation   *plDebugAllocation
	plDebuggeeConnection   *sql.Conn
	plDebuggeeDB           *sql.DB
	plDebuggeeOpenDone     chan struct{}
	plDebuggeeOpenErr      error
	plRetiring             bool
	plCleanupDeadline      time.Time
	plDebuggeeTx           *sql.Tx
	plTxBeginDone          chan struct{}
	plTxBeginErr           error
	plTxRollbackDone       chan struct{}
	plTxRollbackErr        error
}

type agentSession struct {
	server *server
	mu     sync.Mutex
}

type runtimeServer struct {
	mu               sync.RWMutex
	sessions         map[string]*agentSession
	plDebugRuntime   bool
	plDebugConnector func(connectParams) (driver.Connector, error)
	plNativeEntries  map[string]*plDebugNativeEntry
	plMetadataBusy   bool
	plCompileEntries map[string]*plDebugNativeCompileEntry
}

func main() {
	runOracleAgent(os.Stdin, os.Stdout, os.Stderr, newRuntimeServer())
}

func runOracleAgent(input io.Reader, output, diagnostics io.Writer, runtime *runtimeServer) {
	encoder := json.NewEncoder(output)
	var encoderMu sync.Mutex
	var requests sync.WaitGroup
	shutdown := make(chan struct{})
	var shutdownOnce sync.Once
	fmt.Fprintln(output, `{"ready":true}`)

	scanner := bufio.NewScanner(input)
	scanner.Buffer(make([]byte, 0, 64*1024), 512*1024*1024)
	for scanner.Scan() {
		line := strings.TrimSpace(scanner.Text())
		if line == "" {
			continue
		}
		var requestEnvelope request
		if json.Unmarshal([]byte(line), &requestEnvelope) == nil && requestEnvelope.Method == "shutdown" {
			if runtime.plDebugRuntime {
				_ = runtime.closeAllSessions()
			}
			requests.Wait()
			resp, _ := runtime.handleLine(line)
			encoderMu.Lock()
			err := encoder.Encode(resp)
			encoderMu.Unlock()
			if err != nil {
				fmt.Fprintf(diagnostics, "failed to write response: %v\n", err)
			}
			if runtime.plDebugRuntime {
				runtime.awaitNativeDrain()
			}
			return
		}
		requests.Add(1)
		go func() {
			defer requests.Done()
			resp, shouldShutdown := runtime.handleLine(line)
			encoderMu.Lock()
			err := encoder.Encode(resp)
			encoderMu.Unlock()
			if err != nil {
				fmt.Fprintf(diagnostics, "failed to write response: %v\n", err)
				shutdownOnce.Do(func() { close(shutdown) })
				return
			}
			if shouldShutdown {
				shutdownOnce.Do(func() { close(shutdown) })
			}
		}()
		select {
		case <-shutdown:
			if runtime.plDebugRuntime {
				_ = runtime.closeAllSessions()
			}
			requests.Wait()
			if runtime.plDebugRuntime {
				_ = runtime.closeAllSessions()
				runtime.awaitNativeDrain()
			}
			return
		default:
		}
	}
	if runtime.plDebugRuntime {
		_ = runtime.closeAllSessions()
	}
	requests.Wait()
	if runtime.plDebugRuntime {
		_ = runtime.closeAllSessions()
		runtime.awaitNativeDrain()
	}
	if err := scanner.Err(); err != nil && !errors.Is(err, io.EOF) {
		fmt.Fprintf(diagnostics, "failed to read stdin: %v\n", err)
	}
}

func newRuntimeServer() *runtimeServer {
	return &runtimeServer{sessions: map[string]*agentSession{},
		plDebugRuntime:   os.Getenv("DBX_PL_DEBUG_RUNTIME_SCOPE") != "",
		plDebugConnector: newPLDebugConnector}
}

func (r *runtimeServer) handleLine(line string) (resp response, shutdown bool) {
	var req request
	// Last-resort guard: a panic anywhere on the request path must not kill
	// the agent process and break the RPC stream, so convert it to a readable
	// error response instead of an end-of-stream failure.
	defer func() {
		if recovered := recover(); recovered != nil {
			resp = errorResponse(req.ID, fmt.Errorf("agent request panic: %v", recovered), req.Method, stringParam(req.Params, "agentSessionId"))
			shutdown = false
		}
	}()
	if err := json.Unmarshal([]byte(line), &req); err != nil {
		return errorResponse(nil, err, "", ""), false
	}
	if len(req.ID) == 0 {
		req.ID = json.RawMessage("1")
	}
	if r.plDebugRuntime && strings.HasPrefix(req.Method, "pl_debug_") && stringParam(req.Params, "metadataPreparation") == "type_only" && len(line) > plDebugTypedRequestWireBytes {
		return errorResponse(req.ID, errors.New("typed debugger request wire byte budget exceeded"), req.Method, stringParam(req.Params, "agentSessionId")), false
	}
	if r.plDebugRuntime && strings.HasPrefix(req.Method, "pl_debug_") && stringParam(req.Params, "metadataPreparation") == "type_only" {
		if err := plDebugValidateTypedUnicode(line); err != nil {
			return errorResponse(req.ID, err, req.Method, stringParam(req.Params, "agentSessionId")), false
		}
	}
	result, shouldShutdown, err := r.dispatch(req.Method, req.Params)
	if err != nil {
		return errorResponse(req.ID, err, req.Method, stringParam(req.Params, "agentSessionId")), false
	}
	return response{JSONRPC: "2.0", ID: req.ID, Result: result}, shouldShutdown
}

func (r *runtimeServer) dispatch(method string, params map[string]json.RawMessage) (any, bool, error) {
	if r.plDebugRuntime && strings.HasPrefix(method, "pl_debug_") && method != "pl_debug_probe" {
		value, err := r.dispatchNativeDebug(method, params)
		return value, false, err
	}
	switch method {
	case "handshake":
		if r.plDebugRuntime {
			return map[string]any{
				"protocolVersion":      multiSessionProtocolVersion,
				"agentProtocolVersion": multiSessionProtocolVersion,
				"capabilities":         []string{"connect", "multi_session", "pl_debug_retained_results_v1"},
				"plDebug":              map[string]interface{}{"contractVersion": 1, "helperObjects": false, "setValueOperationStatus": true, "breakpointOperationStatus": true, "executeOperationStatus": true},
			}, false, nil
		}
		return map[string]any{
			"protocolVersion":      multiSessionProtocolVersion,
			"agentProtocolVersion": multiSessionProtocolVersion,
			"capabilities":         []string{"connect", "test_connection", "metadata", "query", "transaction", "ddl", "multi_session", "native_job_legacy_v1", "structured_error_v1", "exact_metadata_identity_v1"},
		}, false, nil
	case "open_session":
		agentSessionID := stringParam(params, "agentSessionId")
		if agentSessionID == "" {
			return nil, false, errors.New("agentSessionId is required")
		}
		var connectParams connectParams
		if err := decodeParams(params, &connectParams); err != nil {
			return nil, false, err
		}
		return map[string]bool{"ok": true}, false, r.openSession(agentSessionID, connectParams)
	case "close_session":
		return map[string]bool{"ok": true}, false, r.closeSession(stringParam(params, "agentSessionId"))
	case "validate_session":
		if r.plDebugRuntime {
			return nil, false, errors.New("dedicated PL/SQL sessions cannot reconnect")
		}
		agentSessionID := stringParam(params, "agentSessionId")
		session, err := r.session(agentSessionID)
		if err != nil {
			return nil, false, err
		}
		session.mu.Lock()
		defer session.mu.Unlock()
		if _, _, err := session.server.dispatch("validate_connection", params); err == nil {
			return map[string]bool{"ok": true}, false, nil
		}
		// Reconnect only this logical session. Other sessions in the runtime keep
		// their connections, transactions, cursors, and in-flight requests.
		if err := session.server.connect(session.server.params); err != nil {
			return nil, false, err
		}
		return map[string]bool{"ok": true}, false, nil
	case "cancel_session":
		if r.plDebugRuntime {
			return nil, false, errors.New("use the original PL/SQL operation cancellation")
		}
		session, err := r.session(stringParam(params, "agentSessionId"))
		if err != nil {
			return nil, false, err
		}
		session.server.cancelActiveQuery()
		return map[string]bool{"ok": true}, false, nil
	case "test_connection":
		if r.plDebugRuntime {
			return nil, false, errors.New("dedicated PL/SQL runtime cannot open test connections")
		}
		return newServer().dispatch(method, params)
	case "shutdown":
		return map[string]bool{"ok": true}, true, r.closeAllSessions()
	case "connect":
		var connectParams connectParams
		if err := decodeParams(params, &connectParams); err != nil {
			return nil, false, err
		}
		return map[string]bool{"ok": true}, false, r.replaceSession(legacyAgentSessionID, connectParams)
	case "disconnect":
		return map[string]bool{"ok": true}, false, r.closeSession(legacyAgentSessionID)
	default:
		agentSessionID := stringParam(params, "agentSessionId")
		if agentSessionID == "" {
			agentSessionID = legacyAgentSessionID
		}
		return r.withSession(agentSessionID, method, params)
	}
}

func (r *runtimeServer) withSession(agentSessionID, method string, params map[string]json.RawMessage) (any, bool, error) {
	session, err := r.session(agentSessionID)
	if err != nil {
		return nil, false, err
	}
	if r.plDebugRuntime && method != "pl_debug_probe" {
		return nil, false, errors.New("native PL/SQL dispatcher is not yet available")
	}
	// Oracle connection state, transactions, and cursors are session-scoped;
	// serialize one session while allowing separate sessions to run in parallel.
	session.mu.Lock()
	defer session.mu.Unlock()
	return session.server.dispatch(method, params)
}

func (r *runtimeServer) openSession(agentSessionID string, params connectParams) error {
	r.mu.Lock()
	if _, exists := r.sessions[agentSessionID]; exists {
		r.mu.Unlock()
		return fmt.Errorf("agent session already exists: %s", agentSessionID)
	}
	if len(r.sessions) >= maxAgentSessions {
		r.mu.Unlock()
		return fmt.Errorf("agent session limit reached: %d", maxAgentSessions)
	}
	if r.plDebugRuntime && len(r.sessions) != 0 {
		r.mu.Unlock()
		return errors.New("dedicated PL/SQL runtime already owns its logical session")
	}
	if r.plDebugRuntime && (len(r.plNativeEntries) != 0 || r.nativeCompileUnresolvedLocked()) {
		r.mu.Unlock()
		return errors.New("dedicated PL/SQL runtime retains its original operation receipt")
	}
	session := &agentSession{server: newServer()}
	if r.plDebugRuntime {
		connector, err := r.plDebugConnector(params)
		if err != nil {
			r.mu.Unlock()
			return err
		}
		session.server.plAllocation = &plDebugAllocation{connector: connector, oci: usesOCIProfile(params)}
		session.server.plConnector = r.plDebugConnector
	}
	r.sessions[agentSessionID] = session
	r.mu.Unlock()

	// Reserve the id under the registry lock, then connect outside it so unrelated
	// sessions can establish database connections concurrently.
	session.mu.Lock()
	err := session.server.connect(params)
	session.mu.Unlock()
	if err != nil {
		r.mu.Lock()
		if !r.plDebugRuntime && r.sessions[agentSessionID] == session {
			delete(r.sessions, agentSessionID)
		}
		r.mu.Unlock()
		return err
	}
	return nil
}

func (r *runtimeServer) replaceSession(agentSessionID string, params connectParams) error {
	if r.plDebugRuntime {
		return errors.New("dedicated PL/SQL runtime cannot replace its logical session")
	}
	_ = r.closeSession(agentSessionID)
	return r.openSession(agentSessionID, params)
}

func (r *runtimeServer) session(agentSessionID string) (*agentSession, error) {
	if agentSessionID == "" {
		return nil, errors.New("agentSessionId is required")
	}
	r.mu.RLock()
	session := r.sessions[agentSessionID]
	r.mu.RUnlock()
	if session == nil {
		return nil, fmt.Errorf("agent session not found: %s", agentSessionID)
	}
	return session, nil
}

func (r *runtimeServer) closeSession(agentSessionID string) error {
	if r.plDebugRuntime {
		r.mu.RLock()
		var entry *plDebugNativeEntry
		for _, candidate := range r.plNativeEntries {
			if candidate.agentSessionID == agentSessionID {
				entry = candidate
				break
			}
		}
		r.mu.RUnlock()
		if entry != nil {
			_, err := entry.close()
			if err != nil {
				return err
			}
			entry.mu.Lock()
			released, message := entry.releaseState == "released", entry.cleanupError
			entry.mu.Unlock()
			if !released {
				return errors.New(message)
			}
			return nil
		}
	}
	return r.closeNativeLogicalSession(agentSessionID)
}

func (r *runtimeServer) closeNativeLogicalSession(agentSessionID string) error {
	if agentSessionID == "" {
		return errors.New("agentSessionId is required")
	}
	r.mu.Lock()
	session := r.sessions[agentSessionID]
	if !r.plDebugRuntime {
		delete(r.sessions, agentSessionID)
	}
	r.mu.Unlock()
	if session == nil {
		return nil
	}
	if r.plDebugRuntime {
		if err := session.server.closeNativeAllocations(); err != nil {
			return err
		}
	}
	session.mu.Lock()
	defer session.mu.Unlock()
	err := session.server.disconnect()
	if r.plDebugRuntime && err == nil {
		r.mu.Lock()
		if r.sessions[agentSessionID] == session {
			delete(r.sessions, agentSessionID)
		}
		r.mu.Unlock()
	}
	return err
}

func (r *runtimeServer) closeAllSessions() error {
	if r.plDebugRuntime {
		r.mu.RLock()
		ids := make([]string, 0, len(r.sessions))
		for id := range r.sessions {
			ids = append(ids, id)
		}
		r.mu.RUnlock()
		var firstErr error
		for _, id := range ids {
			if err := r.closeSession(id); firstErr == nil && err != nil {
				firstErr = err
			}
		}
		return firstErr
	}
	r.mu.Lock()
	sessions := r.sessions
	r.sessions = map[string]*agentSession{}
	r.mu.Unlock()
	var firstErr error
	for _, session := range sessions {
		session.mu.Lock()
		err := session.server.disconnect()
		session.mu.Unlock()
		if firstErr == nil && err != nil {
			firstErr = err
		}
	}
	return firstErr
}

func newServer() *server {
	return &server{
		sessions:          map[string]*querySession{},
		tableReadSessions: map[string]*querySession{},
		activeRows:        map[*sql.Rows]context.CancelFunc{},
	}
}

func (s *server) handleLine(line string) (response, bool) {
	var req request
	if err := json.Unmarshal([]byte(line), &req); err != nil {
		return errorResponse(nil, err, "", ""), false
	}
	if len(req.ID) == 0 {
		req.ID = json.RawMessage("1")
	}
	result, shutdown, err := s.dispatch(req.Method, req.Params)
	if err != nil {
		return errorResponse(req.ID, err, req.Method, ""), false
	}
	return response{JSONRPC: "2.0", ID: req.ID, Result: result}, shutdown
}

func (s *server) dispatch(method string, params map[string]json.RawMessage) (any, bool, error) {
	if oracleMethodMayReadLOB(method) {
		if err := s.ensureLegacyOracleLOBFetch(); err != nil {
			return nil, false, err
		}
	}
	switch method {
	case "handshake":
		return map[string]any{
			"protocolVersion":      protocolVersion,
			"agentProtocolVersion": protocolVersion,
			"capabilities":         []string{"connect", "test_connection", "metadata", "query", "transaction", "ddl", "native_job_legacy_v1", "exact_metadata_identity_v1"},
		}, false, nil
	case "connect":
		var cp connectParams
		if err := decodeParams(params, &cp); err != nil {
			return nil, false, err
		}
		return map[string]bool{"ok": true}, false, s.connect(cp)
	case "test_connection":
		var cp connectParams
		if err := decodeParams(params, &cp); err != nil {
			return nil, false, err
		}
		db, err := openAndPingDB(cp, 5*time.Second)
		if err != nil {
			return nil, false, err
		}
		defer db.Close()
		return map[string]bool{"ok": true}, false, nil
	case "validate_connection":
		if s.db == nil {
			return nil, false, errors.New("not connected")
		}
		return map[string]bool{"ok": true}, false, pingDB(s.db, 5*time.Second)
	case "connection_info":
		result, err := s.connectionInfo()
		return result, false, err
	case "list_databases":
		result, err := s.listDatabases()
		return result, false, err
	case "list_schemas":
		result, err := s.listSchemas(stringSliceParam(params, "visible_schemas"))
		return result, false, err
	case "list_tables":
		schema := stringParam(params, "schema")
		result, err := s.listTables(schema, metadataListConstraintsFromParams(params))
		return result, false, err
	case "list_objects":
		schema := stringParam(params, "schema")
		exact, err := exactMetadataReadParam(params)
		if err != nil {
			return nil, false, err
		}
		constraints := metadataListConstraintsFromParams(params)
		constraints.Exact = exact
		result, err := s.listObjects(schema, constraints)
		return result, false, err
	case "completion_assistant_search_v1":
		var request completionAssistantRequest
		if err := decodeParams(params, &request); err != nil {
			return nil, false, err
		}
		result, err := s.completionAssistantSearch(request)
		return result, false, err
	case "get_columns":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		result, err := s.getColumnsWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "get_table_comment":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		result, err := s.getTableCommentWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "get_object_source":
		schema := stringParam(params, "schema")
		name := stringParam(params, "name")
		objectType := stringParam(params, "object_type")
		exact, err := exactMetadataReadParam(params)
		if err != nil {
			return nil, false, err
		}
		if exact && strings.EqualFold(objectType, "TRIGGER") {
			source, err := s.getExactTriggerSource(schema, name)
			return source, false, err
		}
		source, err := s.getObjectSource(schema, name, objectType)
		if err == nil && boolParam(params, "include_source_origin") {
			switch strings.ToUpper(objectType) {
			case "PROCEDURE", "FUNCTION", "PACKAGE", "PACKAGE_BODY":
				source["source_origin"] = "oracle-dictionary"
			}
		}
		return source, false, err
	case "get_table_ddl":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		objectType := stringParam(params, "object_type")
		ddl, err := s.getTableDDLWithIdentity(schema, table, objectType, boolParam(params, "portable"), boolParam(params, "exact_identity"))
		return ddl, false, err
	case "pl_debug_probe":
		return s.plDebugProbe(), false, nil
	case "pl_debug_inspect":
		result, err := s.plDebugInspect(params)
		return result, false, err
	case "pl_debug_resolve_target":
		preview, err := s.previewNativeTarget(params)
		return preview, false, err
	case "execute_query":
		var opts queryOptions
		if err := decodeParams(params, &opts); err != nil {
			return nil, false, err
		}
		result, err := s.executeQuery(opts)
		return result, false, err
	case "execute_query_page":
		var opts queryOptions
		if err := decodeParams(params, &opts); err != nil {
			return nil, false, err
		}
		result, err := s.executeQueryPage(opts, intParam(params, "pageSize"))
		return result, false, err
	case "fetch_query_page":
		result, err := s.fetchQueryPage(stringParam(params, "sessionId"), intParam(params, "pageSize"))
		return result, false, err
	case "close_query_session":
		return s.closeQuerySession(stringParam(params, "sessionId")), false, nil
	case "start_table_read":
		var opts queryOptions
		if err := decodeParams(params, &opts); err != nil {
			return nil, false, err
		}
		result, err := s.startTableRead(opts, intParam(params, "pageSize"))
		return result, false, err
	case "fetch_table_read_page":
		result, err := s.fetchTableReadPage(stringParam(params, "sessionId"), intParam(params, "pageSize"))
		return result, false, err
	case "close_table_read_session":
		return s.closeTableReadSession(stringParam(params, "sessionId")), false, nil
	case "list_indexes":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		result, err := s.listIndexesWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "list_foreign_keys":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		result, err := s.listForeignKeysWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "list_constraints":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		result, err := s.listConstraintsWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "list_triggers":
		schema := stringParam(params, "schema")
		table := stringParam(params, "table")
		exact, err := exactMetadataReadParam(params)
		if err != nil {
			return nil, false, err
		}
		if exact {
			result, err := s.listExactTriggers(schema, table)
			return result, false, err
		}
		result, err := s.listTriggersWithIdentity(schema, table, boolParam(params, "exact_identity"))
		return result, false, err
	case "get_explain_info":
		sqlText := stringParam(params, "sql")
		plan, err := s.getExplainInfo(
			sqlText,
			stringParam(params, "database"),
			stringParam(params, "schema"),
			intParam(params, "timeoutSecs"),
		)
		return map[string]any{"plan": plan, "has_actual_stats": false}, false, err
	case "execute_transaction":
		result, err := s.executeTransaction(params)
		return result, false, err
	case "begin_manual_transaction":
		return map[string]bool{"ok": true}, false, s.beginManualTransaction(stringParam(params, "schema"))
	case "prepare_legacy_job":
		var request legacyJobPrepareRequest
		if err := decodeParams(params, &request); err != nil {
			return legacyJobResult{Phase: "not_started", Reason: "native_job_request_invalid"}, false, nil
		}
		return s.prepareLegacyJob(request), false, nil
	case "commit_legacy_job", "rollback_legacy_job":
		return s.finishLegacyJob(stringParam(params, "operationId"), stringParam(params, "jobId"), method == "commit_legacy_job"), false, nil
	case "legacy_job_status":
		return s.legacyJobStatus(stringParam(params, "operationId")), false, nil
	case "commit_manual_transaction":
		return map[string]bool{"ok": true}, false, s.commitManualTransaction()
	case "rollback_manual_transaction":
		return map[string]bool{"ok": true}, false, s.rollbackManualTransaction()
	case "disconnect":
		return map[string]bool{"ok": true}, false, s.disconnect()
	case "shutdown":
		_ = s.disconnect()
		return map[string]bool{"ok": true}, true, nil
	default:
		return nil, false, fmt.Errorf("unknown method: %s", method)
	}
}

func (s *server) connect(params connectParams) error {
	if s.plAllocation != nil {
		ctx, cancel := context.WithTimeout(context.Background(), plDebugOpenTimeout)
		defer cancel()
		s.params = params
		db, conn, err := s.plAllocation.open(ctx)
		if err != nil {
			return err
		}
		s.db, s.plConnection = db, conn
		return nil
	}
	_ = s.disconnect()
	db, err := openConfiguredSessionDB(params, 15*time.Second)
	if err != nil {
		return err
	}
	majorVersion, versionKnown := oracleServerMajorVersion(db, 15*time.Second)
	s.db = db
	s.params = params
	s.legacyLOBFetchDeferred = shouldUseLegacyOracleLOBFetch(params, majorVersion, versionKnown)
	return nil
}

func openConfiguredSessionDB(params connectParams, timeout time.Duration) (*sql.DB, error) {
	db, err := openAndPingDB(params, timeout)
	if err != nil {
		return nil, err
	}
	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()
	if _, err := db.ExecContext(ctx, "ALTER SESSION SET NLS_LANGUAGE='AMERICAN'"); err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

func oracleServerMajorVersion(db *sql.DB, timeout time.Duration) (int, bool) {
	if timeout > oracleDatabaseVersionProbeTimeout {
		timeout = oracleDatabaseVersionProbeTimeout
	}
	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()
	if majorVersion, ok := oracleServerMajorVersionFromDBConn(ctx, db); ok {
		return majorVersion, true
	}
	for _, query := range oracleDatabaseVersionQueries {
		var version string
		err := db.QueryRowContext(ctx, query).Scan(&version)
		if err == nil {
			if majorVersion, ok := parseOracleMajorVersion(version); ok {
				return majorVersion, true
			}
		}
	}
	return 0, false
}

func oracleServerMajorVersionFromDBConn(ctx context.Context, db *sql.DB) (int, bool) {
	conn, err := db.Conn(ctx)
	if err != nil {
		return 0, false
	}
	defer conn.Close()
	var majorVersion int
	var versionKnown bool
	if err := conn.Raw(func(driverConn any) error {
		majorVersion, versionKnown = oracleServerMajorVersionFromDriverConn(driverConn)
		return nil
	}); err != nil {
		return 0, false
	}
	return majorVersion, versionKnown
}

func oracleServerMajorVersionFromDriverConn(driverConn any) (int, bool) {
	if guarded, ok := driverConn.(*oracleQueryConnection); ok {
		driverConn = guarded.oracleThinConnection
	}
	conn, ok := driverConn.(*go_ora.Connection)
	if !ok {
		return 0, false
	}
	return parseOracleAuthVersionNumber(conn.SessionProperties["AUTH_VERSION_NO"])
}

func parseOracleAuthVersionNumber(value string) (int, bool) {
	encoded, err := strconv.ParseUint(strings.TrimSpace(value), 10, 32)
	if err != nil {
		return 0, false
	}
	majorVersion := int(encoded >> 24)
	return majorVersion, majorVersion > 0
}

func parseOracleMajorVersion(version string) (int, bool) {
	match := oracleVersionNumberRegexp.FindStringSubmatch(strings.TrimSpace(version))
	if len(match) != 2 {
		return 0, false
	}
	value, err := strconv.Atoi(match[1])
	return value, err == nil && value > 0
}

func shouldUseLegacyOracleLOBFetch(params connectParams, majorVersion int, versionKnown bool) bool {
	return versionKnown && majorVersion <= oracleLegacyLOBMaxMajorVersion && !hasOracleLOBFetchOption(params)
}

func oracleMethodMayReadLOB(method string) bool {
	switch method {
	case "get_table_ddl", "execute_query", "execute_query_page", "start_table_read", "execute_transaction":
		return true
	default:
		return false
	}
}

func (s *server) ensureLegacyOracleLOBFetch() error {
	if !s.legacyLOBFetchDeferred {
		return nil
	}
	effectiveParams := withOracleLOBFetchPost(s.params)
	if effectiveParams == s.params {
		s.legacyLOBFetchDeferred = false
		return nil
	}
	db, err := openConfiguredSessionDB(effectiveParams, 15*time.Second)
	if err != nil {
		return err
	}
	oldDB := s.db
	s.db = db
	s.params = effectiveParams
	s.legacyLOBFetchDeferred = false
	if oldDB != nil {
		_ = oldDB.Close()
	}
	return nil
}

func hasOracleLOBFetchOption(params connectParams) bool {
	connectionString := strings.TrimSpace(params.ConnectionString)
	if strings.HasPrefix(strings.ToLower(connectionString), "oracle://") {
		parsed, err := url.Parse(connectionString)
		return err == nil && hasURLValueKey(parsed.Query(), "LOB FETCH")
	}
	values, err := url.ParseQuery(params.URLParams)
	return err == nil && hasURLValueKey(values, "LOB FETCH")
}

func hasURLValueKey(values url.Values, target string) bool {
	for key := range values {
		if strings.EqualFold(strings.TrimSpace(key), target) {
			return true
		}
	}
	return false
}

func withOracleLOBFetchPost(params connectParams) connectParams {
	connectionString := strings.TrimSpace(params.ConnectionString)
	if strings.HasPrefix(strings.ToLower(connectionString), "oracle://") {
		parsed, err := url.Parse(connectionString)
		if err != nil {
			return params
		}
		values := parsed.Query()
		values.Set("LOB FETCH", "POST")
		parsed.RawQuery = values.Encode()
		params.ConnectionString = parsed.String()
		return params
	}
	values, err := url.ParseQuery(params.URLParams)
	if err != nil {
		return params
	}
	values.Set("LOB FETCH", "POST")
	params.URLParams = values.Encode()
	return params
}

func (s *server) disconnect() error {
	if s.plDebuggeeConnection != nil {
		_ = s.plDebuggeeConnection.Close()
		s.plDebuggeeConnection = nil
	}
	if s.plDebuggeeDB != nil {
		_ = s.plDebuggeeDB.Close()
		s.plDebuggeeDB = nil
	}
	s.plDebuggeeTx = nil
	if s.plConnection != nil {
		_ = s.plConnection.Close()
		s.plConnection = nil
	}
	s.closeAllQuerySessions()
	_ = s.rollbackManualTransactionQuiet()
	jobErr := s.closeLegacyJob()
	s.dbmsOutput.reset()
	s.legacyLOBFetchDeferred = false
	if s.db == nil {
		return jobErr
	}
	err := s.db.Close()
	s.db = nil
	if jobErr != nil {
		return jobErr
	}
	return err
}

func (s *server) beginManualTransaction(schema string) error {
	if s.manualTx != nil {
		return errors.New("manual transaction already open")
	}
	db, err := s.requireDB()
	if err != nil {
		return err
	}
	// Hold one exclusive physical connection so DML/SELECT/schema stay on the
	// same Oracle session for the life of the interactive transaction.
	conn, err := db.Conn(context.Background())
	if err != nil {
		return fmt.Errorf("reserve connection for manual transaction: %w", err)
	}
	if strings.TrimSpace(schema) != "" {
		if _, err := conn.ExecContext(context.Background(), "ALTER SESSION SET CURRENT_SCHEMA = "+quoteIdentifier(schema)); err != nil {
			_ = conn.Close()
			return err
		}
	}
	tx, err := conn.BeginTx(context.Background(), nil)
	if err != nil {
		_ = conn.Close()
		return fmt.Errorf("begin manual transaction: %w", err)
	}
	s.manualConn = conn
	s.manualTx = tx
	return nil
}

func (s *server) commitManualTransaction() error {
	if s.manualTx == nil {
		return errors.New("no manual transaction open")
	}
	err := s.manualTx.Commit()
	s.clearManualTransaction()
	return err
}

func (s *server) rollbackManualTransaction() error {
	if s.manualTx == nil {
		return errors.New("no manual transaction open")
	}
	err := s.manualTx.Rollback()
	s.clearManualTransaction()
	return err
}

func (s *server) rollbackManualTransactionQuiet() error {
	if s.manualTx == nil {
		return nil
	}
	err := s.manualTx.Rollback()
	s.clearManualTransaction()
	return err
}

func (s *server) clearManualTransaction() {
	s.manualTx = nil
	if s.manualConn != nil {
		_ = s.manualConn.Close()
		s.manualConn = nil
	}
}

func (s *server) hasManualTransaction() bool {
	return s.manualTx != nil
}

func openDB(params connectParams) (*sql.DB, error) {
	return openDBWithStringConverter(params, nil)
}

func openDBWithStringConverter(params connectParams, stringConverter converters.IStringConverter) (*sql.DB, error) {
	if usesOCIProfile(params) {
		// OCI 由 Oracle 客户端负责字符集转换(NLS_LANG 在进程环境里生效),
		// go-ora 的 string converter 机制不适用于它。
		return openOCIDB(params)
	}
	dsn, err := buildDSNForConnect(params)
	if err != nil {
		return nil, err
	}
	connector := go_ora.NewConnector(dsn)
	if stringConverter != nil {
		go_ora.SetStringConverter(connector, stringConverter, nil)
	}
	db := sql.OpenDB(oracleQueryConnector{connector})
	db.SetMaxOpenConns(4)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(30 * time.Minute)
	return db, nil
}

func openAndPingDB(params connectParams, timeout time.Duration) (*sql.DB, error) {
	db, err := openDB(params)
	if err != nil {
		return nil, err
	}
	if err := pingDB(db, timeout); err != nil {
		db.Close()
		if usesOCIProfile(params) {
			// OCI 的字符集由客户端决定,重试 go-ora 的转换器没有意义。
			return nil, err
		}
		stringConverter, ok := oracleStringConverterForUnsupportedCharsetError(err)
		if !ok {
			return nil, err
		}
		// Retry only charsets with explicit converters. Guessing a converter
		// can make the connection succeed while silently corrupting text.
		db, err = openDBWithStringConverter(params, stringConverter)
		if err != nil {
			return nil, err
		}
		if retryErr := pingDB(db, timeout); retryErr != nil {
			db.Close()
			return nil, retryErr
		}
	}
	return db, nil
}

func pingDB(db *sql.DB, timeout time.Duration) error {
	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()
	return db.PingContext(ctx)
}

func oracleStringConverterForUnsupportedCharsetError(err error) (converters.IStringConverter, bool) {
	charsetID, ok := unsupportedOracleServerCharsetID(err)
	if !ok {
		return nil, false
	}
	stringConverter, ok := oracleStringConverters[charsetID]
	return stringConverter, ok
}

func unsupportedOracleServerCharsetID(err error) (int, bool) {
	if err == nil {
		return 0, false
	}
	match := oracleUnsupportedServerCharsetRegexp.FindStringSubmatch(err.Error())
	if len(match) != 2 {
		return 0, false
	}
	charsetID, parseErr := strconv.Atoi(match[1])
	if parseErr != nil {
		return 0, false
	}
	return charsetID, true
}

type oracleGB18030Converter struct{}

func (oracleGB18030Converter) Encode(input string) []byte {
	output, _, err := transform.String(simplifiedchinese.GB18030.NewEncoder(), input)
	if err != nil {
		return []byte(input)
	}
	return []byte(output)
}

func (oracleGB18030Converter) Decode(input []byte) string {
	output, _, err := transform.Bytes(simplifiedchinese.GB18030.NewDecoder(), input)
	if err != nil {
		return string(input)
	}
	return string(output)
}

func (oracleGB18030Converter) GetLangID() int {
	return oracleCharsetZHS32GB18030
}

func (oracleGB18030Converter) Clone() converters.IStringConverter {
	return oracleGB18030Converter{}
}

func buildDSN(params connectParams) string {
	connectionString := strings.TrimSpace(params.ConnectionString)
	if strings.HasPrefix(strings.ToLower(connectionString), "oracle://") {
		parsed, err := url.Parse(connectionString)
		if err != nil {
			return connectionString
		}
		values := parsed.Query()
		setOracleDefaultPrefetchRows(values)
		parsed.RawQuery = values.Encode()
		return parsed.String()
	}
	username := params.Username
	options := parseURLParams(params.URLParams)
	setOracleDefaultPrefetchRowsMap(options)
	if params.SysDBA {
		options["AUTH TYPE"] = "SYSDBA"
	}

	if jdbc := parseOracleJDBCURL(connectionString); jdbc.Kind != "" {
		if jdbc.Descriptor != "" {
			return buildGoOraJDBC(username, params.Password, jdbc.Descriptor, options)
		}
		host := jdbc.Host
		port := jdbc.Port
		if port == 0 {
			port = 1521
		}
		if jdbc.Kind == "sid" {
			options["SID"] = jdbc.Database
			return buildGoOraURL(host, port, "", username, params.Password, options)
		}
		return buildGoOraURL(host, port, jdbc.Database, username, params.Password, options)
	}

	service := oracleConnectionDatabaseName(params.Database)
	port := params.Port
	if port == 0 {
		port = 1521
	}
	return buildGoOraURL(params.Host, port, service, username, params.Password, options)
}

func setOracleDefaultPrefetchRows(values url.Values) {
	if hasURLValueKey(values, "PREFETCH_ROWS") {
		return
	}
	values.Set("PREFETCH_ROWS", oracleDefaultPrefetchRows)
}

func setOracleDefaultPrefetchRowsMap(options map[string]string) {
	for key := range options {
		if strings.EqualFold(strings.TrimSpace(key), "PREFETCH_ROWS") {
			return
		}
	}
	options["PREFETCH_ROWS"] = oracleDefaultPrefetchRows
}

func oracleConnectionDatabaseName(database string) string {
	database = strings.TrimSpace(database)
	if strings.HasPrefix(strings.ToUpper(database), "SYSDBA:") {
		return strings.TrimSpace(database[len("SYSDBA:"):])
	}
	return database
}

func buildGoOraJDBC(user, password, connStr string, options map[string]string) string {
	if options == nil {
		options = make(map[string]string)
	}
	options["connStr"] = connStr
	return buildGoOraURL("", 0, "", user, password, options)
}

func buildGoOraURL(server string, port int, service, user, password string, options map[string]string) string {
	// go-ora v2.9.0 uses path escaping for user/password, leaving ':' unescaped.
	// Userinfo escaping keeps bastion usernames such as 9008888:reader intact
	// without changing their authentication semantics.
	ret := fmt.Sprintf(
		"oracle://%s@%s/%s",
		url.UserPassword(user, password).String(),
		net.JoinHostPort(server, strconv.Itoa(port)),
		url.PathEscape(service),
	)
	if options != nil {
		ret += "?"
		for key, val := range options {
			val = strings.TrimSpace(val)
			for _, temp := range strings.Split(val, ",") {
				temp = strings.TrimSpace(temp)
				if strings.ToUpper(key) == "SERVER" {
					ret += fmt.Sprintf("%s=%s&", key, temp)
				} else {
					ret += fmt.Sprintf("%s=%s&", key, url.QueryEscape(temp))
				}
			}
		}
		ret = strings.TrimRight(ret, "&")
	}
	return ret
}

type jdbcURLInfo struct {
	Kind       string
	Host       string
	Port       int
	Database   string
	Descriptor string
}

var (
	oracleJDBCServiceRegexp = regexp.MustCompile(`(?i)^jdbc:oracle:thin:@//([^/:]+):([0-9]+)/([^?]+)`)
	oracleJDBCSIDRegexp     = regexp.MustCompile(`(?i)^jdbc:oracle:thin:@([^/:]+):([0-9]+):([^?]+)`)
	oracleJDBCLegacyRegexp  = regexp.MustCompile(`(?i)^jdbc:oracle:thin:@([^/:]+):([0-9]+)/([^?]+)`)
)

func parseOracleJDBCURL(value string) jdbcURLInfo {
	value = strings.TrimSpace(value)
	lower := strings.ToLower(value)
	prefix := ""
	for _, candidate := range []string{"jdbc:oracle:thin:@", "jdbc:oracle:oci8:@", "jdbc:oracle:oci:@"} {
		if strings.HasPrefix(lower, candidate) {
			prefix = candidate
			break
		}
	}
	if prefix == "" {
		return jdbcURLInfo{}
	}
	descriptor := strings.TrimSpace(value[len(prefix):])
	if strings.HasPrefix(descriptor, "(") {
		return jdbcURLInfo{Kind: "descriptor", Descriptor: descriptor}
	}
	if alias := oracleTnsAliasName(descriptor); alias != "" {
		return jdbcURLInfo{Kind: "tns", Database: alias}
	}
	// 下面的正则按 thin 前缀书写,统一归一后再匹配,因此 oci8 与 thin 共用一套解析。
	normalized := "jdbc:oracle:thin:@" + descriptor
	if match := oracleJDBCServiceRegexp.FindStringSubmatch(normalized); len(match) == 4 {
		return jdbcURLInfo{Kind: "service", Host: match[1], Port: parsePort(match[2]), Database: match[3]}
	}
	if match := oracleJDBCSIDRegexp.FindStringSubmatch(normalized); len(match) == 4 {
		return jdbcURLInfo{Kind: "sid", Host: match[1], Port: parsePort(match[2]), Database: match[3]}
	}
	if match := oracleJDBCLegacyRegexp.FindStringSubmatch(normalized); len(match) == 4 {
		return jdbcURLInfo{Kind: "service", Host: match[1], Port: parsePort(match[2]), Database: match[3]}
	}
	return jdbcURLInfo{}
}

// oracleTnsAliasName 返回 `@` 之后、查询串之前的 TNS 别名;不是别名时返回空串。
//
// 判定与前端 parseOracleTnsConnectionString 保持一致:别名不含描述符括号、
// 网络前缀(//)以及路径/端口分隔符(: / \)。
func oracleTnsAliasName(value string) string {
	alias := value
	if index := strings.IndexByte(alias, '?'); index >= 0 {
		alias = alias[:index]
	}
	alias = strings.TrimSpace(alias)
	if alias == "" || strings.HasPrefix(alias, "(") || strings.HasPrefix(alias, "//") {
		return ""
	}
	if strings.ContainsAny(alias, ":/\\") {
		return ""
	}
	return alias
}

func parsePort(value string) int {
	port, _ := strconv.Atoi(value)
	return port
}

func (s *server) requireDB() (*sql.DB, error) {
	if s.db == nil {
		return nil, errors.New("agent is not connected")
	}
	return s.db, nil
}

func (s *server) listDatabases() ([]databaseInfo, error) {
	rows, err := s.queryRows(oracleListDatabasesSQL, nil)
	if err != nil {
		if isOraclePGALimitError(err) {
			return s.currentSchemaDatabase()
		}
		return nil, err
	}
	defer s.closeRows(rows)
	var result []databaseInfo
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		result = append(result, databaseInfo{Name: name})
	}
	if err := rows.Err(); err != nil {
		if isOraclePGALimitError(err) {
			return s.currentSchemaDatabase()
		}
		return nil, err
	}
	return emptyIfNil(result), nil
}

func isOraclePGALimitError(err error) bool {
	return err != nil && strings.Contains(strings.ToUpper(err.Error()), "ORA-04036")
}

func (s *server) currentSchemaDatabase() ([]databaseInfo, error) {
	schema, err := s.currentSchema()
	if err != nil {
		return nil, err
	}
	if strings.TrimSpace(schema) == "" {
		return []databaseInfo{}, nil
	}
	return []databaseInfo{{Name: schema}}, nil
}

func (s *server) listSchemas(visibleSchemas []string) ([]string, error) {
	if visibleSchemas != nil && len(visibleSchemas) == 0 {
		return []string{}, nil
	}
	sqlText, args := oracleListSchemasSQLWithVisibleSchemas(visibleSchemas)
	rows, err := s.queryRows(sqlText, args)
	if err != nil {
		if isOraclePGALimitError(err) {
			databases, fallbackErr := s.currentSchemaDatabase()
			if fallbackErr != nil {
				return nil, fallbackErr
			}
			result := make([]string, 0, len(databases))
			for _, database := range databases {
				result = append(result, database.Name)
			}
			return emptyIfNil(result), nil
		}
		return nil, err
	}
	defer s.closeRows(rows)
	var result []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		result = append(result, name)
	}
	if err := rows.Err(); err != nil {
		if isOraclePGALimitError(err) {
			databases, fallbackErr := s.currentSchemaDatabase()
			if fallbackErr != nil {
				return nil, fallbackErr
			}
			result = result[:0]
			for _, database := range databases {
				result = append(result, database.Name)
			}
			return emptyIfNil(result), nil
		}
		return nil, err
	}
	return emptyIfNil(result), nil
}

func (s *server) listDatabasesFiltered(visibleSchemas []string) ([]databaseInfo, error) {
	if visibleSchemas == nil {
		return s.listDatabases()
	}
	sqlText, args := oracleListDatabasesSQLWithVisibleSchemas(visibleSchemas)
	rows, err := s.queryRows(sqlText, args)
	if err != nil {
		if isOraclePGALimitError(err) {
			return s.currentSchemaDatabase()
		}
		return nil, err
	}
	defer s.closeRows(rows)
	var result []databaseInfo
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, err
		}
		result = append(result, databaseInfo{Name: name})
	}
	if err := rows.Err(); err != nil {
		if isOraclePGALimitError(err) {
			return s.currentSchemaDatabase()
		}
		return nil, err
	}
	return emptyIfNil(result), nil
}

func oracleListDatabasesSQLWithVisibleSchemas(visibleSchemas []string) (string, []any) {
	return oracleListSQLWithVisibleSchemas(oracleListDatabasesSQL, visibleSchemas)
}

func oracleListSchemasSQLWithVisibleSchemas(visibleSchemas []string) (string, []any) {
	return oracleListSQLWithVisibleSchemas(oracleListSchemasSQL, visibleSchemas)
}

func oracleListSQLWithVisibleSchemas(baseSQL string, visibleSchemas []string) (string, []any) {
	if len(visibleSchemas) == 0 {
		return baseSQL, nil
	}
	placeholders := make([]string, 0, len(visibleSchemas))
	args := make([]any, 0, len(visibleSchemas))
	for i, schema := range visibleSchemas {
		placeholders = append(placeholders, fmt.Sprintf(":%d", i+1))
		args = append(args, schema)
	}
	sqlText := strings.Replace(
		baseSQL,
		"\nORDER BY CASE",
		"\n  AND username IN ("+strings.Join(placeholders, ",")+")\nORDER BY CASE",
		1,
	)
	return sqlText, args
}

func (s *server) currentSchema() (string, error) {
	const sqlText = "SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL"
	var schema string
	// CURRENT_SCHEMA is per Oracle session. A manual transaction sets it only on
	// its pinned connection and skips per-statement setSchema, so a pooled
	// session would report the login user's schema and resolve unqualified
	// tables against the wrong owner (#11258).
	if s.manualTx != nil {
		if err := s.manualTx.QueryRow(sqlText).Scan(&schema); err != nil {
			return "", err
		}
		return strings.ToUpper(schema), nil
	}
	// The connection pinned for a statement's DBMS_OUTPUT capture carries the
	// statement's CURRENT_SCHEMA the same way.
	if s.statementConn != nil {
		if err := s.repinPoisonedStatementConn(); err != nil {
			return "", err
		}
		if err := s.statementConn.QueryRowContext(s.statementContext(), sqlText).Scan(&schema); err != nil {
			return "", err
		}
		return strings.ToUpper(schema), nil
	}
	db, err := s.requireDB()
	if err != nil {
		return "", err
	}
	if err := db.QueryRow(sqlText).Scan(&schema); err != nil {
		return "", err
	}
	return strings.ToUpper(schema), nil
}

func (s *server) normalizeSchema(schema string) (string, error) {
	return resolveOracleSchema(schema, s.currentSchema, s.sessionUser)
}

func (s *server) metadataSchema(schema string, exactIdentity bool) (string, error) {
	if !exactIdentity {
		return s.normalizeSchema(schema)
	}
	if schema == "" {
		return "", errors.New("schema is required for exact metadata identity")
	}
	return schema, nil
}

func resolveOracleSchema(schema string, currentSchema, sessionUser func() (string, error)) (string, error) {
	if schema = strings.TrimSpace(schema); schema != "" {
		return strings.ToUpper(schema), nil
	}
	if current, err := currentSchema(); err == nil && strings.TrimSpace(current) != "" {
		return strings.ToUpper(strings.TrimSpace(current)), nil
	}
	return sessionUser()
}

func (s *server) sessionUser() (string, error) {
	username, err := s.rawSessionUser()
	return strings.ToUpper(username), err
}

func (s *server) rawSessionUser() (string, error) {
	db, err := s.requireDB()
	if err != nil {
		return "", err
	}
	var username string
	if err := db.QueryRow("SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL").Scan(&username); err != nil {
		return "", err
	}
	return username, nil
}

func (s *server) schemaIsSessionUser(schema string) bool {
	username, err := s.sessionUser()
	return err == nil && strings.EqualFold(schema, username)
}

func (s *server) metadataSchemaIsSessionUser(schema string, exactIdentity bool) bool {
	if !exactIdentity {
		return s.schemaIsSessionUser(schema)
	}
	username, err := s.rawSessionUser()
	return err == nil && schema == username
}

type oracleMetadataListQuery struct {
	SQL  string
	Args []any
}

func metadataListConstraintsFromParams(params map[string]json.RawMessage) metadataListConstraints {
	objectTypes := stringSliceParam(params, "object_types")
	if len(objectTypes) == 0 {
		objectTypes = stringSliceParam(params, "objectTypes")
	}
	limit := intParam(params, "limit")
	offset := intParam(params, "offset")
	if limit < 0 {
		limit = 0
	}
	if offset < 0 {
		offset = 0
	}
	return metadataListConstraints{
		Filter:        stringParam(params, "filter"),
		Limit:         limit,
		Offset:        offset,
		ObjectTypes:   objectTypes,
		ExactIdentity: boolParam(params, "exact_identity"),
	}
}

func oracleListTablesQuery(schema string, constraints metadataListConstraints) oracleMetadataListQuery {
	return oracleConstrainedMetadataListQuery(
		oracleListTablesBaseSQL,
		"OBJECT_NAME, TABLE_TYPE, COMMENTS",
		"TABLE_TYPE",
		oracleListTablesOrderSQL,
		[]any{schema, schema, schema},
		constraints,
	)
}

func oracleListSessionUserTablesQuery(constraints metadataListConstraints) oracleMetadataListQuery {
	return oracleConstrainedMetadataListQuery(
		oracleListTablesSessionUserBaseSQL,
		"OBJECT_NAME, TABLE_TYPE, COMMENTS",
		"TABLE_TYPE",
		oracleListTablesOrderSQL,
		nil,
		constraints,
	)
}

func oracleListObjectsQuery(schema string, constraints metadataListConstraints) oracleMetadataListQuery {
	return oracleListObjectsQueryWithTypeOwner(schema, schema, constraints)
}

func oracleListObjectsQueryWithTypeOwner(schema, typeOwner string, constraints metadataListConstraints) oracleMetadataListQuery {
	baseSQL := oracleListObjectsBaseSQL + `
UNION ALL
SELECT o.OBJECT_NAME,
       CASE o.OBJECT_TYPE WHEN 'TYPE BODY' THEN 'TYPE_BODY' ELSE o.OBJECT_TYPE END AS OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(4000)) AS COMMENTS,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_SCHEMA,
       CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM ALL_OBJECTS o
WHERE o.OWNER = :4 AND o.OBJECT_TYPE IN ('TYPE', 'TYPE BODY')`
	if constraints.Exact {
		baseSQL = strings.Replace(baseSQL, "'PACKAGE BODY', 'INDEX')", "'PACKAGE BODY', 'INDEX', 'TRIGGER')", 1)
	}
	return oracleConstrainedMetadataListQuery(
		baseSQL,
		"OBJECT_NAME, OBJECT_TYPE, COMMENTS, PARENT_SCHEMA, PARENT_NAME",
		"OBJECT_TYPE",
		oracleListObjectsOrderSQL,
		[]any{schema, schema, schema, typeOwner},
		constraints,
	)
}

func oracleListSessionUserObjectsQuery(constraints metadataListConstraints) oracleMetadataListQuery {
	baseSQL := oracleListObjectsSessionUserBaseSQL
	if constraints.Exact {
		baseSQL = strings.Replace(baseSQL, "'PACKAGE BODY', 'INDEX', 'TYPE', 'TYPE BODY')", "'PACKAGE BODY', 'INDEX', 'TYPE', 'TYPE BODY', 'TRIGGER')", 1)
	}
	return oracleConstrainedMetadataListQuery(
		baseSQL,
		"OBJECT_NAME, OBJECT_TYPE, COMMENTS, PARENT_SCHEMA, PARENT_NAME",
		"OBJECT_TYPE",
		oracleListObjectsOrderSQL,
		nil,
		constraints,
	)
}

func oracleConstrainedMetadataListQuery(baseSQL, selectList, typeColumn, orderSQL string, baseArgs []any, constraints metadataListConstraints) oracleMetadataListQuery {
	args := append([]any{}, baseArgs...)
	where := make([]string, 0, 2)
	if filter := strings.TrimSpace(constraints.Filter); filter != "" {
		args = append(args, strings.ToUpper(oracleFuzzyLikePattern(filter)))
		where = append(where, fmt.Sprintf("UPPER(OBJECT_NAME) LIKE :%d ESCAPE '\\'", len(args)))
	}
	if objectTypes := normalizedMetadataObjectTypes(constraints.ObjectTypes); len(objectTypes) > 0 {
		placeholders := make([]string, 0, len(objectTypes))
		for _, objectType := range objectTypes {
			args = append(args, objectType)
			placeholders = append(placeholders, fmt.Sprintf(":%d", len(args)))
		}
		where = append(where, fmt.Sprintf("%s IN (%s)", typeColumn, strings.Join(placeholders, ",")))
	}

	sqlText := fmt.Sprintf("SELECT %s\nFROM (\n%s\n)", selectList, baseSQL)
	if len(where) > 0 {
		sqlText += "\nWHERE " + strings.Join(where, " AND ")
	}
	sqlText += "\n" + orderSQL

	if constraints.Limit > 0 {
		args = append(args, constraints.Offset+constraints.Limit)
		maxRowParam := len(args)
		args = append(args, constraints.Offset)
		offsetParam := len(args)
		sqlText = fmt.Sprintf(
			"SELECT %s\nFROM (\n  SELECT DBX_Q.*, ROWNUM AS DBX_RN\n  FROM (\n%s\n  ) DBX_Q\n  WHERE ROWNUM <= :%d\n)\nWHERE DBX_RN > :%d",
			selectList,
			sqlText,
			maxRowParam,
			offsetParam,
		)
	} else if constraints.Offset > 0 {
		args = append(args, constraints.Offset)
		offsetParam := len(args)
		sqlText = fmt.Sprintf(
			"SELECT %s\nFROM (\n  SELECT DBX_Q.*, ROWNUM AS DBX_RN\n  FROM (\n%s\n  ) DBX_Q\n)\nWHERE DBX_RN > :%d",
			selectList,
			sqlText,
			offsetParam,
		)
	}

	return oracleMetadataListQuery{SQL: sqlText, Args: args}
}

func normalizedMetadataObjectTypes(values []string) []string {
	seen := map[string]bool{}
	result := make([]string, 0, len(values))
	for _, value := range values {
		normalized := strings.ToUpper(strings.TrimSpace(value))
		normalized = strings.ReplaceAll(normalized, "-", "_")
		normalized = strings.ReplaceAll(normalized, " ", "_")
		if normalized == "" || seen[normalized] {
			continue
		}
		seen[normalized] = true
		result = append(result, normalized)
	}
	sort.Strings(result)
	return result
}

func oracleFuzzyLikePattern(value string) string {
	value = strings.TrimSpace(value)
	if value == "" {
		return "%%"
	}
	var builder strings.Builder
	builder.Grow(len(value)*2 + 2)
	builder.WriteByte('%')
	for _, ch := range value {
		switch ch {
		case '\\', '%', '_':
			builder.WriteByte('\\')
		}
		builder.WriteRune(ch)
		builder.WriteByte('%')
	}
	return builder.String()
}

func (s *server) listTables(schema string, constraints metadataListConstraints) ([]tableInfo, error) {
	schema, err := s.metadataSchema(schema, constraints.ExactIdentity)
	if err != nil {
		return nil, err
	}
	query := oracleListTablesQuery(schema, constraints)
	if s.metadataSchemaIsSessionUser(schema, constraints.ExactIdentity) {
		query = oracleListSessionUserTablesQuery(constraints)
	}
	rows, err := s.queryRows(query.SQL, query.Args)
	if err != nil {
		if isOraclePGALimitError(err) && !constraints.ExactIdentity {
			return []tableInfo{}, nil
		}
		return nil, err
	}
	defer s.closeRows(rows)
	var result []tableInfo
	for rows.Next() {
		var item tableInfo
		if err := rows.Scan(&item.Name, &item.TableType, &item.Comment); err != nil {
			return nil, err
		}
		result = append(result, item)
	}
	return emptyIfNil(result), rows.Err()
}

func (s *server) listObjects(schema string, constraints metadataListConstraints) ([]objectInfo, error) {
	rawTypeOwner := schema
	schema, err := s.metadataSchema(schema, constraints.Exact || constraints.ExactIdentity)
	if err != nil {
		return nil, err
	}
	if rawTypeOwner == "" {
		rawTypeOwner = schema
	}
	query := oracleListObjectsQueryWithTypeOwner(schema, rawTypeOwner, constraints)
	useSessionUser := !constraints.Exact && rawTypeOwner == schema && s.metadataSchemaIsSessionUser(schema, constraints.ExactIdentity)
	if useSessionUser {
		query = oracleListSessionUserObjectsQuery(constraints)
	}
	rows, err := s.queryRows(query.SQL, query.Args)
	if err != nil {
		if isOraclePGALimitError(err) && !constraints.Exact && !constraints.ExactIdentity && !metadataIncludesIndexes(constraints.ObjectTypes) {
			return []objectInfo{}, nil
		}
		return nil, err
	}
	defer s.closeRows(rows)
	var result []objectInfo
	for rows.Next() {
		var item objectInfo
		if err := rows.Scan(&item.Name, &item.ObjectType, &item.Comment, &item.ParentSchema, &item.ParentName); err != nil {
			return nil, err
		}
		item.Schema = schema
		if strings.EqualFold(item.ObjectType, "TYPE") || strings.EqualFold(item.ObjectType, "TYPE_BODY") {
			item.Schema = rawTypeOwner
		}
		result = append(result, item)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	s.closeRows(rows)
	if constraints.Exact {
		s.enrichExactTriggerObjects(result, schema)
	}
	return emptyIfNil(result), nil
}

func metadataIncludesIndexes(objectTypes []string) bool {
	for _, objectType := range normalizedMetadataObjectTypes(objectTypes) {
		if objectType == "INDEX" {
			return true
		}
	}
	return false
}

func (s *server) completionAssistantSearch(request completionAssistantRequest) (completionAssistantResponse, error) {
	limit := request.MaxResults
	if limit <= 0 {
		limit = 100
	}
	if limit > 1000 {
		limit = 1000
	}
	preferredSchema := strings.ToUpper(strings.TrimSpace(request.Schema))
	if preferredSchema == "" {
		var err error
		preferredSchema, err = s.currentSchema()
		if err != nil {
			return completionAssistantResponse{}, err
		}
	}

	if completionRequestHasTableLikeKind(request.ObjectKinds) {
		return s.completionAssistantTables(request, preferredSchema, limit)
	}
	if completionRequestHasRoutineKind(request.ObjectKinds) {
		return s.completionAssistantRoutines(request, preferredSchema, limit)
	}
	return completionAssistantResponse{Candidates: []completionAssistantCandidate{}}, nil
}

func completionRequestHasTableLikeKind(kinds []string) bool {
	if len(kinds) == 0 {
		return true
	}
	for _, kind := range kinds {
		switch strings.ToLower(strings.TrimSpace(kind)) {
		case "table", "view":
			return true
		}
	}
	return false
}

func completionRequestHasRoutineKind(kinds []string) bool {
	for _, kind := range kinds {
		switch strings.ToLower(strings.TrimSpace(kind)) {
		case "routine", "procedure", "function", "sequence":
			return true
		}
	}
	return false
}

func (s *server) completionAssistantTables(request completionAssistantRequest, preferredSchema string, limit int) (completionAssistantResponse, error) {
	scanLimit := limit * 3
	if scanLimit < limit+1 {
		scanLimit = limit + 1
	}
	if scanLimit > 1000 {
		scanLimit = 1000
	}
	query := oracleCompletionTablesQuery(request, preferredSchema, scanLimit+1)
	rows, err := s.queryRows(query.SQL, query.Args)
	if err != nil {
		return completionAssistantResponse{}, err
	}

	type tableRow struct {
		owner, name, objectType string
		targetOwner, targetName sql.NullString
	}
	rawRows := make([]tableRow, 0, scanLimit+1)
	targets := make([]oracleCompletionSynonymTarget, 0)
	for rows.Next() {
		var row tableRow
		if err := rows.Scan(&row.owner, &row.name, &row.objectType, &row.targetOwner, &row.targetName); err != nil {
			s.closeRows(rows)
			return completionAssistantResponse{}, err
		}
		rawRows = append(rawRows, row)
		if strings.EqualFold(row.objectType, "SYNONYM") && row.targetOwner.Valid && row.targetName.Valid {
			targets = append(targets, oracleCompletionSynonymTarget{Owner: row.targetOwner.String, Name: row.targetName.String})
		}
	}
	rowsErr := rows.Err()
	closeErr := s.closeRows(rows)
	if rowsErr != nil {
		return completionAssistantResponse{}, rowsErr
	}
	if closeErr != nil {
		return completionAssistantResponse{}, closeErr
	}

	validTargets, err := s.oracleCompletionValidSynonymTargets(targets, oracleCompletionTableObjectTypes(request.ObjectKinds))
	if err != nil {
		return completionAssistantResponse{}, err
	}
	candidates := make([]completionAssistantCandidate, 0, limit+1)
	for _, row := range rawRows {
		if strings.EqualFold(row.objectType, "SYNONYM") {
			if !row.targetOwner.Valid || !row.targetName.Valid {
				continue
			}
			if _, ok := validTargets[oracleCompletionSynonymTargetKey(row.targetOwner.String, row.targetName.String)]; !ok {
				continue
			}
		}
		kind := "table"
		if strings.EqualFold(row.objectType, "VIEW") {
			kind = "view"
		}
		candidates = append(candidates, completionAssistantCandidate{
			Name:     row.name,
			Kind:     kind,
			Database: stringPointer(request.Database),
			Schema:   stringPointer(row.owner),
			DataType: stringPointer(row.objectType),
		})
	}
	incomplete := len(candidates) > limit || len(rawRows) > scanLimit
	if incomplete {
		if len(candidates) > limit {
			candidates = candidates[:limit]
		}
	}
	return completionAssistantResponse{Candidates: candidates, Incomplete: incomplete}, nil
}

type oracleCompletionSynonymTarget struct {
	Owner string
	Name  string
}

func oracleCompletionSynonymTargetKey(owner, name string) string {
	return owner + "\x00" + name
}

func (s *server) oracleCompletionValidSynonymTargets(targets []oracleCompletionSynonymTarget, objectTypes []string) (map[string]struct{}, error) {
	unique := make([]oracleCompletionSynonymTarget, 0, len(targets))
	seen := make(map[string]struct{}, len(targets))
	for _, target := range targets {
		key := oracleCompletionSynonymTargetKey(target.Owner, target.Name)
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		unique = append(unique, target)
	}

	valid := make(map[string]struct{}, len(unique))
	const batchSize = 100
	for start := 0; start < len(unique); start += batchSize {
		end := start + batchSize
		if end > len(unique) {
			end = len(unique)
		}
		query := oracleCompletionSynonymTargetsQuery(unique[start:end], objectTypes)
		rows, err := s.queryRows(query.SQL, query.Args)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var owner, name string
			if err := rows.Scan(&owner, &name); err != nil {
				s.closeRows(rows)
				return nil, err
			}
			valid[oracleCompletionSynonymTargetKey(owner, name)] = struct{}{}
		}
		rowsErr := rows.Err()
		closeErr := s.closeRows(rows)
		if rowsErr != nil {
			return nil, rowsErr
		}
		if closeErr != nil {
			return nil, closeErr
		}
	}
	return valid, nil
}

func (s *server) completionAssistantRoutines(request completionAssistantRequest, preferredSchema string, limit int) (completionAssistantResponse, error) {
	if strings.TrimSpace(request.ParentName) != "" {
		return s.completionAssistantPackageRoutines(request, preferredSchema, limit)
	}

	query := oracleCompletionRoutinesQuery(request, preferredSchema, limit+1)
	rows, err := s.queryRows(query.SQL, query.Args)
	if err != nil {
		return completionAssistantResponse{}, err
	}
	defer s.closeRows(rows)

	candidates := make([]completionAssistantCandidate, 0, limit+1)
	for rows.Next() {
		var owner, name, objectType string
		var parentName sql.NullString
		if err := rows.Scan(&owner, &name, &objectType, &parentName); err != nil {
			return completionAssistantResponse{}, err
		}
		kind := strings.ToLower(objectType)
		if objectType == "PACKAGE" {
			kind = "object"
		}
		candidate := completionAssistantCandidate{
			Name:     name,
			Kind:     kind,
			Database: stringPointer(request.Database),
			Schema:   stringPointer(owner),
			DataType: stringPointer(objectType),
		}
		if parentName.Valid {
			candidate.ParentSchema = stringPointer(owner)
			candidate.ParentName = stringPointer(parentName.String)
		}
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return completionAssistantResponse{}, err
	}
	incomplete := len(candidates) > limit
	if incomplete {
		candidates = candidates[:limit]
	}
	return completionAssistantResponse{Candidates: candidates, Incomplete: incomplete}, nil
}

type oraclePackageRoutineRow struct {
	owner         string
	parentName    string
	name          string
	objectID      int64
	subprogramID  int64
	position      sql.NullInt64
	sequence      sql.NullInt64
	argumentName  sql.NullString
	inOut         sql.NullString
	dataType      sql.NullString
	typeOwner     sql.NullString
	typeName      sql.NullString
	typeSubname   sql.NullString
	dataLength    sql.NullInt64
	dataPrecision sql.NullInt64
	dataScale     sql.NullInt64
}

type oraclePackageRoutine struct {
	owner        string
	parentName   string
	name         string
	isFunction   bool
	returnType   string
	argumentList []string
}

func (s *server) completionAssistantPackageRoutines(request completionAssistantRequest, preferredSchema string, limit int) (completionAssistantResponse, error) {
	query := oracleCompletionPackageRoutinesQuery(request, preferredSchema)
	rows, err := s.queryRows(query.SQL, query.Args)
	if err != nil {
		return completionAssistantResponse{}, err
	}
	defer s.closeRows(rows)

	packageRows := make([]oraclePackageRoutineRow, 0)
	for rows.Next() {
		var row oraclePackageRoutineRow
		if err := rows.Scan(
			&row.owner,
			&row.parentName,
			&row.name,
			&row.objectID,
			&row.subprogramID,
			&row.position,
			&row.sequence,
			&row.argumentName,
			&row.inOut,
			&row.dataType,
			&row.typeOwner,
			&row.typeName,
			&row.typeSubname,
			&row.dataLength,
			&row.dataPrecision,
			&row.dataScale,
		); err != nil {
			return completionAssistantResponse{}, err
		}
		packageRows = append(packageRows, row)
	}
	if err := rows.Err(); err != nil {
		return completionAssistantResponse{}, err
	}
	return oracleCompletionPackageCandidates(packageRows, request.Database, limit), nil
}

func oracleCompletionPackageCandidates(rows []oraclePackageRoutineRow, database string, limit int) completionAssistantResponse {
	routines := make([]oraclePackageRoutine, 0)
	routineIndexes := make(map[string]int)
	for _, row := range rows {
		key := fmt.Sprintf("%s\x00%d\x00%d", row.owner, row.objectID, row.subprogramID)
		index, found := routineIndexes[key]
		if !found {
			index = len(routines)
			routineIndexes[key] = index
			routines = append(routines, oraclePackageRoutine{owner: row.owner, parentName: row.parentName, name: row.name})
		}
		routine := &routines[index]
		if !row.position.Valid {
			continue
		}
		dataType := oracleCompletionArgumentDataType(row)
		if row.position.Int64 == 0 {
			routine.isFunction = true
			routine.returnType = dataType
			continue
		}
		argument := oracleCompletionArgumentSignature(row, dataType)
		if argument != "" {
			routine.argumentList = append(routine.argumentList, argument)
		}
	}

	incomplete := len(routines) > limit
	if incomplete {
		routines = routines[:limit]
	}
	candidates := make([]completionAssistantCandidate, 0, len(routines))
	for _, routine := range routines {
		kind := "procedure"
		if routine.isFunction {
			kind = "function"
		}
		candidate := completionAssistantCandidate{
			Name:         routine.name,
			Kind:         kind,
			Database:     stringPointer(database),
			Schema:       stringPointer(routine.owner),
			ParentSchema: stringPointer(routine.owner),
			ParentName:   stringPointer(routine.parentName),
			Signature:    stringPointer(strings.Join(routine.argumentList, ", ")),
		}
		if routine.returnType != "" {
			candidate.DataType = stringPointer(routine.returnType)
		}
		candidates = append(candidates, candidate)
	}
	return completionAssistantResponse{Candidates: candidates, Incomplete: incomplete}
}

func oracleCompletionArgumentSignature(row oraclePackageRoutineRow, dataType string) string {
	parts := make([]string, 0, 3)
	if row.argumentName.Valid {
		parts = append(parts, strings.TrimSpace(row.argumentName.String))
	}
	if row.inOut.Valid {
		direction := strings.Join(strings.Fields(strings.ReplaceAll(row.inOut.String, "/", " ")), " ")
		if direction != "" {
			parts = append(parts, direction)
		}
	}
	if dataType != "" {
		parts = append(parts, dataType)
	}
	return strings.Join(parts, " ")
}

func oracleCompletionArgumentDataType(row oraclePackageRoutineRow) string {
	typeParts := make([]string, 0, 3)
	if row.typeOwner.Valid && strings.TrimSpace(row.typeOwner.String) != "" {
		typeParts = append(typeParts, strings.TrimSpace(row.typeOwner.String))
	}
	if row.typeName.Valid && strings.TrimSpace(row.typeName.String) != "" {
		typeParts = append(typeParts, strings.TrimSpace(row.typeName.String))
	}
	if row.typeSubname.Valid && strings.TrimSpace(row.typeSubname.String) != "" {
		typeParts = append(typeParts, strings.TrimSpace(row.typeSubname.String))
	}
	if len(typeParts) > 0 {
		return strings.Join(typeParts, ".")
	}

	dataType := strings.TrimSpace(row.dataType.String)
	switch strings.ToUpper(dataType) {
	case "NUMBER", "NUMERIC", "DECIMAL":
		if row.dataPrecision.Valid {
			if row.dataScale.Valid {
				return fmt.Sprintf("%s(%d, %d)", dataType, row.dataPrecision.Int64, row.dataScale.Int64)
			}
			return fmt.Sprintf("%s(%d)", dataType, row.dataPrecision.Int64)
		}
	case "CHAR", "VARCHAR", "VARCHAR2", "NCHAR", "NVARCHAR2", "RAW":
		if row.dataLength.Valid && row.dataLength.Int64 > 0 {
			return fmt.Sprintf("%s(%d)", dataType, row.dataLength.Int64)
		}
	}
	return dataType
}

func stringPointer(value string) *string {
	if value == "" {
		return nil
	}
	result := value
	return &result
}

func oracleCompletionTableObjectTypes(kinds []string) []string {
	objectTypes := make([]string, 0, 2)
	for _, kind := range kinds {
		switch strings.ToLower(strings.TrimSpace(kind)) {
		case "table":
			objectTypes = append(objectTypes, "'TABLE'")
		case "view":
			objectTypes = append(objectTypes, "'VIEW'")
		}
	}
	if len(objectTypes) == 0 {
		objectTypes = []string{"'TABLE'", "'VIEW'"}
	}
	return objectTypes
}

func oracleCompletionTablesQuery(request completionAssistantRequest, preferredSchema string, limit int) oracleMetadataListQuery {
	objectTypes := oracleCompletionTableObjectTypes(request.ObjectKinds)
	pattern := oracleCompletionLikePattern(request.Mask, request.MatchMode)
	args := make([]any, 0, 7)
	args = append(args, pattern)
	objectNamePredicate := oracleCompletionNamePredicate("o.OBJECT_NAME", len(args), request.CaseSensitive)
	objectOwnerPredicate := ""
	synonymOwnerPredicate := ""
	owner := ""
	if !request.GlobalSearch {
		owner = strings.ToUpper(strings.TrimSpace(request.ParentSchema))
		if owner == "" {
			owner = strings.ToUpper(strings.TrimSpace(request.Schema))
		}
		if owner == "" {
			owner = preferredSchema
		}
		args = append(args, owner)
		objectOwnerPredicate = fmt.Sprintf(" AND o.OWNER = :%d", len(args))
	}
	args = append(args, pattern)
	synonymNamePredicate := oracleCompletionNamePredicate("s.SYNONYM_NAME", len(args), request.CaseSensitive)
	if owner != "" {
		args = append(args, owner)
		synonymOwnerPredicate = fmt.Sprintf(" AND s.OWNER = :%d", len(args))
	}
	args = append(args, preferredSchema)
	preferredParam := len(args)
	args = append(args, strings.TrimSpace(request.Mask))
	exactParam := len(args)
	args = append(args, limit)
	limitParam := len(args)

	baseSQL := fmt.Sprintf(`SELECT o.OWNER,
       o.OBJECT_NAME,
       o.OBJECT_TYPE,
       CAST(NULL AS VARCHAR2(128)) AS TARGET_OWNER,
       CAST(NULL AS VARCHAR2(128)) AS TARGET_NAME
  FROM ALL_OBJECTS o
  WHERE o.OBJECT_TYPE IN (%s)
    AND %s%s
  UNION ALL
  SELECT s.OWNER,
       s.SYNONYM_NAME AS OBJECT_NAME,
       'SYNONYM' AS OBJECT_TYPE,
       s.TABLE_OWNER AS TARGET_OWNER,
       s.TABLE_NAME AS TARGET_NAME
  FROM ALL_SYNONYMS s
	WHERE s.DB_LINK IS NULL
	    AND %s%s`, strings.Join(objectTypes, ","), objectNamePredicate, objectOwnerPredicate, synonymNamePredicate, synonymOwnerPredicate)
	unionSQL := fmt.Sprintf("SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, TARGET_OWNER, TARGET_NAME\nFROM (\n%s\n)", baseSQL)
	orderedSQL := oracleCompletionOrderedSQL(unionSQL, "OBJECT_NAME", "OBJECT_TYPE", preferredParam, exactParam)
	return oracleMetadataListQuery{
		SQL:  fmt.Sprintf("SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, TARGET_OWNER, TARGET_NAME FROM (\n%s\n) WHERE ROWNUM <= :%d", orderedSQL, limitParam),
		Args: args,
	}
}

func oracleCompletionSynonymTargetsQuery(targets []oracleCompletionSynonymTarget, objectTypes []string) oracleMetadataListQuery {
	args := make([]any, 0, len(targets)*2)
	predicates := make([]string, 0, len(targets))
	for _, target := range targets {
		args = append(args, target.Owner, target.Name)
		predicates = append(predicates, fmt.Sprintf("(o.OWNER = :%d AND o.OBJECT_NAME = :%d)", len(args)-1, len(args)))
	}
	return oracleMetadataListQuery{
		SQL:  fmt.Sprintf("SELECT DISTINCT o.OWNER, o.OBJECT_NAME\nFROM ALL_OBJECTS o\nWHERE o.OBJECT_TYPE IN (%s)\n  AND (%s)", strings.Join(objectTypes, ","), strings.Join(predicates, " OR ")),
		Args: args,
	}
}

func oracleCompletionRoutineObjectTypes(kinds []string) []string {
	if len(kinds) == 0 {
		return []string{"'FUNCTION'", "'PROCEDURE'", "'PACKAGE'", "'SEQUENCE'"}
	}
	hasRoutine := false
	hasProc := false
	hasFunc := false
	hasSeq := false
	for _, kind := range kinds {
		switch strings.ToLower(strings.TrimSpace(kind)) {
		case "routine":
			hasRoutine = true
		case "procedure":
			hasProc = true
		case "function":
			hasFunc = true
		case "sequence":
			hasSeq = true
		}
	}
	types := make([]string, 0, 4)
	if hasRoutine {
		types = append(types, "'FUNCTION'", "'PROCEDURE'", "'PACKAGE'")
	} else {
		if hasProc {
			types = append(types, "'PROCEDURE'")
		}
		if hasFunc {
			types = append(types, "'FUNCTION'")
		}
	}
	if hasSeq {
		types = append(types, "'SEQUENCE'")
	}
	if len(types) == 0 {
		return []string{"'FUNCTION'", "'PROCEDURE'", "'PACKAGE'"}
	}
	return types
}

func oracleCompletionRoutinesQuery(request completionAssistantRequest, preferredSchema string, limit int) oracleMetadataListQuery {
	pattern := oracleCompletionLikePattern(request.Mask, request.MatchMode)
	args := make([]any, 0, 5)
	objectTypes := oracleCompletionRoutineObjectTypes(request.ObjectKinds)
	baseSQL := fmt.Sprintf(`
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, CAST(NULL AS VARCHAR2(128)) AS PARENT_NAME
FROM ALL_OBJECTS o
WHERE o.OBJECT_TYPE IN (%s)`, strings.Join(objectTypes, ", "))
	args = append(args, pattern)
	nameParam := len(args)

	ownerPredicate := ""
	if !request.GlobalSearch {
		owner := strings.ToUpper(strings.TrimSpace(request.ParentSchema))
		if owner == "" {
			owner = strings.ToUpper(strings.TrimSpace(request.Schema))
		}
		if owner == "" {
			owner = preferredSchema
		}
		args = append(args, owner)
		ownerPredicate = fmt.Sprintf(" AND OWNER = :%d", len(args))
	}
	args = append(args, preferredSchema)
	preferredParam := len(args)
	args = append(args, strings.TrimSpace(request.Mask))
	exactParam := len(args)
	args = append(args, limit)
	limitParam := len(args)

	filteredSQL := fmt.Sprintf("SELECT DISTINCT OWNER, OBJECT_NAME, OBJECT_TYPE, PARENT_NAME\nFROM (\n%s\n)\nWHERE %s%s", baseSQL, oracleCompletionNamePredicate("OBJECT_NAME", nameParam, request.CaseSensitive), ownerPredicate)
	orderedSQL := oracleCompletionOrderedSQL(filteredSQL, "OBJECT_NAME", "OBJECT_TYPE", preferredParam, exactParam)
	return oracleMetadataListQuery{
		SQL:  fmt.Sprintf("SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, PARENT_NAME FROM (\n%s\n) WHERE ROWNUM <= :%d", orderedSQL, limitParam),
		Args: args,
	}
}

func oracleCompletionPackageRoutinesQuery(request completionAssistantRequest, preferredSchema string) oracleMetadataListQuery {
	owner := strings.TrimSpace(request.ParentSchema)
	if owner == "" {
		owner = strings.TrimSpace(request.Schema)
	}
	if owner == "" {
		owner = preferredSchema
	}
	parentName := strings.TrimSpace(request.ParentName)
	pattern := oracleCompletionLikePattern(request.Mask, request.MatchMode)
	args := []any{owner, parentName, pattern}
	return oracleMetadataListQuery{
		SQL: fmt.Sprintf(`SELECT p.OWNER,
       p.OBJECT_NAME AS PARENT_NAME,
       p.PROCEDURE_NAME AS OBJECT_NAME,
       p.OBJECT_ID,
       p.SUBPROGRAM_ID,
       a.POSITION,
       a.SEQUENCE,
       a.ARGUMENT_NAME,
       a.IN_OUT,
       a.DATA_TYPE,
       a.TYPE_OWNER,
       a.TYPE_NAME,
       a.TYPE_SUBNAME,
       a.DATA_LENGTH,
       a.DATA_PRECISION,
       a.DATA_SCALE
FROM ALL_PROCEDURES p
LEFT JOIN ALL_ARGUMENTS a
  ON a.OWNER = p.OWNER
 AND a.OBJECT_ID = p.OBJECT_ID
 AND a.SUBPROGRAM_ID = p.SUBPROGRAM_ID
 AND a.DATA_LEVEL = 0
WHERE p.OBJECT_TYPE = 'PACKAGE'
  AND p.PROCEDURE_NAME IS NOT NULL
  AND p.OWNER = :1
  AND p.OBJECT_NAME = :2
  AND %s
ORDER BY p.PROCEDURE_NAME,
         p.SUBPROGRAM_ID,
         NVL(a.SEQUENCE, 0)`, oracleCompletionNamePredicate("p.PROCEDURE_NAME", 3, request.CaseSensitive)),
		Args: args,
	}
}

func oracleCompletionNamePredicate(column string, parameter int, caseSensitive bool) string {
	if caseSensitive {
		return fmt.Sprintf("%s LIKE :%d ESCAPE '\\'", column, parameter)
	}
	return fmt.Sprintf("UPPER(%s) LIKE UPPER(:%d) ESCAPE '\\'", column, parameter)
}

func oracleCompletionLikePattern(mask, matchMode string) string {
	escaped := strings.NewReplacer("\\", "\\\\", "%", "\\%", "_", "\\_").Replace(strings.TrimSpace(mask))
	if strings.EqualFold(strings.TrimSpace(matchMode), "contains") {
		return "%" + escaped + "%"
	}
	return escaped + "%"
}

func oracleCompletionOrderedSQL(baseSQL, nameColumn, typeColumn string, preferredParam, exactParam int) string {
	return fmt.Sprintf(`%s
ORDER BY CASE
           WHEN OWNER = :%d THEN 0
           WHEN OWNER = 'PUBLIC' THEN 1
           WHEN OWNER IN ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','XDB','MDSYS','CTXSYS','WMSYS') THEN 3
           ELSE 2
         END,
         CASE WHEN UPPER(%s) = UPPER(:%d) THEN 0 ELSE 1 END,
         CASE %s WHEN 'TABLE' THEN 0 WHEN 'VIEW' THEN 1 WHEN 'FUNCTION' THEN 0 WHEN 'PROCEDURE' THEN 1 WHEN 'PACKAGE' THEN 2 WHEN 'SYNONYM' THEN 3 ELSE 4 END,
         %s,
         OWNER`, baseSQL, preferredParam, nameColumn, exactParam, typeColumn, nameColumn)
}

func oracleObjectNameCandidates(name string) (string, string, bool) {
	exact := strings.TrimSpace(name)
	uppercase := strings.ToUpper(exact)
	return exact, uppercase, exact != uppercase
}

func (s *server) getColumns(schema, table string) ([]columnInfo, error) {
	return s.getColumnsWithIdentity(schema, table, false)
}

func (s *server) getColumnsWithIdentity(schema, table string, exactIdentity bool) ([]columnInfo, error) {
	schema, err := s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return nil, err
	}
	if exactIdentity {
		return s.getColumnsByName(schema, table)
	}
	exact, uppercase, hasUppercaseFallback := oracleObjectNameCandidates(table)
	result, err := s.getColumnsByName(schema, exact)
	if err != nil || len(result) > 0 || !hasUppercaseFallback {
		return result, err
	}
	return s.getColumnsByName(schema, uppercase)
}

func (s *server) getColumnsByName(schema, table string) ([]columnInfo, error) {
	rows, err := s.queryRows(`
SELECT c.COLUMN_NAME,
       c.DATA_TYPE,
       c.NULLABLE,
       c.DATA_DEFAULT,
       CASE WHEN pk.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IS_PRIMARY_KEY,
       cc.COMMENTS,
       c.DATA_PRECISION,
       c.DATA_SCALE,
	       c.DATA_LENGTH,
       c.CHAR_LENGTH,
       c.CHAR_USED
FROM ALL_TAB_COLUMNS c
LEFT JOIN (
  SELECT acc.OWNER, acc.TABLE_NAME, acc.COLUMN_NAME
  FROM ALL_CONSTRAINTS ac
  JOIN ALL_CONS_COLUMNS acc ON acc.OWNER = ac.OWNER AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
  WHERE ac.CONSTRAINT_TYPE = 'P'
) pk ON pk.OWNER = c.OWNER AND pk.TABLE_NAME = c.TABLE_NAME AND pk.COLUMN_NAME = c.COLUMN_NAME
LEFT JOIN ALL_COL_COMMENTS cc ON cc.OWNER = c.OWNER AND cc.TABLE_NAME = c.TABLE_NAME AND cc.COLUMN_NAME = c.COLUMN_NAME
WHERE c.OWNER = :1 AND c.TABLE_NAME = :2
ORDER BY c.COLUMN_ID`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	var result []columnInfo
	for rows.Next() {
		var item columnInfo
		var nullable string
		var primary int
		if err := rows.Scan(
			&item.Name,
			&item.DataType,
			&nullable,
			&item.ColumnDefault,
			&primary,
			&item.Comment,
			&item.NumericPrecision,
			&item.NumericScale,
			&item.DataLength,
			&item.CharacterMaximumLength,
			&item.CharacterLengthUnit,
		); err != nil {
			return nil, err
		}
		item.IsNullable = nullable == "Y"
		item.IsPrimaryKey = primary != 0
		item.DataType = oracleColumnTypeDDL(item)
		result = append(result, item)
	}
	return emptyIfNil(result), rows.Err()
}

func (s *server) getTableComment(schema, table string) (*string, error) {
	return s.getTableCommentWithIdentity(schema, table, false)
}

func (s *server) getTableCommentWithIdentity(schema, table string, exactIdentity bool) (*string, error) {
	schema, err := s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return nil, err
	}
	if exactIdentity {
		comment, _, err := s.getTableCommentByName(schema, table)
		return comment, err
	}
	exact, uppercase, hasUppercaseFallback := oracleObjectNameCandidates(table)
	comment, found, err := s.getTableCommentByName(schema, exact)
	if err != nil || found || !hasUppercaseFallback {
		return comment, err
	}
	comment, _, err = s.getTableCommentByName(schema, uppercase)
	return comment, err
}

func (s *server) getTableCommentByName(schema, table string) (*string, bool, error) {
	db, err := s.requireDB()
	if err != nil {
		return nil, false, err
	}
	var comment sql.NullString
	err = db.QueryRow(
		"SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = :1 AND TABLE_NAME = :2",
		schema,
		table,
	).Scan(&comment)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, false, nil
	}
	if err != nil {
		return nil, false, err
	}
	if !comment.Valid {
		return nil, true, nil
	}
	return &comment.String, true, nil
}

func (s *server) loadOracleColumnMeta(schema, table string) ([]oracleColumnMeta, error) {
	schema, err := s.normalizeSchema(schema)
	if err != nil {
		return nil, err
	}
	exact, uppercase, hasUppercaseFallback := oracleObjectNameCandidates(table)
	result, err := s.loadOracleColumnMetaByName(schema, exact)
	if err != nil || len(result) > 0 || !hasUppercaseFallback {
		return result, err
	}
	return s.loadOracleColumnMetaByName(schema, uppercase)
}

func (s *server) loadOracleColumnMetaByName(schema, table string) ([]oracleColumnMeta, error) {
	rows, err := s.queryRows(`
SELECT COLUMN_NAME, DATA_TYPE, DATA_TYPE_OWNER
FROM ALL_TAB_COLUMNS
WHERE OWNER = :1 AND TABLE_NAME = :2
ORDER BY COLUMN_ID`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	var result []oracleColumnMeta
	for rows.Next() {
		var item oracleColumnMeta
		var dataTypeOwner sql.NullString
		if err := rows.Scan(&item.Name, &item.DataType, &dataTypeOwner); err != nil {
			return nil, err
		}
		if dataTypeOwner.Valid {
			item.DataTypeOwner = dataTypeOwner.String
		}
		result = append(result, item)
	}
	return result, rows.Err()
}

func (s *server) listIndexes(schema, table string) ([]indexInfo, error) {
	return s.listIndexesWithIdentity(schema, table, false)
}

func (s *server) listIndexesWithIdentity(schema, table string, exactIdentity bool) ([]indexInfo, error) {
	schema, err := s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return nil, err
	}
	if !exactIdentity {
		table = strings.TrimSpace(table)
	}
	rows, err := s.queryRows(`
SELECT i.INDEX_NAME,
       ic.COLUMN_NAME,
       i.UNIQUENESS,
       CASE WHEN pk.CONSTRAINT_NAME IS NULL THEN 0 ELSE 1 END AS IS_PRIMARY,
       i.INDEX_TYPE,
	       i.PARTITIONED,
       i.VISIBILITY,
       ic.DESCEND,
       ie.COLUMN_EXPRESSION,
       ic.COLUMN_POSITION, i.OWNER
FROM ALL_INDEXES i
JOIN ALL_IND_COLUMNS ic ON ic.INDEX_OWNER = i.OWNER AND ic.INDEX_NAME = i.INDEX_NAME
  AND ic.TABLE_OWNER = i.TABLE_OWNER AND ic.TABLE_NAME = i.TABLE_NAME
LEFT JOIN ALL_IND_EXPRESSIONS ie ON ie.INDEX_OWNER = ic.INDEX_OWNER AND ie.INDEX_NAME = ic.INDEX_NAME
  AND ie.TABLE_OWNER = ic.TABLE_OWNER AND ie.TABLE_NAME = ic.TABLE_NAME AND ie.COLUMN_POSITION = ic.COLUMN_POSITION
LEFT JOIN ALL_CONSTRAINTS pk ON pk.OWNER = i.TABLE_OWNER
  AND pk.TABLE_NAME = i.TABLE_NAME
  AND pk.CONSTRAINT_TYPE = 'P'
  AND pk.INDEX_NAME = i.INDEX_NAME
  AND pk.INDEX_OWNER = i.OWNER
WHERE i.TABLE_OWNER = :1 AND i.TABLE_NAME = :2
ORDER BY i.OWNER, i.INDEX_NAME, ic.COLUMN_POSITION`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)

	byName := map[[2]string]*indexInfo{}
	order := [][2]string{}
	for rows.Next() {
		var name, column, uniqueness, indexType, partitioned, visibility, direction, owner string
		var expression sql.NullString
		var primary int
		var position int
		if err := rows.Scan(&name, &column, &uniqueness, &primary, &indexType, &partitioned, &visibility, &direction, &expression, &position, &owner); err != nil {
			return nil, err
		}
		identity := [2]string{owner, name}
		item := byName[identity]
		if item == nil {
			visible := visibility
			item = &indexInfo{
				Owner:               &owner,
				Name:                name,
				Columns:             []string{},
				IsUnique:            uniqueness == "UNIQUE",
				IsPrimary:           primary != 0,
				IndexType:           &indexType,
				IncludedColumns:     []string{},
				KeyIsExpression:     []bool{},
				KeyDirections:       []string{},
				Visibility:          &visible,
				KeyMetadataComplete: true,
			}
			byName[identity] = item
			order = append(order, identity)
		}
		if position != len(item.Columns)+1 || (direction != "ASC" && direction != "DESC") || (visibility != "VISIBLE" && visibility != "INVISIBLE") || item.Visibility == nil || *item.Visibility != visibility || partitioned != "NO" || (indexType != "NORMAL" && indexType != "FUNCTION-BASED NORMAL") {
			item.KeyMetadataComplete = false
		}
		key := column
		isExpression := expression.Valid && strings.TrimSpace(expression.String) != ""
		if isExpression {
			key = expression.String
		} else if strings.HasPrefix(column, "SYS_NC") && strings.HasSuffix(column, "$") {
			item.KeyMetadataComplete = false
		}
		item.Columns = append(item.Columns, key)
		item.KeyIsExpression = append(item.KeyIsExpression, isExpression)
		item.KeyDirections = append(item.KeyDirections, direction)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	result := make([]indexInfo, 0, len(order))
	for _, name := range order {
		result = append(result, *byName[name])
	}
	return emptyIfNil(result), nil
}

func (s *server) listForeignKeys(schema, table string) ([]foreignKeyInfo, error) {
	return s.listForeignKeysWithIdentity(schema, table, false)
}

func (s *server) listForeignKeysWithIdentity(schema, table string, exactIdentity bool) ([]foreignKeyInfo, error) {
	schema, err := s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return nil, err
	}
	if !exactIdentity {
		table = strings.TrimSpace(table)
	}
	rows, err := s.queryRows(`
SELECT ac.CONSTRAINT_NAME,
       acc.COLUMN_NAME,
       rcc.OWNER AS REF_SCHEMA,
       rcc.TABLE_NAME AS REF_TABLE,
       rcc.COLUMN_NAME AS REF_COLUMN,
       ac.DELETE_RULE
FROM ALL_CONSTRAINTS ac
JOIN ALL_CONS_COLUMNS acc ON acc.OWNER = ac.OWNER AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
JOIN ALL_CONS_COLUMNS rcc ON rcc.OWNER = ac.R_OWNER AND rcc.CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME
  AND rcc.POSITION = acc.POSITION
WHERE ac.OWNER = :1
  AND ac.TABLE_NAME = :2
  AND ac.CONSTRAINT_TYPE = 'R'
ORDER BY ac.CONSTRAINT_NAME, acc.POSITION`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	var result []foreignKeyInfo
	for rows.Next() {
		var item foreignKeyInfo
		if err := rows.Scan(&item.Name, &item.Column, &item.RefSchema, &item.RefTable, &item.RefColumn, &item.OnDelete); err != nil {
			return nil, err
		}
		result = append(result, item)
	}
	return emptyIfNil(result), rows.Err()
}

func oracleConstraintTypeName(kind string) string {
	switch kind {
	case "P":
		return "PRIMARY KEY"
	case "U":
		return "UNIQUE"
	case "C":
		return "CHECK"
	default:
		return kind
	}
}

func oracleSystemNotNullConstraint(kind string, generated sql.NullString, definition string, column, nullable sql.NullString) bool {
	if kind != "C" || !generated.Valid || generated.String != "GENERATED NAME" || !column.Valid || !nullable.Valid || nullable.String != "N" {
		return false
	}
	matches := oracleNotNullConstraintRegexp.FindStringSubmatch(definition)
	if matches == nil {
		return false
	}
	if matches[1] != "" {
		return strings.ReplaceAll(matches[1], `""`, `"`) == column.String
	}
	return strings.EqualFold(matches[2], column.String)
}

// listConstraints returns primary key, unique, and check constraints for a
// table. Oracle represents every NOT NULL column as a system-generated CHECK
// constraint (e.g. "COL" IS NOT NULL); those are excluded here so the result
// only contains constraints a user would recognize as such, matching how
// tools like DBeaver/Navicat present Oracle constraints.
func (s *server) listConstraints(schema, table string) ([]constraintInfo, error) {
	return s.listConstraintsWithIdentity(schema, table, false)
}

func (s *server) listConstraintsWithIdentity(schema, table string, exactIdentity bool) ([]constraintInfo, error) {
	var err error
	if exactIdentity {
		schema, err = s.metadataSchema(schema, true)
	} else {
		schema, err = s.normalizeSchemaForIdentity(schema)
	}
	if err != nil {
		return nil, err
	}
	if !exactIdentity {
		table = strings.TrimSpace(table)
	}
	// SEARCH_CONDITION is a LONG column: Oracle rejects LONG values in WHERE
	// clauses, functions, or ORDER BY (ORA-00932), so it can only appear in
	// the SELECT list here. The NOT-NULL-check exclusion below is therefore
	// applied in Go after scanning, not in SQL.
	rows, err := s.queryRows(`
SELECT ac.CONSTRAINT_NAME,
       ac.CONSTRAINT_TYPE,
       ac.SEARCH_CONDITION,
       ac.GENERATED,
       ac.STATUS,
       ac.DEFERRABLE,
       ac.DEFERRED,
       ac.VALIDATED,
	       ac.RELY,
	       ac.INDEX_OWNER,
	       ac.INDEX_NAME,
       acc.COLUMN_NAME,
       acc.POSITION,
       atc.NULLABLE
FROM ALL_CONSTRAINTS ac
LEFT JOIN ALL_CONS_COLUMNS acc ON acc.OWNER = ac.OWNER AND acc.CONSTRAINT_NAME = ac.CONSTRAINT_NAME
LEFT JOIN ALL_TAB_COLUMNS atc ON atc.OWNER = ac.OWNER AND atc.TABLE_NAME = ac.TABLE_NAME AND atc.COLUMN_NAME = acc.COLUMN_NAME
WHERE ac.OWNER = :1
  AND ac.TABLE_NAME = :2
  AND ac.CONSTRAINT_TYPE IN ('P', 'U', 'C')
ORDER BY ac.CONSTRAINT_NAME, acc.POSITION`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)

	byName := map[string]*constraintInfo{}
	skipped := map[string]bool{}
	order := []string{}
	for rows.Next() {
		var name, kind string
		var condition, generated, status, deferrable, deferred, validated, rely, indexOwner, indexName, column, nullable sql.NullString
		var position sql.NullInt64
		if err := rows.Scan(&name, &kind, &condition, &generated, &status, &deferrable, &deferred, &validated, &rely, &indexOwner, &indexName, &column, &position, &nullable); err != nil {
			return nil, err
		}
		if skipped[name] {
			continue
		}
		item := byName[name]
		if item == nil {
			definition := ""
			if condition.Valid {
				definition = strings.TrimSpace(condition.String)
			}
			if oracleSystemNotNullConstraint(kind, generated, definition, column, nullable) {
				skipped[name] = true
				continue
			}
			item = &constraintInfo{
				Name:              name,
				ConstraintType:    oracleConstraintTypeName(kind),
				Definition:        definition,
				Columns:           []string{},
				RefColumns:        []string{},
				Deferrable:        deferrable.Valid && deferrable.String == "DEFERRABLE",
				InitiallyDeferred: deferred.Valid && deferred.String == "DEFERRED",
				Enabled:           status.Valid && status.String == "ENABLED",
				Valid:             validated.Valid && validated.String == "VALIDATED",
				OracleMetadata: &oracleConstraintMetadata{
					MetadataComplete: kind != "" && status.Valid && validated.Valid && deferrable.Valid && deferred.Valid &&
						(kind != "C" || condition.Valid),
					Owner:          schema,
					ConstraintType: stringPointer(kind),
					Status:         nullableStringPointer(status),
					Validated:      nullableStringPointer(validated),
					Deferrable:     nullableStringPointer(deferrable),
					Deferred:       nullableStringPointer(deferred),
					Rely:           nullableStringPointer(rely),
					IndexOwner:     nullableStringPointer(indexOwner),
					IndexName:      nullableStringPointer(indexName),
				},
			}
			if kind == "U" {
				if status.Valid && status.String == "DISABLED" && !indexOwner.Valid && !indexName.Valid {
					item.OracleMetadata.Index = nil
					item.OracleMetadata.IndexSharing = nil
				} else {
					item.OracleMetadata.Index, item.OracleMetadata.IndexSharing = s.oracleUniqueIndexMetadata(schema, table, indexOwner, indexName)
					if item.OracleMetadata.Index == nil || !item.OracleMetadata.Index.MetadataComplete || item.OracleMetadata.IndexSharing == nil {
						item.OracleMetadata.MetadataComplete = false
					}
				}
			}
			byName[name] = item
			order = append(order, name)
		}
		if column.Valid && column.String != "" {
			if !position.Valid || position.Int64 != int64(len(item.Columns)+1) {
				item.OracleMetadata.MetadataComplete = false
			}
			item.Columns = append(item.Columns, column.String)
		} else if position.Valid {
			item.OracleMetadata.MetadataComplete = false
		}
		if item.OracleMetadata.IndexOwner == nil != (item.OracleMetadata.IndexName == nil) {
			item.OracleMetadata.MetadataComplete = false
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	result := make([]constraintInfo, 0, len(order))
	for _, name := range order {
		result = append(result, *byName[name])
	}
	return emptyIfNil(result), nil
}

func nullableStringPointer(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	result := value.String
	return &result
}

func (s *server) oracleUniqueIndexMetadata(tableOwner, tableName string, indexOwner, indexName sql.NullString) (*oracleConstraintIndexMetadata, *string) {
	if !indexOwner.Valid || !indexName.Valid || indexOwner.String == "" || indexName.String == "" {
		return nil, nil
	}
	rows, err := s.queryRows(`
SELECT i.OWNER, i.TABLE_OWNER, i.TABLE_NAME, i.INDEX_TYPE, i.UNIQUENESS,
	   i.STATUS, i.PARTITIONED, i.VISIBILITY, i.SECONDARY,
	   ic.COLUMN_NAME, ic.DESCEND, ie.COLUMN_EXPRESSION, ic.COLUMN_POSITION
FROM ALL_INDEXES i
JOIN ALL_IND_COLUMNS ic ON ic.INDEX_OWNER = i.OWNER AND ic.INDEX_NAME = i.INDEX_NAME
LEFT JOIN ALL_IND_EXPRESSIONS ie ON ie.INDEX_OWNER = ic.INDEX_OWNER AND ie.INDEX_NAME = ic.INDEX_NAME
	AND ie.TABLE_OWNER = ic.TABLE_OWNER AND ie.TABLE_NAME = ic.TABLE_NAME AND ie.COLUMN_POSITION = ic.COLUMN_POSITION
WHERE i.OWNER = :1 AND i.INDEX_NAME = :2 AND i.TABLE_OWNER = :3 AND i.TABLE_NAME = :4
ORDER BY ic.COLUMN_POSITION`, []any{indexOwner.String, indexName.String, tableOwner, tableName})
	if err != nil {
		return &oracleConstraintIndexMetadata{}, nil
	}
	defer s.closeRows(rows)
	metadata := &oracleConstraintIndexMetadata{
		Owner: indexOwner.String, TableOwner: tableOwner, TableName: tableName,
		Columns: []string{}, Directions: []string{}, KeyIsExpression: []bool{}, IncludedColumns: []string{},
	}
	var sharing *string
	for rows.Next() {
		var owner, actualTableOwner, actualTable, indexType, uniqueness, status, partitioned, visibility sql.NullString
		var secondary, column, descend, expression sql.NullString
		var position sql.NullInt64
		if err := rows.Scan(&owner, &actualTableOwner, &actualTable, &indexType, &uniqueness, &status, &partitioned, &visibility, &secondary, &column, &descend, &expression, &position); err != nil {
			return metadata, nil
		}
		if !owner.Valid || !actualTableOwner.Valid || !actualTable.Valid || !indexType.Valid || !uniqueness.Valid || !status.Valid || !partitioned.Valid || (partitioned.String != "YES" && partitioned.String != "NO") || !visibility.Valid || !position.Valid || position.Int64 != int64(len(metadata.Columns)+1) || !descend.Valid || (descend.String != "ASC" && descend.String != "DESC") || (!column.Valid && !expression.Valid) {
			return metadata, nil
		}
		metadata.Owner, metadata.TableOwner, metadata.TableName = owner.String, actualTableOwner.String, actualTable.String
		metadata.IndexType, metadata.Uniqueness, metadata.Status = indexType.String, uniqueness.String, status.String
		metadata.Visibility, metadata.Secondary = visibility.String, nullableStringPointer(secondary)
		metadata.Partitioned = partitioned.String == "YES"
		metadata.Columns = append(metadata.Columns, column.String)
		metadata.Directions = append(metadata.Directions, descend.String)
		metadata.KeyIsExpression = append(metadata.KeyIsExpression, expression.Valid && strings.TrimSpace(expression.String) != "")
	}
	if err := rows.Err(); err != nil || len(metadata.Columns) == 0 || len(metadata.Directions) != len(metadata.Columns) {
		return metadata, nil
	}
	metadata.MetadataComplete = true
	sharingRows, err := s.queryRows(`
SELECT COUNT(*) FROM ALL_CONSTRAINTS
WHERE OWNER = :1 AND TABLE_NAME = :2 AND INDEX_OWNER = :3 AND INDEX_NAME = :4
	AND CONSTRAINT_TYPE IN ('P', 'U')`, []any{tableOwner, tableName, indexOwner.String, indexName.String})
	if err != nil {
		return metadata, nil
	}
	defer s.closeRows(sharingRows)
	if sharingRows.Next() {
		var count int64
		if err := sharingRows.Scan(&count); err == nil && count == 1 {
			value := "exclusive"
			sharing = &value
		} else if err == nil && count > 1 {
			value := "shared"
			sharing = &value
		}
	}
	return metadata, sharing
}

func (s *server) listTriggers(schema, table string) ([]triggerInfo, error) {
	return s.listTriggersWithIdentity(schema, table, false)
}

func (s *server) listTriggersWithIdentity(schema, table string, exactIdentity bool) ([]triggerInfo, error) {
	schema, err := s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return nil, err
	}
	if !exactIdentity {
		table = strings.TrimSpace(table)
	}
	rows, err := s.queryRows(oracleListTriggersSQL, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	var result []triggerInfo
	var currentName string
	var currentDescription string
	var currentStructured bool
	var source strings.Builder
	flush := func() {
		if len(result) == 0 || currentName == "" {
			return
		}
		if !currentStructured {
			// Keep the declaration so callers can tell the source carries clauses the
			// timing/event/body form cannot round-trip.
			if body := strings.TrimSpace(strings.ReplaceAll(source.String(), "\r\n", "\n")); body != "" {
				result[len(result)-1].Statement = &body
			}
			return
		}
		if body, ok := oracleTriggerBody(source.String(), currentDescription); ok {
			result[len(result)-1].Statement = &body
		}
	}
	for rows.Next() {
		var name, event, timing string
		var description, status, whenClause, referencingNames, lineText sql.NullString
		var line sql.NullInt64
		if err := rows.Scan(&name, &event, &timing, &description, &status, &whenClause, &referencingNames, &line, &lineText); err != nil {
			return nil, err
		}
		if name != currentName {
			flush()
			currentName = name
			currentDescription = description.String
			currentStructured = oracleTriggerIsStructured(description.String, whenClause.String, referencingNames.String)
			source.Reset()
			result = append(result, triggerInfo{Name: name, Event: event, Timing: timing, Enabled: oracleTriggerEnabled(status)})
		}
		if line.Valid && lineText.Valid {
			source.WriteString(lineText.String)
		}
	}
	flush()
	return emptyIfNil(result), rows.Err()
}

var oracleTriggerUnstructuredClauseRegexp = regexp.MustCompile(`(?i)\b(?:FOLLOWS|PRECEDES|COMPOUND|CROSSEDITION)\b`)

// oracleTriggerIsStructured reports whether a trigger is fully described by its timing, event and
// PL/SQL body, so it can be rebuilt with CREATE OR REPLACE without dropping a WHEN condition,
// custom REFERENCING names or FOLLOWS/PRECEDES ordering.
func oracleTriggerIsStructured(description, whenClause, referencingNames string) bool {
	if strings.TrimSpace(whenClause) != "" {
		return false
	}
	if referencing := strings.Join(strings.Fields(strings.ToUpper(referencingNames)), " "); referencing != "" && referencing != "REFERENCING NEW AS NEW OLD AS OLD" {
		return false
	}
	return !oracleTriggerUnstructuredClauseRegexp.MatchString(description)
}

func oracleTriggerEnabled(status sql.NullString) *bool {
	switch strings.ToUpper(strings.TrimSpace(status.String)) {
	case "ENABLED":
		enabled := true
		return &enabled
	case "DISABLED":
		enabled := false
		return &enabled
	}
	return nil
}

func oracleTriggerBody(source, description string) (string, bool) {
	source = strings.ReplaceAll(source, "\r\n", "\n")
	description = strings.ReplaceAll(description, "\r\n", "\n")
	if strings.TrimSpace(source) == "" {
		return "", false
	}

	sourceLines := strings.Split(source, "\n")
	descriptionLines := strings.Split(strings.TrimSpace(description), "\n")
	for len(descriptionLines) > 0 && strings.TrimSpace(descriptionLines[len(descriptionLines)-1]) == "" {
		descriptionLines = descriptionLines[:len(descriptionLines)-1]
	}
	if len(descriptionLines) > 0 && len(sourceLines) >= len(descriptionLines) {
		matches := true
		for index, descriptionLine := range descriptionLines {
			sourceLine := strings.TrimSpace(sourceLines[index])
			if index == 0 && len(sourceLine) >= len("TRIGGER") && strings.EqualFold(sourceLine[:len("TRIGGER")], "TRIGGER") {
				sourceLine = strings.TrimSpace(sourceLine[len("TRIGGER"):])
			}
			if !strings.EqualFold(sourceLine, strings.TrimSpace(descriptionLine)) {
				matches = false
				break
			}
		}
		if matches {
			return strings.TrimSpace(strings.Join(sourceLines[len(descriptionLines):], "\n")), true
		}
	}

	// ALL_SOURCE is still more useful than an empty editor if a database version formats DESCRIPTION differently.
	return strings.TrimSpace(source), true
}

func (s *server) getObjectSource(schema, name, objectType string) (map[string]any, error) {
	upperType := strings.ToUpper(objectType)
	if upperType == "TYPE" || upperType == "TYPE_BODY" || upperType == "TYPE BODY" {
		if schema == "" {
			var err error
			schema, err = s.normalizeSchemaForIdentity(schema)
			if err != nil {
				return nil, err
			}
		}
		if upperType != "TYPE" {
			upperType = "TYPE BODY"
		}
		source, found, sourceErr := s.loadObjectSourceText(schema, name, upperType)
		if sourceErr != nil || !found || strings.TrimSpace(source) == "" {
			db, dbErr := s.requireDB()
			if dbErr != nil {
				if sourceErr != nil {
					return nil, errors.Join(sourceErr, dbErr)
				}
				return nil, dbErr
			}
			ddlType := "TYPE"
			if upperType == "TYPE BODY" {
				ddlType = "TYPE_BODY"
			}
			var ddl sql.NullString
			ddlErr := db.QueryRow("SELECT DBMS_METADATA.GET_DDL(:1, :2, :3) FROM DUAL", ddlType, name, schema).Scan(&ddl)
			if ddlErr == nil && ddl.Valid && strings.TrimSpace(ddl.String) != "" {
				source = ddl.String
			} else {
				if ddlErr == nil {
					ddlErr = fmt.Errorf("empty source returned")
				}
				if sourceErr != nil {
					return nil, errors.Join(sourceErr, ddlErr)
				}
				return nil, ddlErr
			}
		}
		return map[string]any{"name": name, "object_type": objectType, "schema": schema, "source": source, "editable": false}, nil
	}
	var err error
	schema, err = s.normalizeSchemaForIdentity(schema)
	if err != nil {
		return nil, err
	}
	if upperType == "VIEW" {
		source, err := s.getViewSource(schema, name)
		if err != nil {
			return nil, err
		}
		return map[string]any{"name": name, "object_type": objectType, "schema": schema, "source": source}, nil
	}
	if upperType == "MATERIALIZED_VIEW" || upperType == "SEQUENCE" || upperType == "SYNONYM" {
		return s.getMetadataObjectSource(schema, name, upperType)
	}

	// Unquoted Oracle identifiers are stored uppercase; quoted mixed-case names must stay exact.
	// Try caller-provided identity first, then uppercase fallback (same pattern as column metadata).
	dictionaryType := upperType
	if dictionaryType == "PACKAGE_BODY" {
		dictionaryType = "PACKAGE BODY"
	}
	for _, candidate := range oracleObjectIdentityNameCandidates(name) {
		source, found, queryErr := s.loadObjectSourceText(schema, candidate, dictionaryType)
		if queryErr != nil {
			return nil, queryErr
		}
		if found {
			return map[string]any{"name": candidate, "object_type": objectType, "schema": schema, "source": source}, nil
		}
	}
	return map[string]any{"name": name, "object_type": objectType, "schema": schema, "source": ""}, nil
}

func (s *server) getMetadataObjectSource(schema, name, objectType string) (map[string]any, error) {
	db, err := s.requireDB()
	if err != nil {
		return nil, err
	}
	var lastErr error
	for _, candidate := range oracleObjectIdentityNameCandidates(name) {
		var source string
		lastErr = db.QueryRow("SELECT DBMS_METADATA.GET_DDL(:1, :2, :3) FROM DUAL", objectType, candidate, schema).Scan(&source)
		if lastErr == nil {
			return map[string]any{"name": candidate, "object_type": objectType, "schema": schema, "source": source}, nil
		}
	}
	return nil, lastErr
}

func (s *server) loadObjectSourceText(schema, name, objectType string) (string, bool, error) {
	source, found, err := s.loadAggregatedObjectSourceText(schema, name, objectType)
	if err == nil {
		return source, found, nil
	}
	return s.loadObjectSourceTextRows(schema, name, objectType)
}

func (s *server) loadAggregatedObjectSourceText(schema, name, objectType string) (string, bool, error) {
	rows, err := s.queryRows(`
SELECT DBMS_XMLGEN.CONVERT(
  XMLAGG(XMLELEMENT(E, TEXT) ORDER BY LINE).EXTRACT('//text()').GETCLOBVAL(),
  1
)
FROM ALL_SOURCE
WHERE OWNER = :1 AND NAME = :2 AND TYPE = :3`, []any{schema, name, objectType})
	if err != nil {
		return "", false, err
	}
	defer s.closeRows(rows)
	if !rows.Next() {
		return "", false, rows.Err()
	}
	var source sql.NullString
	if err := rows.Scan(&source); err != nil {
		return "", false, err
	}
	if err := rows.Err(); err != nil {
		return "", false, err
	}
	return source.String, source.Valid, nil
}

func (s *server) loadObjectSourceTextRows(schema, name, objectType string) (string, bool, error) {
	rows, err := s.queryRows(`
SELECT TEXT
FROM ALL_SOURCE
WHERE OWNER = :1 AND NAME = :2 AND TYPE = :3
ORDER BY LINE`, []any{schema, name, objectType})
	if err != nil {
		return "", false, err
	}
	defer s.closeRows(rows)
	var builder strings.Builder
	var anyLine bool
	for rows.Next() {
		var line string
		if err := rows.Scan(&line); err != nil {
			return "", false, err
		}
		builder.WriteString(line)
		anyLine = true
	}
	if err := rows.Err(); err != nil {
		return "", false, err
	}
	return builder.String(), anyLine, nil
}

// oracleObjectIdentityNameCandidates returns ALL_SOURCE name variants.
// Exact form first (quoted mixed-case), then uppercase for unquoted identifiers.
func oracleObjectIdentityNameCandidates(name string) []string {
	trimmed := strings.TrimSpace(name)
	if trimmed == "" {
		return nil
	}
	upper := strings.ToUpper(trimmed)
	if trimmed == upper {
		return []string{upper}
	}
	return []string{trimmed, upper}
}

// normalizeSchemaForIdentity preserves mixed-case schema owners (quoted identities)
// and uppercases already-uppercase / empty-resolved session schemas.
func (s *server) normalizeSchemaForIdentity(schema string) (string, error) {
	trimmed := strings.TrimSpace(schema)
	if trimmed != "" && trimmed != strings.ToUpper(trimmed) {
		// Mixed or lower case from a quoted click-site identity — keep exact OWNER.
		return trimmed, nil
	}
	return s.normalizeSchema(schema)
}

func (s *server) getTableDDL(schema, table, objectType string) (string, error) {
	return s.getTableDDLWithOptions(schema, table, objectType, false)
}

func (s *server) getTableDDLWithOptions(schema, table, objectType string, portable bool) (string, error) {
	return s.getTableDDLWithIdentity(schema, table, objectType, portable, false)
}

func (s *server) getTableDDLWithIdentity(schema, table, objectType string, portable, exactIdentity bool) (string, error) {
	var err error
	schema, err = s.metadataSchema(schema, exactIdentity)
	if err != nil {
		return "", err
	}
	db, err := s.requireDB()
	if err != nil {
		return "", err
	}
	objectType, table, err = s.resolveDDLObjectWithIdentity(schema, table, objectType, exactIdentity)
	if err != nil {
		return "", err
	}
	if objectType == "VIEW" {
		return s.buildViewDDL(schema, table)
	}
	var ddl string
	var indexDDLs []string
	if portable && objectType == "TABLE" {
		var metadataErr error
		err = withOraclePortableMetadataSession(db, func(conn *sql.Conn) error {
			metadataErr = conn.QueryRowContext(
				context.Background(),
				"SELECT DBMS_METADATA.GET_DDL(:1, :2, :3) FROM DUAL",
				objectType,
				table,
				schema,
			).Scan(&ddl)
			indexDDLs, _ = loadTableIndexDDLsFromConn(conn, schema, table)
			return nil
		})
		if err != nil {
			return "", err
		}
		err = metadataErr
	} else {
		err = db.QueryRow("SELECT DBMS_METADATA.GET_DDL(:1, :2, :3) FROM DUAL", objectType, table, schema).Scan(&ddl)
	}
	if err == nil && strings.TrimSpace(ddl) != "" {
		if objectType == "TABLE" {
			if portable {
				return s.appendTableDependentDDLWithIndexes(schema, table, ddl, indexDDLs), nil
			}
			return s.appendTableDependentDDL(schema, table, ddl), nil
		}
		return ddl, nil
	}
	if objectType == "TABLE" {
		if exactIdentity {
			if err != nil {
				return "", err
			}
			return "", errors.New("exact table DDL is unavailable")
		}
		fallback, fallbackErr := s.buildTableDDL(schema, table)
		if fallbackErr != nil {
			return "", fallbackErr
		}
		if portable {
			return s.appendTableDependentDDLWithIndexes(schema, table, fallback, indexDDLs), nil
		}
		return s.appendTableDependentDDL(schema, table, fallback), nil
	}
	return "", err
}

func withOraclePortableMetadataSession(db *sql.DB, operation func(*sql.Conn) error) (err error) {
	conn, err := db.Conn(context.Background())
	if err != nil {
		return err
	}
	if _, err = conn.ExecContext(context.Background(), oracleDisableSegmentAttributesSQL); err != nil {
		_ = conn.Close()
		return fmt.Errorf("failed to disable Oracle segment attributes: %w", err)
	}
	defer func() {
		if _, resetErr := conn.ExecContext(context.Background(), oracleEnableSegmentAttributesSQL); resetErr != nil {
			_ = conn.Raw(func(any) error { return driver.ErrBadConn })
			if err == nil {
				err = fmt.Errorf("failed to restore Oracle segment attributes: %w", resetErr)
			}
		}
		_ = conn.Close()
	}()
	return operation(conn)
}

func (s *server) appendTableDependentDDL(schema, table, tableDDL string) string {
	indexDDLs, _ := s.loadTableIndexDDLs(schema, table)
	return s.appendTableDependentDDLWithIndexes(schema, table, tableDDL, indexDDLs)
}

func (s *server) appendTableDependentDDLWithIndexes(schema, table, tableDDL string, indexDDLs []string) string {
	var builder strings.Builder
	baseDDL := strings.TrimSpace(tableDDL)
	builder.WriteString(baseDDL)
	dependentAppended := false
	appendDependent := func(ddl string) {
		if strings.TrimSpace(ddl) == "" {
			return
		}
		if !dependentAppended && !strings.HasSuffix(baseDDL, ";") && !strings.HasSuffix(baseDDL, "/") {
			builder.WriteByte(';')
		}
		appendOracleDDLFragment(&builder, ddl)
		dependentAppended = true
	}

	for _, ddl := range indexDDLs {
		appendDependent(ddl)
	}
	if triggerDDLs, err := s.loadTableTriggerDDLs(schema, table); err == nil {
		for _, ddl := range triggerDDLs {
			appendDependent(ddl)
		}
	}
	if comments, err := s.loadTableCommentDDLs(schema, table); err == nil {
		for _, ddl := range comments {
			appendDependent(ddl)
		}
	}
	return builder.String()
}

const oracleTableIndexDDLsSQL = `
SELECT DBMS_METADATA.GET_DDL('INDEX', i.INDEX_NAME, i.OWNER)
FROM ALL_INDEXES i
WHERE i.TABLE_OWNER = :1
  AND i.TABLE_NAME = :2
  AND i.GENERATED = 'N'
  AND i.INDEX_NAME NOT IN (
    SELECT c.INDEX_NAME
    FROM ALL_CONSTRAINTS c
    WHERE c.OWNER = :3
      AND c.TABLE_NAME = :4
      AND c.CONSTRAINT_TYPE IN ('P', 'U')
      AND c.INDEX_NAME IS NOT NULL
      AND c.INDEX_OWNER = i.OWNER
  )
ORDER BY i.INDEX_NAME`

func (s *server) loadTableIndexDDLs(schema, table string) ([]string, error) {
	rows, err := s.queryRows(oracleTableIndexDDLsSQL, []any{schema, table, schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	return scanOracleDDLs(rows)
}

func loadTableIndexDDLsFromConn(conn *sql.Conn, schema, table string) ([]string, error) {
	rows, err := conn.QueryContext(context.Background(), oracleTableIndexDDLsSQL, schema, table, schema, table)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanOracleDDLs(rows)
}

func scanOracleDDLs(rows *sql.Rows) ([]string, error) {
	var result []string
	for rows.Next() {
		var ddl sql.NullString
		if err := rows.Scan(&ddl); err != nil {
			return nil, err
		}
		if ddl.Valid && strings.TrimSpace(ddl.String) != "" {
			result = append(result, ddl.String)
		}
	}
	return result, rows.Err()
}

func (s *server) loadTableTriggerDDLs(schema, table string) ([]string, error) {
	rows, err := s.queryRows(`
SELECT DBMS_METADATA.GET_DDL('TRIGGER', t.TRIGGER_NAME, t.OWNER)
FROM ALL_TRIGGERS t
WHERE t.TABLE_OWNER = :1 AND t.TABLE_NAME = :2
ORDER BY t.TRIGGER_NAME`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	var result []string
	for rows.Next() {
		var ddl sql.NullString
		if err := rows.Scan(&ddl); err != nil {
			return nil, err
		}
		if ddl.Valid && strings.TrimSpace(ddl.String) != "" {
			result = append(result, ddl.String)
		}
	}
	return result, rows.Err()
}

func (s *server) loadTableCommentDDLs(schema, table string) ([]string, error) {
	db, err := s.requireDB()
	if err != nil {
		return nil, err
	}
	qualifiedTable := quoteIdentifier(schema) + "." + quoteIdentifier(table)
	var result []string
	var tableComment sql.NullString
	err = db.QueryRow(
		"SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER = :1 AND TABLE_NAME = :2",
		schema,
		table,
	).Scan(&tableComment)
	if err != nil && !errors.Is(err, sql.ErrNoRows) {
		return nil, err
	}
	if tableComment.Valid && strings.TrimSpace(tableComment.String) != "" {
		result = append(result, fmt.Sprintf("COMMENT ON TABLE %s IS %s", qualifiedTable, oracleStringLiteral(tableComment.String)))
	}

	rows, err := s.queryRows(`
SELECT COLUMN_NAME, COMMENTS
FROM ALL_COL_COMMENTS
WHERE OWNER = :1 AND TABLE_NAME = :2 AND COMMENTS IS NOT NULL
ORDER BY COLUMN_NAME`, []any{schema, table})
	if err != nil {
		return nil, err
	}
	defer s.closeRows(rows)
	for rows.Next() {
		var columnName string
		var comment sql.NullString
		if err := rows.Scan(&columnName, &comment); err != nil {
			return nil, err
		}
		if comment.Valid && strings.TrimSpace(comment.String) != "" {
			result = append(result, fmt.Sprintf(
				"COMMENT ON COLUMN %s.%s IS %s",
				qualifiedTable,
				quoteIdentifier(columnName),
				oracleStringLiteral(comment.String),
			))
		}
	}
	return result, rows.Err()
}

func appendOracleDDLFragment(builder *strings.Builder, ddl string) {
	trimmed := strings.TrimSpace(ddl)
	if trimmed == "" {
		return
	}
	if builder.Len() > 0 {
		builder.WriteString("\n\n")
	}
	builder.WriteString(trimmed)
	if !strings.HasSuffix(trimmed, ";") && !strings.HasSuffix(trimmed, "/") {
		builder.WriteByte(';')
	}
}

func oracleStringLiteral(value string) string {
	return "'" + strings.ReplaceAll(value, "'", "''") + "'"
}

func (s *server) resolveDDLObject(schema, name, requested string) (string, string, error) {
	return s.resolveDDLObjectWithIdentity(schema, name, requested, false)
}

func (s *server) resolveDDLObjectWithIdentity(schema, name, requested string, exactIdentity bool) (string, string, error) {
	exact, uppercase, hasUppercaseFallback := oracleObjectNameCandidates(name)
	if exactIdentity {
		exact, hasUppercaseFallback = name, false
	}
	objectType := normalizeDDLObjectType(requested)
	if objectType != "" {
		return objectType, exact, nil
	}
	db, err := s.requireDB()
	if err != nil {
		return "", "", err
	}
	resolve := func(objectName string) error {
		return db.QueryRow(`
SELECT OBJECT_TYPE
FROM (
  SELECT OBJECT_TYPE
  FROM ALL_OBJECTS
  WHERE OWNER = :1
    AND OBJECT_NAME = :2
    AND OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW')
  ORDER BY CASE OBJECT_TYPE WHEN 'TABLE' THEN 0 WHEN 'VIEW' THEN 1 ELSE 2 END
)
WHERE ROWNUM = 1`, schema, objectName).Scan(&objectType)
	}
	err = resolve(exact)
	if errors.Is(err, sql.ErrNoRows) && hasUppercaseFallback {
		err = resolve(uppercase)
		if err == nil {
			exact = uppercase
		}
	}
	if errors.Is(err, sql.ErrNoRows) {
		return "", "", fmt.Errorf("object not found: %s.%s", schema, name)
	}
	if err != nil {
		return "", "", err
	}
	return normalizeDDLObjectType(objectType), exact, nil
}

func normalizeDDLObjectType(value string) string {
	switch strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(value), " ", "_")) {
	case "TABLE":
		return "TABLE"
	case "VIEW":
		return "VIEW"
	case "MATERIALIZED_VIEW":
		return "MATERIALIZED_VIEW"
	default:
		return ""
	}
}

func (s *server) buildViewDDL(schema, name string) (string, error) {
	source, err := s.getViewSource(schema, name)
	if err != nil {
		return "", err
	}
	trimmed := strings.TrimSpace(source)
	upperSource := strings.ToUpper(trimmed)
	var ddl string
	if strings.HasPrefix(upperSource, "CREATE ") || strings.HasPrefix(upperSource, "ALTER ") {
		ddl = trimmed
	} else {
		ddl = fmt.Sprintf("CREATE OR REPLACE VIEW %s.%s AS\n%s", quoteIdentifier(schema), quoteIdentifier(name), trimmed)
	}
	return s.appendViewCommentDDLs(schema, name, ddl), nil
}

// appendViewCommentDDLs appends COMMENT ON TABLE/COLUMN statements for views.
// Oracle stores view comments in ALL_TAB_COMMENTS / ALL_COL_COMMENTS the same way as tables.
func (s *server) appendViewCommentDDLs(schema, name, viewDDL string) string {
	comments, err := s.loadTableCommentDDLs(schema, name)
	if err != nil || len(comments) == 0 {
		return viewDDL
	}
	var builder strings.Builder
	builder.WriteString(terminateOracleViewDDL(strings.TrimSpace(viewDDL)))
	for _, comment := range comments {
		appendOracleDDLFragment(&builder, comment)
	}
	return builder.String()
}

func terminateOracleViewDDL(ddl string) string {
	var lastCode byte
	trailingLineComment := false
	for pos := 0; pos < len(ddl); pos++ {
		if isSQLWhitespace(ddl[pos]) {
			continue
		}
		if ddl[pos] == '-' && pos+1 < len(ddl) && ddl[pos+1] == '-' {
			pos = skipLineCommentSQL(ddl, pos)
			trailingLineComment = true
			continue
		}
		if ddl[pos] == '/' && pos+1 < len(ddl) && ddl[pos+1] == '*' {
			pos = skipBlockCommentSQL(ddl, pos)
			trailingLineComment = false
			continue
		}
		if end, ok := skipOracleAlternativeQuotedSQL(ddl, pos); ok {
			pos = end
		} else if ddl[pos] == '\'' {
			pos = skipSingleQuotedSQL(ddl, pos)
		} else if ddl[pos] == '"' {
			pos = skipDoubleQuotedSQL(ddl, pos)
		}
		lastCode = ddl[pos]
		trailingLineComment = false
	}
	if lastCode == ';' || lastCode == '/' {
		return ddl
	}
	if trailingLineComment {
		return ddl + "\n;"
	}
	return ddl + ";"
}

func (s *server) getViewSource(schema, name string) (string, error) {
	db, err := s.requireDB()
	if err != nil {
		return "", err
	}
	viewName := strings.TrimSpace(name)
	var source string
	viewsErr := db.QueryRow(
		"SELECT TEXT FROM ALL_VIEWS WHERE OWNER = :1 AND VIEW_NAME = :2",
		schema, viewName,
	).Scan(&source)
	if viewsErr == nil && strings.TrimSpace(source) != "" {
		return strings.TrimSpace(source), nil
	}

	var ddl string
	metadataErr := db.QueryRow(
		"SELECT DBMS_METADATA.GET_DDL('VIEW', :1, :2) FROM DUAL",
		viewName, schema,
	).Scan(&ddl)
	if metadataErr == nil && strings.TrimSpace(ddl) != "" {
		return strings.TrimSpace(ddl), nil
	}

	if viewsErr != nil && !errors.Is(viewsErr, sql.ErrNoRows) && metadataErr != nil {
		return "", fmt.Errorf(
			"failed to load view source for %s.%s: ALL_VIEWS: %v; DBMS_METADATA: %w",
			schema, viewName, viewsErr, metadataErr,
		)
	}
	return "", fmt.Errorf("view source not found: %s.%s", schema, viewName)
}

func (s *server) buildTableDDL(schema, table string) (string, error) {
	columns, err := s.getColumns(schema, table)
	if err != nil {
		return "", err
	}
	if len(columns) == 0 {
		return "", fmt.Errorf("table not found: %s.%s", schema, table)
	}
	var builder strings.Builder
	builder.WriteString("CREATE TABLE ")
	builder.WriteString(quoteIdentifier(schema))
	builder.WriteByte('.')
	builder.WriteString(quoteIdentifier(table))
	builder.WriteString(" (\n")
	for i, column := range columns {
		if i > 0 {
			builder.WriteString(",\n")
		}
		builder.WriteString("  ")
		builder.WriteString(quoteIdentifier(column.Name))
		builder.WriteByte(' ')
		builder.WriteString(oracleColumnTypeDDL(column))
		if column.ColumnDefault != nil && strings.TrimSpace(*column.ColumnDefault) != "" {
			builder.WriteString(" DEFAULT ")
			builder.WriteString(strings.TrimSpace(*column.ColumnDefault))
		}
		if !column.IsNullable {
			builder.WriteString(" NOT NULL")
		}
	}
	primary := make([]string, 0)
	for _, column := range columns {
		if column.IsPrimaryKey {
			primary = append(primary, quoteIdentifier(column.Name))
		}
	}
	if len(primary) > 0 {
		builder.WriteString(",\n  PRIMARY KEY (")
		builder.WriteString(strings.Join(primary, ", "))
		builder.WriteByte(')')
	}
	builder.WriteString("\n)")
	return builder.String(), nil
}

func oracleColumnTypeDDL(column columnInfo) string {
	dataType := strings.ToUpper(strings.TrimSpace(column.DataType))
	if dataType == "" {
		return "VARCHAR2(4000)"
	}
	if strings.Contains(dataType, "(") {
		return dataType
	}
	if dataType == "RAW" {
		if column.DataLength != nil && *column.DataLength > 0 {
			return fmt.Sprintf("RAW(%d)", *column.DataLength)
		}
		return dataType
	}
	if isOracleCharacterType(dataType) && column.CharacterMaximumLength != nil && *column.CharacterMaximumLength > 0 {
		if unit := oracleCharacterLengthUnit(dataType, column.CharacterLengthUnit); unit != "" {
			return fmt.Sprintf("%s(%d %s)", dataType, *column.CharacterMaximumLength, unit)
		}
		return fmt.Sprintf("%s(%d)", dataType, *column.CharacterMaximumLength)
	}
	if dataType == "NUMBER" {
		if column.NumericPrecision == nil && column.NumericScale != nil {
			return fmt.Sprintf("NUMBER(*,%d)", *column.NumericScale)
		}
		if column.NumericPrecision != nil && *column.NumericPrecision > 0 {
			if column.NumericScale != nil && *column.NumericScale != 0 {
				return fmt.Sprintf("NUMBER(%d,%d)", *column.NumericPrecision, *column.NumericScale)
			}
			return fmt.Sprintf("NUMBER(%d)", *column.NumericPrecision)
		}
		return "NUMBER"
	}
	if dataType == "FLOAT" &&
		column.NumericPrecision != nil && *column.NumericPrecision > 0 {
		return fmt.Sprintf("%s(%d)", dataType, *column.NumericPrecision)
	}
	return dataType
}

func oracleCharacterLengthUnit(dataType string, charUsed *string) string {
	switch dataType {
	case "CHAR", "VARCHAR", "VARCHAR2":
	default:
		return ""
	}
	if charUsed == nil {
		return ""
	}
	switch strings.ToUpper(strings.TrimSpace(*charUsed)) {
	case "B":
		return "BYTE"
	case "C":
		return "CHAR"
	default:
		return ""
	}
}

func isOracleCharacterType(dataType string) bool {
	switch dataType {
	case "CHAR", "VARCHAR2", "VARCHAR", "NCHAR", "NVARCHAR2", "RAW":
		return true
	default:
		return false
	}
}

func (s *server) getExplainInfo(sqlText, database, schema string, timeoutSecs int) (string, error) {
	if strings.TrimSpace(sqlText) == "" {
		return "", errors.New("sql is required")
	}
	db, err := s.requireDB()
	if err != nil {
		return "", err
	}

	ctx := context.Background()
	var cancel context.CancelFunc
	if timeoutSecs > 0 {
		ctx, cancel = context.WithTimeout(ctx, time.Duration(timeoutSecs)*time.Second)
	} else {
		ctx, cancel = context.WithCancel(ctx)
	}
	defer cancel()

	conn, err := db.Conn(ctx)
	if err != nil {
		return "", err
	}
	defer conn.Close()

	targetSchema := oracleExplainTargetSchema(database, schema, s.params.Database)
	if targetSchema != "" {
		var originalSchema string
		if err := conn.QueryRowContext(ctx, "SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL").Scan(&originalSchema); err != nil {
			return "", err
		}
		if !strings.EqualFold(originalSchema, targetSchema) {
			if _, err := conn.ExecContext(ctx, "ALTER SESSION SET CURRENT_SCHEMA = "+quoteIdentifier(targetSchema)); err != nil {
				return "", err
			}
			defer restoreOracleCurrentSchema(conn, originalSchema)
		}
	}

	statementID := "DBX_" + strings.ToUpper(strconv.FormatInt(time.Now().UnixNano(), 36))
	// EXPLAIN PLAN resolves its plan table in the session user's schema, while a bare
	// PLAN_TABLE inside DBMS_XPLAN.DISPLAY resolves through CURRENT_SCHEMA - and the
	// schema switch above changes CURRENT_SCHEMA. Name the table explicitly so the read
	// and the cleanup hit the same object EXPLAIN PLAN wrote to, otherwise DISPLAY answers
	// "cannot fetch plan for statement_id '...'".
	planTable := oracleExplainPlanTableName(ctx, conn)
	defer cleanupOracleExplainPlan(conn, statementID, planTable)
	statementSQL := trimStatementSQL(sqlText)
	explainArgs := oracleExplainPlanBindArgs(statementSQL)
	if _, err := conn.ExecContext(ctx, "EXPLAIN PLAN SET STATEMENT_ID = '"+statementID+"' FOR "+statementSQL, explainArgs...); err != nil {
		return "", err
	}
	planRows, err := conn.QueryContext(
		ctx,
		"SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('"+planTable+"', :1, 'TYPICAL +PREDICATE'))",
		statementID,
	)
	if err != nil {
		return "", err
	}
	defer planRows.Close()
	var builder strings.Builder
	for planRows.Next() {
		var line string
		if err := planRows.Scan(&line); err != nil {
			return "", err
		}
		builder.WriteString(line)
		builder.WriteByte('\n')
	}
	return strings.TrimSpace(builder.String()), planRows.Err()
}

func oracleExplainTargetSchema(database, schema, configuredDatabase string) string {
	if schema = strings.TrimSpace(schema); schema != "" {
		return schema
	}
	database = oracleConnectionDatabaseName(database)
	if database == "" || strings.EqualFold(database, oracleConnectionDatabaseName(configuredDatabase)) {
		return ""
	}
	return database
}

// oracleExplainPlanTableName returns the explicitly qualified name of the plan table that
// EXPLAIN PLAN resolves for this session: the session user's own object named PLAN_TABLE when
// it exists, otherwise the table behind the PUBLIC synonym (usually SYS.PLAN_TABLE$). Falls
// back to the bare name when neither can be resolved.
func oracleExplainPlanTableName(ctx context.Context, conn *sql.Conn) string {
	const probe = `SELECT
		(SELECT OWNER FROM ALL_OBJECTS WHERE OWNER = USER AND OBJECT_NAME = 'PLAN_TABLE' AND ROWNUM = 1),
		(SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = USER AND OBJECT_NAME = 'PLAN_TABLE' AND ROWNUM = 1),
		(SELECT TABLE_OWNER FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'PLAN_TABLE' AND ROWNUM = 1),
		(SELECT TABLE_NAME FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' AND SYNONYM_NAME = 'PLAN_TABLE' AND ROWNUM = 1)
	FROM DUAL`
	var sessionOwner, sessionName, publicOwner, publicName sql.NullString
	if err := conn.QueryRowContext(ctx, probe).Scan(&sessionOwner, &sessionName, &publicOwner, &publicName); err != nil {
		return "PLAN_TABLE"
	}
	return oraclePlanTableName(sessionOwner.String, sessionName.String, publicOwner.String, publicName.String)
}

// oraclePlanTableName picks the qualified plan table name from the probe results, preferring
// the session user's own PLAN_TABLE because that is what EXPLAIN PLAN writes to.
func oraclePlanTableName(sessionOwner, sessionName, publicOwner, publicName string) string {
	if strings.TrimSpace(sessionOwner) != "" && strings.TrimSpace(sessionName) != "" {
		return quoteIdentifier(strings.TrimSpace(sessionOwner)) + "." + quoteIdentifier(strings.TrimSpace(sessionName))
	}
	if strings.TrimSpace(publicOwner) != "" && strings.TrimSpace(publicName) != "" {
		return quoteIdentifier(strings.TrimSpace(publicOwner)) + "." + quoteIdentifier(strings.TrimSpace(publicName))
	}
	return "PLAN_TABLE"
}

func cleanupOracleExplainPlan(conn *sql.Conn, statementID, planTable string) {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	_, _ = conn.ExecContext(ctx, "DELETE FROM "+planTable+" WHERE STATEMENT_ID = :1", statementID)
}

type oracleBindParam struct {
	Name       string
	Positional bool
}

func oracleExplainPlanBindArgs(sqlText string) []any {
	params := oracleExplainPlanBindParams(sqlText)
	args := make([]any, 0, len(params))
	for _, param := range params {
		if param.Positional {
			args = append(args, nil)
			continue
		}
		args = append(args, sql.Named(param.Name, nil))
	}
	return args
}

func oracleExplainPlanBindParams(sqlText string) []oracleBindParam {
	params := make([]oracleBindParam, 0)
	seenNamed := map[string]bool{}
	for pos := 0; pos < len(sqlText); pos++ {
		switch sqlText[pos] {
		case '\'':
			pos = skipSingleQuotedSQL(sqlText, pos)
		case '"':
			pos = skipDoubleQuotedSQL(sqlText, pos)
		case 'q', 'Q':
			if end, ok := skipOracleAlternativeQuotedSQL(sqlText, pos); ok {
				pos = end
			}
		case '-':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '-' {
				pos = skipLineCommentSQL(sqlText, pos)
			}
		case '/':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '*' {
				pos = skipBlockCommentSQL(sqlText, pos)
			}
		case ':':
			param, end, ok := readOracleBindParam(sqlText, pos)
			if !ok {
				continue
			}
			if param.Positional {
				params = append(params, param)
			} else if key := strings.ToUpper(param.Name); !seenNamed[key] {
				seenNamed[key] = true
				params = append(params, param)
			}
			pos = end - 1
		}
	}
	return params
}

func readOracleBindParam(sqlText string, pos int) (oracleBindParam, int, bool) {
	if pos < 0 || pos+1 >= len(sqlText) || sqlText[pos] != ':' {
		return oracleBindParam{}, pos, false
	}
	if pos > 0 && sqlText[pos-1] == ':' {
		return oracleBindParam{}, pos, false
	}
	next := sqlText[pos+1]
	if next >= '0' && next <= '9' {
		end := pos + 2
		for end < len(sqlText) && sqlText[end] >= '0' && sqlText[end] <= '9' {
			end++
		}
		return oracleBindParam{Name: sqlText[pos+1 : end], Positional: true}, end, true
	}
	if !isOracleIdentifierStart(next) {
		return oracleBindParam{}, pos, false
	}
	end := pos + 2
	for end < len(sqlText) && isOracleIdentifierPart(sqlText[end]) {
		end++
	}
	return oracleBindParam{Name: sqlText[pos+1 : end]}, end, true
}

func restoreOracleCurrentSchema(conn *sql.Conn, schema string) {
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	_, _ = conn.ExecContext(ctx, "ALTER SESSION SET CURRENT_SCHEMA = "+quoteIdentifier(schema))
}

func (s *server) executeTransaction(params map[string]json.RawMessage) (queryResult, error) {
	if s.hasManualTransaction() {
		return queryResult{}, errors.New("cannot start a one-shot transaction while a manual transaction is open")
	}
	var payload struct {
		Statements []string `json:"statements"`
		Schema     string   `json:"schema"`
	}
	if err := decodeParams(params, &payload); err != nil {
		return queryResult{}, err
	}
	var result queryResult
	var err error
	messages := s.withStatementDBMSOutput(strings.Join(payload.Statements, "\n"), func() error {
		result, err = s.executeTransactionStatements(payload.Statements, payload.Schema)
		return err
	})
	result.Messages = append(result.Messages, messages...)
	return result, err
}

func (s *server) executeTransactionStatements(statements []string, schema string) (queryResult, error) {
	ctx := s.statementContext()
	tx, err := s.beginStatementTx(ctx)
	if err != nil {
		return queryResult{}, err
	}
	if strings.TrimSpace(schema) != "" {
		if _, err := tx.ExecContext(ctx, "ALTER SESSION SET CURRENT_SCHEMA = "+quoteIdentifier(schema)); err != nil {
			tx.Rollback()
			return queryResult{}, err
		}
	}
	var affected int64
	start := time.Now()
	for _, statement := range statements {
		statement = trimStatementSQL(statement)
		if statement == "" {
			continue
		}
		result, err := tx.ExecContext(ctx, statement)
		if err != nil {
			tx.Rollback()
			return queryResult{}, err
		}
		count, _ := result.RowsAffected()
		affected += count
	}
	if err := tx.Commit(); err != nil {
		return queryResult{}, err
	}
	return queryResult{
		Columns:         []string{},
		Rows:            [][]any{},
		AffectedRows:    affected,
		ExecutionTimeMS: time.Since(start).Milliseconds(),
	}, nil
}

func (s *server) executeQueryPage(opts queryOptions, pageSize int) (queryPageResult, error) {
	var result queryPageResult
	var err error
	conn, messages := s.withPagedStatementDBMSOutput(opts.SQL, func() error {
		result, err = s.executeQueryPageWithoutOutput(opts, pageSize)
		return err
	})
	result.Messages = append(result.Messages, messages...)
	if conn != nil {
		if err == nil && result.SessionID != nil {
			s.sessions[*result.SessionID].conn = conn
		} else {
			_ = conn.Close()
		}
	}
	return result, err
}

func (s *server) executeQueryPageWithoutOutput(opts queryOptions, pageSize int) (queryPageResult, error) {
	start := time.Now()
	if strings.TrimSpace(opts.Schema) != "" && !s.hasManualTransaction() {
		if err := s.setSchema(opts.Schema); err != nil {
			return queryPageResult{}, err
		}
	}
	sqlText := trimStatementSQL(opts.SQL)
	if !isQuerySQL(sqlText) {
		result, err := s.executeQuery(opts)
		return queryPageResult{
			Columns:         result.Columns,
			ColumnTypes:     result.ColumnTypes,
			Rows:            result.Rows,
			AffectedRows:    result.AffectedRows,
			ExecutionTimeMS: result.ExecutionTimeMS,
			Truncated:       result.Truncated,
			SessionID:       nil,
			HasMore:         false,
		}, err
	}
	result, session, err := s.runPagedOracleSelect(sqlText, opts, pageSize, start)
	if err != nil {
		return queryPageResult{}, err
	}
	if session != nil {
		sessionID := s.storeQuerySession(session)
		result.SessionID = &sessionID
	}
	return result, nil
}

// runPagedOracleSelect executes a SELECT through the value-rewrite path and
// reads its first page. It returns the live session when more pages remain.
// When the first page panics while decoding an unsupported column type (before
// any row was streamed to the client), it retries once with the placeholder
// projection so the remaining columns stay readable; panics on later pages
// surface as errors instead.
func (s *server) runPagedOracleSelect(sqlText string, opts queryOptions, pageSize int, start time.Time) (queryPageResult, *querySession, error) {
	// Until a cursor is handed to the caller, this function owns its rows,
	// including panics while reading Columns/ColumnTypes before the first page.
	var ownedRows *sql.Rows
	defer func() { s.closeRows(ownedRows) }()
	for attempt := 0; ; attempt++ {
		// Only the first page may be retried: once rows have been returned the
		// caller owns them and a second execution would duplicate data.
		retrySQL := func(err error) (string, bool) {
			if attempt != 0 || s.hasManualTransaction() || oracleSQLLocksRows(sqlText) {
				return "", false
			}
			return oraclePagedRetrySQL(sqlText, s.loadOracleColumnMeta, err)
		}
		rows, err := s.queryRowsWithOracleValueRewriteIfNeeded(sqlText, opts.TimeoutSecs, deferLobsForQuery(sqlText, opts.DeferLOBs))
		if err != nil {
			if rewritten, ok := retrySQL(err); ok {
				sqlText = rewritten
				continue
			}
			return queryPageResult{}, nil, err
		}
		ownedRows = rows
		columns, err := rows.Columns()
		if err != nil {
			s.closeRows(rows)
			if rewritten, ok := retrySQL(err); ok {
				sqlText = rewritten
				continue
			}
			return queryPageResult{}, nil, err
		}
		columnTypes := columnTypeNames(rows)
		maxRows := opts.MaxRows
		if maxRows <= 0 {
			maxRows = defaultMaxRows
		}
		session := &querySession{rows: rows, columns: columns, columnTypes: columnTypes, remaining: maxRows, outputIdentity: s.currentDBMSOutputIdentity()}
		result, err := readQuerySessionPage(session, pageSize)
		result.ExecutionTimeMS = time.Since(start).Milliseconds()
		if err != nil {
			s.closeRows(rows)
			if rewritten, ok := retrySQL(err); ok {
				sqlText = rewritten
				continue
			}
			return queryPageResult{}, nil, err
		}
		if result.HasMore {
			ownedRows = nil
			return result, session, nil
		}
		s.closeRows(rows)
		return result, nil, nil
	}
}

// oraclePlaceholderRetrySQL returns the placeholder projection used to retry a
// query whose first page panicked while decoding values of an unsupported
// column type. It reports false when no placeholder rewrite applies.
func oraclePlaceholderRetrySQL(sqlText string, loadColumns oracleColumnMetaLoader) (string, bool) {
	placeholder, err := rewriteOracleSelectSQL(sqlText, loadColumns, true)
	if err != nil || placeholder == sqlText {
		return "", false
	}
	return placeholder, true
}

// oraclePagedRetrySQL returns the statement to retry with when the first page
// of a paged Oracle SELECT fails while reading the response.
//
// A driver panic means the driver state for the scanned value is unknown, so it
// keeps the placeholder projection used by the non-paged fallback. Response
// reading failures such as go-ora's "TTC error: received code ..." mean a column
// value could not be decoded (SDO_GEOMETRY, XMLTYPE, ...); those retry with the
// value rewrite so the columns come back as WKT / serialized text instead of
// failing the whole page (issue #13179). The non-paged executeSelect path
// already performs this rewrite in executeOracleSelectWithXMLTypeRetry; without
// it the paged path (table data and paged query results) surfaced the raw TTC
// error and the table could not be opened.
func oraclePagedRetrySQL(sqlText string, loadColumns oracleColumnMetaLoader, err error) (string, bool) {
	var panicErr oracleDriverPanicError
	if errors.As(err, &panicErr) {
		return oraclePlaceholderRetrySQL(sqlText, loadColumns)
	}
	if !shouldRetryOracleXMLTypeRewrite(err) {
		return "", false
	}
	rewritten, rewriteErr := rewriteOracleSelectSQL(sqlText, loadColumns, false)
	if rewriteErr != nil || rewritten == sqlText {
		return "", false
	}
	return rewritten, true
}

func (s *server) fetchQueryPage(sessionID string, pageSize int) (queryPageResult, error) {
	session := s.sessions[sessionID]
	if session == nil {
		return queryPageResult{Columns: []string{}, ColumnTypes: []string{}, Rows: [][]any{}, SessionID: nil, HasMore: false}, nil
	}
	s.dbmsOutput.markDirty(session.outputIdentity)
	result, err := readQuerySessionPage(session, pageSize)
	if err != nil {
		s.closeQuerySession(sessionID)
		return queryPageResult{}, err
	}
	if result.HasMore {
		result.SessionID = &sessionID
	} else {
		s.closeQuerySession(sessionID)
	}
	return result, nil
}

func (s *server) storeQuerySession(session *querySession) string {
	s.nextSessionID++
	sessionID := fmt.Sprintf("oracle-go-%d", s.nextSessionID)
	s.sessions[sessionID] = session
	return sessionID
}

func (s *server) startTableRead(opts queryOptions, pageSize int) (queryPageResult, error) {
	s.dbmsOutput.markDirty(s.currentDBMSOutputIdentity())
	start := time.Now()
	if strings.TrimSpace(opts.Schema) != "" && !s.hasManualTransaction() {
		if err := s.setSchema(opts.Schema); err != nil {
			return queryPageResult{}, err
		}
	}
	sqlText := trimStatementSQL(opts.SQL)
	if !isQuerySQL(sqlText) {
		return queryPageResult{}, errors.New("table read requires a SELECT query")
	}
	result, session, err := s.runPagedOracleSelect(sqlText, opts, pageSize, start)
	if err != nil {
		return queryPageResult{}, err
	}
	if session != nil {
		sessionID := s.storeTableReadSession(session)
		result.SessionID = &sessionID
	}
	return result, nil
}

func (s *server) fetchTableReadPage(sessionID string, pageSize int) (queryPageResult, error) {
	session := s.tableReadSessions[sessionID]
	if session == nil {
		return queryPageResult{Columns: []string{}, ColumnTypes: []string{}, Rows: [][]any{}, SessionID: nil, HasMore: false}, nil
	}
	s.dbmsOutput.markDirty(session.outputIdentity)
	result, err := readQuerySessionPage(session, pageSize)
	if err != nil {
		s.closeTableReadSession(sessionID)
		return queryPageResult{}, err
	}
	if result.HasMore {
		result.SessionID = &sessionID
	} else {
		s.closeTableReadSession(sessionID)
	}
	return result, nil
}

func (s *server) storeTableReadSession(session *querySession) string {
	s.nextTableReadSessionID++
	sessionID := fmt.Sprintf("oracle-go-table-%d", s.nextTableReadSessionID)
	s.tableReadSessions[sessionID] = session
	return sessionID
}

func (s *server) closeQuerySession(sessionID string) bool {
	session := s.sessions[sessionID]
	if session == nil {
		return false
	}
	s.dbmsOutput.markDirty(session.outputIdentity)
	s.closeRows(session.rows)
	if session.conn != nil {
		_ = session.conn.Close()
	}
	delete(s.sessions, sessionID)
	return true
}

func (s *server) closeTableReadSession(sessionID string) bool {
	session := s.tableReadSessions[sessionID]
	if session == nil {
		return false
	}
	s.dbmsOutput.markDirty(session.outputIdentity)
	s.closeRows(session.rows)
	delete(s.tableReadSessions, sessionID)
	return true
}

func (s *server) closeAllQuerySessions() {
	for sessionID := range s.sessions {
		s.closeQuerySession(sessionID)
	}
	for sessionID := range s.tableReadSessions {
		s.closeTableReadSession(sessionID)
	}
}

func readQuerySessionPage(session *querySession, pageSize int) (result queryPageResult, err error) {
	// go-ora can panic while decoding row values for column types it does not
	// support (for example custom object types). Convert those panics to errors
	// so row iteration failures never terminate the agent process and break
	// the RPC stream.
	defer func() {
		if recovered := recover(); recovered != nil {
			result = queryPageResult{}
			err = oracleDriverPanicError{value: recovered}
		}
	}()
	if pageSize <= 0 {
		pageSize = defaultMaxRows
	}
	result = queryPageResult{Columns: session.columns, ColumnTypes: session.columnTypes, Rows: [][]any{}, SessionID: nil, HasMore: false}
	for len(result.Rows) < pageSize && session.remaining > 0 {
		if session.pending != nil {
			result.Rows = append(result.Rows, session.pending)
			session.pending = nil
			session.remaining--
			continue
		}
		if !session.rows.Next() {
			return result, session.rows.Err()
		}
		row, err := scanRow(session.rows, len(session.columns), session.columnTypes)
		if err != nil {
			return queryPageResult{}, err
		}
		result.Rows = append(result.Rows, row)
		session.remaining--
	}
	if session.remaining <= 0 {
		result.Truncated = true
		return result, nil
	}
	// A full page is enough evidence that another page may exist. Do not read
	// one extra row just to decide has_more: with a prefetch boundary this
	// forces another Oracle round trip before the first page can be displayed.
	if len(result.Rows) >= pageSize {
		result.HasMore = true
		return result, nil
	}
	if session.rows.Next() {
		row, err := scanRow(session.rows, len(session.columns), session.columnTypes)
		if err != nil {
			return queryPageResult{}, err
		}
		session.pending = row
		result.HasMore = true
		return result, nil
	}
	return result, session.rows.Err()
}

func (s *server) executeQuery(opts queryOptions) (queryResult, error) {
	var result queryResult
	var err error
	messages := s.withStatementDBMSOutput(opts.SQL, func() error {
		result, err = s.executeQueryWithoutOutput(opts)
		return err
	})
	result.Messages = append(result.Messages, messages...)
	return result, err
}

func (s *server) executeQueryWithoutOutput(opts queryOptions) (queryResult, error) {
	start := time.Now()
	if strings.TrimSpace(opts.Schema) != "" && !s.hasManualTransaction() {
		if err := s.setSchema(opts.Schema); err != nil {
			return queryResult{}, err
		}
	}
	sqlText := trimStatementSQL(opts.SQL)
	maxRows := opts.MaxRows
	if maxRows <= 0 {
		maxRows = defaultMaxRows
	}
	if isQuerySQL(sqlText) {
		result, err := s.executeSelect(sqlText, maxRows, opts.TimeoutSecs, deferLobsForQuery(sqlText, opts.DeferLOBs))
		result.ExecutionTimeMS = time.Since(start).Milliseconds()
		return result, err
	}
	if _, err := s.requireDB(); err != nil {
		return queryResult{}, err
	}
	ctx, cancel := context.WithCancel(s.statementContext())
	var timer *time.Timer
	if opts.TimeoutSecs > 0 {
		var t *time.Timer
		t = time.AfterFunc(time.Duration(opts.TimeoutSecs)*time.Second, func() {
			s.activeCancelMu.Lock()
			if s.activeTimer == t {
				cancel()
			}
			s.activeCancelMu.Unlock()
		})
		timer = t
	}
	s.activeCancelMu.Lock()
	s.activeCancel = cancel
	s.activeTimer = timer
	s.activeCancelMu.Unlock()
	defer func() {
		cancel()
		s.activeCancelMu.Lock()
		s.activeCancel = nil
		if s.activeTimer != nil {
			s.activeTimer.Stop()
			s.activeTimer = nil
		}
		s.activeCancelMu.Unlock()
	}()
	execResult, err := s.execContext(ctx, sqlText)
	if err != nil {
		return queryResult{}, err
	}
	affected, _ := execResult.RowsAffected()
	return queryResult{Columns: []string{}, ColumnTypes: []string{}, Rows: [][]any{}, AffectedRows: affected, ExecutionTimeMS: time.Since(start).Milliseconds()}, nil
}

func (s *server) execContext(ctx context.Context, sqlText string) (sql.Result, error) {
	if s.manualTx != nil {
		return s.manualTx.ExecContext(ctx, sqlText)
	}
	if s.statementConn != nil {
		return s.statementConn.ExecContext(ctx, sqlText)
	}
	db, err := s.requireDB()
	if err != nil {
		return nil, err
	}
	return db.ExecContext(ctx, sqlText)
}

func (s *server) executeSelect(sqlText string, maxRows int, timeoutSecs int, deferLOBs bool) (queryResult, error) {
	return executeOracleSelectWithXMLTypeRetry(
		sqlText,
		func(query string) (queryResult, error) {
			return s.executeSelectOnce(query, maxRows, timeoutSecs, deferLOBs)
		},
		s.rewriteXMLTypeSelectSQL,
	)
}

func executeOracleSelectWithXMLTypeRetry(
	sqlText string,
	execute func(string) (queryResult, error),
	rewrite func(string) (string, error),
) (queryResult, error) {
	result, err := execute(sqlText)
	if err == nil || !shouldRetryOracleXMLTypeRewrite(err) {
		return result, err
	}
	rewritten, rewriteErr := rewrite(sqlText)
	if rewriteErr != nil || rewritten == sqlText {
		return result, err
	}
	return execute(rewritten)
}

func shouldRetryOracleXMLTypeRewrite(err error) bool {
	if err == nil {
		return false
	}
	var decodeErr oracleProtocolDecodeError
	if errors.As(err, &decodeErr) {
		return true
	}
	message := err.Error()
	return strings.Contains(message, "abnormal data representation for date") ||
		strings.Contains(message, "TTC error: received code ")
}

func (s *server) executeSelectOnce(sqlText string, maxRows int, timeoutSecs int, deferLOBs bool) (queryResult, error) {
	rows, err := s.queryRowsWithOracleValueRewriteIfNeeded(sqlText, timeoutSecs, deferLOBs)
	if err != nil {
		return queryResult{}, err
	}
	defer s.closeRows(rows)
	columns, err := rows.Columns()
	if err != nil {
		return queryResult{}, err
	}
	columnTypes := columnTypeNames(rows)
	result := queryResult{Columns: columns, ColumnTypes: columnTypes, Rows: [][]any{}}
	for rows.Next() {
		if len(result.Rows) >= maxRows {
			result.Truncated = true
			break
		}
		values, err := scanRow(rows, len(columns), columnTypes)
		if err != nil {
			return queryResult{}, err
		}
		result.Rows = append(result.Rows, values)
	}
	return result, rows.Err()
}

func scanRow(rows *sql.Rows, columnCount int, columnTypes []string) ([]any, error) {
	values := make([]any, columnCount)
	scanTargets := make([]any, columnCount)
	for i := range values {
		scanTargets[i] = &values[i]
	}
	if err := rows.Scan(scanTargets...); err != nil {
		return nil, err
	}
	for i, value := range values {
		values[i] = normalizeValue(value, columnTypeAt(columnTypes, i))
	}
	return values, nil
}

func columnTypeAt(columnTypes []string, index int) string {
	if index < 0 || index >= len(columnTypes) {
		return ""
	}
	return columnTypes[index]
}

func columnTypeNames(rows *sql.Rows) []string {
	types, err := rows.ColumnTypes()
	if err != nil {
		return []string{}
	}
	result := make([]string, 0, len(types))
	for _, columnType := range types {
		result = append(result, columnType.DatabaseTypeName())
	}
	return result
}

// deferLobsForQuery keeps SQL-level large-value previews in charge. When the
// client already projected __DBX_LARGE_VALUE_BYTES_ marker columns (Oracle
// table-data previews truncate LOBs with DBMS_LOB.SUBSTR in the SQL), those
// expressions must pass through untouched and the nested value rewrite
// (geometry/XMLType/opaque conversions) keeps running as usual, so the
// deferred-LOB placeholder pass must not engage on top of them.
func deferLobsForQuery(sqlText string, requested bool) bool {
	return requested && !strings.Contains(strings.ToUpper(sqlText), largeValueBytesColumnPrefix)
}

func (s *server) queryRowsWithOracleValueRewriteIfNeeded(sqlText string, timeoutSecs int, deferLOBs bool) (resultRows *sql.Rows, queryErr error) {
	// A statement that keeps row locks while it runs is rewritten before the
	// first attempt: when such a statement panics after the rows were locked,
	// the locks stay behind and the retry below blocks on them until the query
	// times out (issues #9344 / #9180). A manual transaction cannot retry at
	// all: the panic leaves its session unusable and the transaction is rolled
	// back (issue #13455).
	if oracleSQLLocksRows(sqlText) || s.hasManualTransaction() {
		rewritten, err := rewriteOracleSelectSQL(sqlText, s.loadOracleColumnMeta, deferLOBs)
		if err == nil && rewritten != sqlText {
			return s.queryRowsWithTimeout(rewritten, nil, timeoutSecs)
		}
		return s.queryRowsWithTimeout(sqlText, nil, timeoutSecs)
	}
	if deferLOBs {
		rewritten, err := rewriteOracleSelectSQL(sqlText, s.loadOracleColumnMeta, true)
		if err == nil && rewritten != sqlText {
			rows, rewrittenErr := s.queryRowsWithTimeout(rewritten, nil, timeoutSecs)
			if rewrittenErr == nil {
				return rows, nil
			}
			// A deferred projection can still read a value the driver cannot
			// decode (user-defined object/collection columns). Only a panic is
			// worth retrying through the value-rewrite fallback below; other
			// failures keep their own error.
			var panicErr oracleDriverPanicError
			if s.hasManualTransaction() || !errors.As(rewrittenErr, &panicErr) {
				return nil, rewrittenErr
			}
		}
	}
	rows, err := s.queryRowsWithTimeout(sqlText, nil, timeoutSecs)
	if err != nil {
		var panicErr oracleDriverPanicError
		if !s.hasManualTransaction() && errors.As(err, &panicErr) {
			rewritten, rewriteErr := rewriteOracleSelectSQL(sqlText, s.loadOracleColumnMeta, false)
			if rewriteErr == nil && rewritten != sqlText {
				rewrittenRows, rewrittenErr := s.queryRowsWithTimeout(rewritten, nil, timeoutSecs)
				if rewrittenErr == nil {
					return rewrittenRows, nil
				}
				// When the server's ArcSDE extproc agent is broken, the
				// SDE.ST_AsText rewrite itself fails with ORA-28595 and would
				// hide the remaining columns; retry once with the placeholder
				// projection so the query stays readable.
				if placeholder, ok := oracleExtprocFallbackSQL(sqlText, s.loadOracleColumnMeta, rewrittenErr); ok {
					if placeholderRows, placeholderErr := s.queryRowsWithTimeout(placeholder, nil, timeoutSecs); placeholderErr == nil {
						return placeholderRows, nil
					}
				}
				return nil, rewrittenErr
			}
		}
		return nil, err
	}
	// Metadata access can panic too. Retain ownership until rows are actually
	// returned; the caller cannot clean up rows it has not received.
	defer func() {
		if rows != resultRows {
			s.closeRows(rows)
		}
	}()
	typeNames := columnTypeNames(rows)
	if !oracleColumnTypeNamesContainXMLType(typeNames) {
		return rows, nil
	}
	rewritten, err := rewriteOracleSelectSQL(sqlText, s.loadOracleColumnMeta, false)
	if err != nil {
		s.closeRows(rows)
		return nil, err
	}
	if rewritten == sqlText {
		return rows, nil
	}
	// XMLTYPE keeps its metadata-triggered fallback so ordinary non-preview
	// queries do not run dictionary probes.
	s.closeRows(rows)
	return s.queryRowsWithTimeout(rewritten, nil, timeoutSecs)
}

func oracleColumnTypeNamesContainXMLType(typeNames []string) bool {
	for _, typeName := range typeNames {
		if isOracleXMLType(typeName) {
			return true
		}
	}
	return false
}

// isOracleExtprocAgentFailure reports whether err is an Oracle extproc agent
// failure (ORA-28595, typically raised through SDE.ST_GEOMETRY_SHAPELIB_PKG or
// SDE.ST_GEOMETRY_OPERATORS) that makes every SDE.ST_GEOMETRY function call
// unusable on the server. Matching stays deliberately narrow: the ORA code
// must be present; SDE package names alone never trigger it.
func isOracleExtprocAgentFailure(err error) bool {
	if err == nil {
		return false
	}
	// Accept both the "ORA-28595" form and a bare "28595" code.
	return strings.Contains(strings.ToUpper(err.Error()), "28595")
}

// oracleExtprocFallbackSQL returns the placeholder projection to retry with
// when the panic-fallback SDE.ST_AsText rewrite failed because of a broken
// extproc agent (ORA-28595). It reports false when the error is unrelated or
// no placeholder rewrite applies.
func oracleExtprocFallbackSQL(sqlText string, loadColumns oracleColumnMetaLoader, rewriteErr error) (string, bool) {
	if !isOracleExtprocAgentFailure(rewriteErr) {
		return "", false
	}
	placeholder, err := rewriteOracleSelectSQL(sqlText, loadColumns, true)
	if err != nil || placeholder == sqlText {
		return "", false
	}
	return placeholder, true
}

func (s *server) rewriteXMLTypeSelectSQL(sqlText string) (string, error) {
	return rewriteOracleXMLTypeSelectSQL(sqlText, s.loadOracleColumnMeta)
}

func rewriteOracleXMLTypeSelectSQL(sqlText string, loadColumns oracleColumnMetaLoader) (string, error) {
	return rewriteOracleSelectSQL(sqlText, loadColumns, false)
}

func rewriteOracleSelectSQL(sqlText string, loadColumns oracleColumnMetaLoader, deferLOBs bool) (string, error) {
	rewritten, _, err := rewriteOracleSelectSQLDepth(sqlText, loadColumns, deferLOBs, 0)
	return rewritten, err
}

func rewriteOracleSelectSQLDepth(sqlText string, loadColumns oracleColumnMetaLoader, deferLOBs bool, depth int) (string, bool, error) {
	if depth > 8 {
		return sqlText, false, nil
	}
	if rewritten, changed, handled, err := rewriteDirectOracleSelectSQL(sqlText, loadColumns, deferLOBs); handled || err != nil {
		return rewritten, changed, err
	}
	if deferLOBs {
		return rewriteOracleFullPassthroughInnerSelect(sqlText, loadColumns, depth)
	}
	rewritten, changed, err := rewriteNestedOracleSelects(sqlText, loadColumns, deferLOBs, depth)
	return rewritten, changed, err
}

func rewriteOracleFullPassthroughInnerSelect(sqlText string, loadColumns oracleColumnMetaLoader, depth int) (string, bool, error) {
	innerStart, innerEnd, ok := oracleFullPassthroughInnerSelectRange(sqlText)
	if !ok {
		return sqlText, false, nil
	}
	inner := sqlText[innerStart:innerEnd]
	rewritten, changed, err := rewriteOracleSelectSQLDepth(inner, loadColumns, true, depth+1)
	if err != nil || !changed {
		return sqlText, false, err
	}
	return sqlText[:innerStart] + rewritten + sqlText[innerEnd:], true, nil
}

func oracleFullPassthroughInnerSelectRange(sqlText string) (int, int, bool) {
	selectStart := leadingSQLSelectListStart(sqlText)
	if selectStart < 0 {
		return 0, 0, false
	}
	fromIdx := findTopLevelSQLKeyword(sqlText, selectStart, "from")
	if fromIdx < 0 {
		return 0, 0, false
	}
	prefix, selectList := splitOracleSelectListModifier(sqlText[selectStart:fromIdx])
	if strings.TrimSpace(prefix) != "" {
		return 0, 0, false
	}
	items := splitTopLevelSQLList(selectList)
	if len(items) != 1 {
		return 0, 0, false
	}
	if _, ok := parseOracleStarSelectItem(items[0]); !ok {
		return 0, 0, false
	}
	open := skipSQLWhitespace(sqlText, fromIdx+len("from"))
	if open >= len(sqlText) || sqlText[open] != '(' {
		return 0, 0, false
	}
	close := findMatchingSQLParen(sqlText, open)
	if close < 0 || !startsWithSQLKeyword(trimLeadingSQLComments(sqlText[open+1:close]), "select") {
		return 0, 0, false
	}
	pos := skipSQLWhitespace(sqlText, close+1)
	if pos < len(sqlText) && sqlText[pos] != ';' && !nextKeywordIsOracleClause(sqlText[pos:]) {
		_, afterAlias, aliasOK := readOracleIdentifierToken(sqlText, pos)
		if !aliasOK {
			return 0, 0, false
		}
		pos = skipSQLWhitespace(sqlText, afterAlias)
	}
	if pos < len(sqlText) && sqlText[pos] != ';' && !nextKeywordIsOracleClause(sqlText[pos:]) {
		return 0, 0, false
	}
	return open + 1, close, true
}

func rewriteNestedOracleSelects(sqlText string, loadColumns oracleColumnMetaLoader, deferLOBs bool, depth int) (string, bool, error) {
	var builder strings.Builder
	changed := false
	last := 0
	for pos := 0; pos < len(sqlText); pos++ {
		switch sqlText[pos] {
		case '\'':
			pos = skipSingleQuotedSQL(sqlText, pos)
		case '"':
			pos = skipDoubleQuotedSQL(sqlText, pos)
		case '-':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '-' {
				pos = skipLineCommentSQL(sqlText, pos)
			}
		case '/':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '*' {
				pos = skipBlockCommentSQL(sqlText, pos)
			}
		case '(':
			end := findMatchingSQLParen(sqlText, pos)
			if end < 0 {
				return sqlText, false, nil
			}
			inner := sqlText[pos+1 : end]
			if startsWithSQLKeyword(trimLeadingSQLComments(inner), "select") {
				rewrittenInner, innerChanged, err := rewriteOracleSelectSQLDepth(inner, loadColumns, deferLOBs, depth+1)
				if err != nil {
					return "", false, err
				}
				if innerChanged {
					builder.WriteString(sqlText[last : pos+1])
					builder.WriteString(rewrittenInner)
					last = end
					changed = true
				}
			}
			pos = end
		}
	}
	if !changed {
		return sqlText, false, nil
	}
	builder.WriteString(sqlText[last:])
	return builder.String(), true, nil
}

func rewriteDirectOracleSelectSQL(sqlText string, loadColumns oracleColumnMetaLoader, deferLOBs bool) (string, bool, bool, error) {
	selectStart := leadingSQLSelectListStart(sqlText)
	if selectStart < 0 {
		return sqlText, false, false, nil
	}
	fromIdx := findTopLevelSQLKeyword(sqlText, selectStart, "from")
	if fromIdx < 0 {
		return sqlText, false, false, nil
	}
	if oracleSQLHasTopLevelSetOperator(sqlText, fromIdx+len("from")) {
		return sqlText, false, false, nil
	}
	selectListPrefix, selectList := splitOracleSelectListModifier(sqlText[selectStart:fromIdx])
	deferSelectLOBs := deferLOBs && !startsWithSQLKeyword(strings.TrimSpace(selectListPrefix), "distinct")
	tableRef, ok := parseSingleOracleTableRef(sqlText[fromIdx+len("from"):])
	if !ok {
		return sqlText, false, false, nil
	}
	items := splitTopLevelSQLList(selectList)
	if len(items) == 0 || !oracleSelectListMayReferenceTableColumns(items) {
		return sqlText, false, true, nil
	}
	columns, err := loadColumns(tableRef.Schema, tableRef.Table)
	if err != nil {
		return "", false, true, err
	}
	if deferSelectLOBs && oracleColumnsConflictWithLargeValueMarkers(columns) {
		deferSelectLOBs = false
	}
	if !oracleColumnsNeedValueRewrite(columns, deferSelectLOBs) {
		return sqlText, false, true, nil
	}
	rewrittenItems, changed := rewriteOracleSelectItems(items, columns, tableRef, deferSelectLOBs)
	if !changed {
		return sqlText, false, true, nil
	}
	var builder strings.Builder
	builder.WriteString(sqlText[:selectStart])
	builder.WriteString(selectListPrefix)
	builder.WriteString(strings.Join(rewrittenItems, ", "))
	builder.WriteByte(' ')
	builder.WriteString(sqlText[fromIdx:])
	return builder.String(), true, true, nil
}

type oracleTableRef struct {
	Schema    string
	Table     string
	Alias     string
	AliasText string
}

type oracleIdentifierToken struct {
	Name   string
	Text   string
	Quoted bool
}

func parseSingleOracleTableRef(fromSQL string) (oracleTableRef, bool) {
	pos := skipSQLWhitespace(fromSQL, 0)
	if pos >= len(fromSQL) || fromSQL[pos] == '(' {
		return oracleTableRef{}, false
	}
	first, next, ok := readOracleIdentifierToken(fromSQL, pos)
	if !ok {
		return oracleTableRef{}, false
	}
	ref := oracleTableRef{Table: first.Name}
	pos = skipSQLWhitespace(fromSQL, next)
	if pos < len(fromSQL) && fromSQL[pos] == '.' {
		second, afterSecond, ok := readOracleIdentifierToken(fromSQL, skipSQLWhitespace(fromSQL, pos+1))
		if !ok {
			return oracleTableRef{}, false
		}
		ref.Schema = first.Name
		ref.Table = second.Name
		pos = skipSQLWhitespace(fromSQL, afterSecond)
	}
	pos = parseOracleDatabaseLinkSuffix(fromSQL, pos)
	if pos < len(fromSQL) {
		if strings.HasPrefix(strings.TrimLeft(fromSQL[pos:], " \t\r\n"), ",") {
			return oracleTableRef{}, false
		}
		if nextKeywordIsOracleClause(fromSQL[pos:]) {
			return ref, true
		}
		if startsWithSQLKeyword(fromSQL[pos:], "join") ||
			startsWithSQLKeyword(fromSQL[pos:], "inner") ||
			startsWithSQLKeyword(fromSQL[pos:], "left") ||
			startsWithSQLKeyword(fromSQL[pos:], "right") ||
			startsWithSQLKeyword(fromSQL[pos:], "full") ||
			startsWithSQLKeyword(fromSQL[pos:], "cross") {
			return oracleTableRef{}, false
		}
		alias, afterAlias, ok := readOracleIdentifierToken(fromSQL, pos)
		if ok && !oracleIdentifierIsClause(alias.Name) {
			ref.Alias = alias.Name
			ref.AliasText = alias.Text
			pos = skipSQLWhitespace(fromSQL, afterAlias)
		}
		if strings.HasPrefix(strings.TrimLeft(fromSQL[pos:], " \t\r\n"), ",") ||
			startsWithSQLKeyword(fromSQL[pos:], "join") ||
			startsWithSQLKeyword(fromSQL[pos:], "inner") ||
			startsWithSQLKeyword(fromSQL[pos:], "left") ||
			startsWithSQLKeyword(fromSQL[pos:], "right") ||
			startsWithSQLKeyword(fromSQL[pos:], "full") ||
			startsWithSQLKeyword(fromSQL[pos:], "cross") {
			return oracleTableRef{}, false
		}
	}
	return ref, true
}

// parseOracleDatabaseLinkSuffix consumes the `@link` (or `@!link`) suffix of a
// remote table reference so that the alias written after the link is still
// recognized (issues #9171 / #9344). Without it `schema.table@link t` parsed as
// an unaliased reference, so a `t.*` / `t.column` projection no longer matched
// the table and go-ora panicked on the user-defined type columns the value
// rewrite is meant to substitute.
//
// A suffix that cannot be read as a link name is left in place, which keeps the
// previous shape for connect-string links and other unparsable spellings.
func parseOracleDatabaseLinkSuffix(fromSQL string, pos int) int {
	if pos >= len(fromSQL) || fromSQL[pos] != '@' {
		return pos
	}
	linkStart := pos + 1
	if linkStart < len(fromSQL) && fromSQL[linkStart] == '!' {
		// `table@!link` keeps the same link name; the marker only changes where
		// the link itself is resolved.
		linkStart++
	}
	if _, afterLink, ok := readOracleIdentifierToken(fromSQL, linkStart); ok {
		return skipSQLWhitespace(fromSQL, afterLink)
	}
	return pos
}

func splitOracleSelectListModifier(selectList string) (string, string) {
	trimmedLeft := strings.TrimLeft(selectList, " \t\r\n")
	prefixLen := len(selectList) - len(trimmedLeft)
	for _, keyword := range []string{"distinct", "all"} {
		if startsWithSQLKeyword(trimmedLeft, keyword) {
			modifierEnd := prefixLen + len(keyword)
			for modifierEnd < len(selectList) && isSQLWhitespace(selectList[modifierEnd]) {
				modifierEnd++
			}
			return selectList[:modifierEnd], selectList[modifierEnd:]
		}
	}
	return selectList[:prefixLen], selectList[prefixLen:]
}

func oracleSQLHasTopLevelSetOperator(sqlText string, start int) bool {
	for _, keyword := range []string{"union", "minus", "intersect"} {
		if findTopLevelSQLKeyword(sqlText, start, keyword) >= 0 {
			return true
		}
	}
	return false
}

func oracleSelectListMayReferenceTableColumns(items []string) bool {
	for _, item := range items {
		if _, ok := parseOracleStarSelectItem(item); ok {
			return true
		}
		if _, _, _, ok := parseOracleColumnSelectItem(item); ok {
			return true
		}
	}
	return false
}

func rewriteOracleSelectItems(items []string, columns []oracleColumnMeta, tableRef oracleTableRef, deferLOBs bool) ([]string, bool) {
	columnsByName := map[string]oracleColumnMeta{}
	for _, column := range columns {
		columnsByName[oracleIdentifierKey(column.Name)] = column
	}
	rewritten := make([]string, 0, len(items))
	changed := false
	sourceIndex := 0
	for _, item := range items {
		if qualifier, ok := parseOracleStarSelectItem(item); ok && oracleQualifierMatchesTable(qualifier, tableRef) {
			for _, column := range columns {
				columnRef := oracleColumnRef(tableRef.AliasText, column.Name)
				outputAlias := quoteIdentifier(column.Name)
				if isOracleGeometryType(column) && !deferLOBs {
					rewritten = append(rewritten, oracleGeometryExpression(column, columnRef, outputAlias))
				} else if isOracleXMLType(column.DataType) && !deferLOBs {
					rewritten = append(rewritten, oracleXMLSerializeExpression(columnRef, outputAlias))
				} else if isOracleOpaqueObjectType(column) {
					rewritten = append(rewritten, oracleOpaqueObjectExpression(column, columnRef, outputAlias))
				} else if deferLOBs {
					if expressions, ok := oracleDeferredLOBExpressions(columnRef, outputAlias, sourceIndex, column); ok {
						rewritten = append(rewritten, expressions...)
					} else {
						rewritten = append(rewritten, columnRef)
					}
				} else {
					rewritten = append(rewritten, columnRef)
				}
				sourceIndex++
			}
			changed = true
			continue
		}
		qualifier, column, alias, ok := parseOracleColumnSelectItem(item)
		if ok && oracleQualifierMatchesTable(qualifier, tableRef) {
			if meta, exists := columnsByName[oracleIdentifierKey(column.Name)]; exists {
				outputAlias := alias
				if outputAlias == "" {
					outputAlias = quoteIdentifier(meta.Name)
				}
				columnRef := oracleColumnRef(qualifier, meta.Name)
				if isOracleGeometryType(meta) && !deferLOBs {
					rewritten = append(rewritten, oracleGeometryExpression(meta, columnRef, outputAlias))
					changed = true
					sourceIndex++
					continue
				}
				if isOracleXMLType(meta.DataType) && !deferLOBs {
					rewritten = append(rewritten, oracleXMLSerializeExpression(columnRef, outputAlias))
					changed = true
					sourceIndex++
					continue
				}
				if isOracleOpaqueObjectType(meta) {
					rewritten = append(rewritten, oracleOpaqueObjectExpression(meta, columnRef, outputAlias))
					changed = true
					sourceIndex++
					continue
				}
				if deferLOBs {
					if expressions, isLOB := oracleDeferredLOBExpressions(columnRef, outputAlias, sourceIndex, meta); isLOB {
						rewritten = append(rewritten, expressions...)
						changed = true
						sourceIndex++
						continue
					}
				}
			}
		}
		rewritten = append(rewritten, item)
		sourceIndex++
	}
	return rewritten, changed
}

func oracleDeferredLOBExpressions(columnRef, outputAlias string, sourceIndex int, column oracleColumnMeta) ([]string, bool) {
	kind, placeholder, ok := oracleDeferredLOBKind(column.DataType)
	valueRef := columnRef
	if isOracleGeometryType(column) {
		kind, placeholder, ok = "C", oracleGeometryPlaceholder(column), true
	}
	if !ok {
		return nil, false
	}
	valueExpression := fmt.Sprintf("CASE WHEN %s IS NULL THEN NULL ELSE '%s' END AS %s", valueRef, placeholder, outputAlias)
	markerAlias := fmt.Sprintf("%s%s_%d", largeValueBytesColumnPrefix, kind, sourceIndex)
	markerExpression := fmt.Sprintf("CASE WHEN %s IS NULL THEN NULL ELSE 'D:1' END AS %s", valueRef, quoteIdentifier(markerAlias))
	return []string{valueExpression, markerExpression}, true
}

func oracleSTGeometryExpression(columnRef, alias string) string {
	return fmt.Sprintf("SDE.ST_AsText(%s) AS %s", columnRef, alias)
}

func oracleSDOGeometryExpression(columnRef, alias string) string {
	return fmt.Sprintf("SDO_UTIL.TO_WKTGEOMETRY(%s) AS %s", columnRef, alias)
}

func oracleGeometryExpression(column oracleColumnMeta, columnRef, alias string) string {
	if isOracleSDOGeometry(column) {
		return oracleSDOGeometryExpression(columnRef, alias)
	}
	return oracleSTGeometryExpression(columnRef, alias)
}

func oracleGeometryPlaceholder(column oracleColumnMeta) string {
	if isOracleSDOGeometry(column) {
		return "<SDO_GEOMETRY>"
	}
	return "<ST_GEOMETRY>"
}

// oracleOpaqueObjectExpression projects a placeholder instead of the value of a
// user-defined type (object, collection, ANYDATA, ...). go-ora cannot decode
// those values over the wire and panics while scanning them, which fails the
// whole statement and rolls the transaction back (issues #9344 / #9180). The
// placeholder keeps the statement and the remaining columns readable.
func oracleOpaqueObjectExpression(column oracleColumnMeta, columnRef, alias string) string {
	return fmt.Sprintf("CASE WHEN %s IS NULL THEN NULL ELSE '%s' END AS %s", columnRef, oracleOpaqueObjectPlaceholder(column), alias)
}

func oracleOpaqueObjectPlaceholder(column oracleColumnMeta) string {
	name := strings.ToUpper(strings.TrimSpace(column.DataType))
	if name == "" {
		name = "OBJECT"
	}
	return "<" + name + ">"
}

// oracleSQLLocksRows reports whether a statement holds row locks while it runs
// (FOR UPDATE, FOR UPDATE OF ..., NOWAIT / WAIT n / SKIP LOCKED). Such a
// statement must not be retried after a driver panic: the failed attempt keeps
// its locks, so the retry would block on them instead of returning.
func oracleSQLLocksRows(sqlText string) bool {
	for searchFrom := 0; searchFrom < len(sqlText); {
		pos := findTopLevelSQLKeyword(sqlText, searchFrom, "for")
		if pos < 0 {
			return false
		}
		if sqlKeywordAt(sqlText, skipSQLWhitespace(sqlText, pos+len("for")), "update") {
			return true
		}
		searchFrom = pos + len("for")
	}
	return false
}

func oracleDeferredLOBKind(dataType string) (kind, placeholder string, ok bool) {
	switch strings.ToUpper(strings.TrimSpace(dataType)) {
	case "CLOB":
		return "C", "<CLOB>", true
	case "NCLOB":
		return "N", "<NCLOB>", true
	case "BLOB":
		return "L", "<BLOB>", true
	case "BFILE":
		return "F", "<BFILE>", true
	case "XMLTYPE", "SYS.XMLTYPE":
		return "C", "<XMLTYPE>", true
	default:
		return "", "", false
	}
}

func oracleXMLSerializeExpression(columnRef, alias string) string {
	// go-ora v2.9.0 does not fully decode Oracle XMLTYPE result payloads,
	// especially when 11g switches larger values to locator-based transfer.
	return fmt.Sprintf("XMLSERIALIZE(CONTENT %s AS CLOB) AS %s", columnRef, alias)
}

func oracleColumnRef(qualifier, column string) string {
	if strings.TrimSpace(qualifier) == "" {
		return quoteIdentifier(column)
	}
	return qualifier + "." + quoteIdentifier(column)
}

func parseOracleStarSelectItem(item string) (string, bool) {
	trimmed := strings.TrimSpace(item)
	if trimmed == "*" {
		return "", true
	}
	qualifier, pos, ok := readOracleIdentifierToken(trimmed, 0)
	if !ok {
		return "", false
	}
	pos = skipSQLWhitespace(trimmed, pos)
	if pos >= len(trimmed) || trimmed[pos] != '.' {
		return "", false
	}
	pos = skipSQLWhitespace(trimmed, pos+1)
	if pos < len(trimmed) && trimmed[pos] == '*' && strings.TrimSpace(trimmed[pos+1:]) == "" {
		return qualifier.Text, true
	}
	return "", false
}

func parseOracleColumnSelectItem(item string) (qualifier string, column oracleIdentifierToken, alias string, ok bool) {
	trimmed := strings.TrimSpace(item)
	first, pos, ok := readOracleIdentifierToken(trimmed, 0)
	if !ok {
		return "", oracleIdentifierToken{}, "", false
	}
	column = first
	pos = skipSQLWhitespace(trimmed, pos)
	if pos < len(trimmed) && trimmed[pos] == '.' {
		second, afterSecond, ok := readOracleIdentifierToken(trimmed, skipSQLWhitespace(trimmed, pos+1))
		if !ok {
			return "", oracleIdentifierToken{}, "", false
		}
		qualifier = first.Text
		column = second
		pos = skipSQLWhitespace(trimmed, afterSecond)
	}
	if pos >= len(trimmed) {
		return qualifier, column, "", true
	}
	if startsWithSQLKeyword(trimmed[pos:], "as") {
		aliasToken, afterAlias, ok := readOracleIdentifierToken(trimmed, skipSQLWhitespace(trimmed, pos+len("as")))
		if !ok || strings.TrimSpace(trimmed[afterAlias:]) != "" {
			return "", oracleIdentifierToken{}, "", false
		}
		return qualifier, column, aliasToken.Text, true
	}
	aliasToken, afterAlias, ok := readOracleIdentifierToken(trimmed, pos)
	if !ok || strings.TrimSpace(trimmed[afterAlias:]) != "" {
		return "", oracleIdentifierToken{}, "", false
	}
	return qualifier, column, aliasToken.Text, true
}

func oracleQualifierMatchesTable(qualifier string, tableRef oracleTableRef) bool {
	if strings.TrimSpace(qualifier) == "" {
		return true
	}
	key := oracleIdentifierKey(unquoteOracleIdentifierText(qualifier))
	if tableRef.Alias != "" && key == oracleIdentifierKey(tableRef.Alias) {
		return true
	}
	return key == oracleIdentifierKey(tableRef.Table)
}

func oracleColumnsNeedValueRewrite(columns []oracleColumnMeta, deferLOBs bool) bool {
	for _, column := range columns {
		if isOracleGeometryType(column) || isOracleXMLType(column.DataType) {
			return true
		}
		if deferLOBs && isOracleDeferredLOBType(column.DataType) {
			return true
		}
		// Opaque user-defined types are substituted in both the plain and the
		// deferred preview projection, so a preview never panics on them.
		if isOracleOpaqueObjectType(column) {
			return true
		}
	}
	return false
}

func oracleColumnsConflictWithLargeValueMarkers(columns []oracleColumnMeta) bool {
	for _, column := range columns {
		if strings.HasPrefix(strings.ToUpper(column.Name), largeValueBytesColumnPrefix) {
			return true
		}
	}
	return false
}

func isOracleDeferredLOBType(dataType string) bool {
	_, _, ok := oracleDeferredLOBKind(dataType)
	return ok
}

func isOracleXMLType(dataType string) bool {
	normalized := strings.ToUpper(strings.TrimSpace(dataType))
	return normalized == "XMLTYPE" || normalized == "SYS.XMLTYPE"
}

func isOracleSTGeometry(column oracleColumnMeta) bool {
	// Esri's SDE.ST_GEOMETRY is an unregistered Oracle object for go-ora;
	// SDE.ST_AsText provides the supported CLOB representation for reads.
	dataType := strings.ToUpper(strings.TrimSpace(column.DataType))
	if dataType == "SDE.ST_GEOMETRY" {
		return true
	}
	return dataType == "ST_GEOMETRY" && strings.EqualFold(strings.TrimSpace(column.DataTypeOwner), "SDE")
}

func isOracleSDOGeometry(column oracleColumnMeta) bool {
	// Oracle Spatial's MDSYS.SDO_GEOMETRY is likewise an unregistered object
	// type for go-ora and panics on scan; SDO_UTIL.TO_WKTGEOMETRY provides
	// the supported CLOB (WKT) representation for reads. Most schemas only
	// see it through the PUBLIC synonym, so DATA_TYPE_OWNER is commonly
	// "PUBLIC" rather than "MDSYS" and isn't checked here.
	return strings.ToUpper(strings.TrimSpace(column.DataType)) == "SDO_GEOMETRY"
}

func isOracleGeometryType(column oracleColumnMeta) bool {
	return isOracleSTGeometry(column) || isOracleSDOGeometry(column)
}

// isOracleOpaqueObjectType reports whether a column's declared type is a named
// user-defined type that the driver cannot decode. Built-in scalar types report
// no DATA_TYPE_OWNER; named types (object, collection, ANYDATA, ...) do. Types
// DBX is able to render — SDE/SDO geometry, XMLType and deferred LOBs — are
// matched earlier, so they are never treated as opaque here.
func isOracleOpaqueObjectType(column oracleColumnMeta) bool {
	if isOracleGeometryType(column) || isOracleXMLType(column.DataType) || isOracleDeferredLOBType(column.DataType) {
		return false
	}
	return strings.TrimSpace(column.DataTypeOwner) != ""
}

func leadingSQLSelectListStart(sqlText string) int {
	trimmed := trimLeadingSQLComments(sqlText)
	prefixLen := len(sqlText) - len(trimmed)
	if !startsWithSQLKeyword(trimmed, "select") {
		return -1
	}
	return prefixLen + len("select")
}

func splitTopLevelSQLList(value string) []string {
	var result []string
	start := 0
	depth := 0
	for pos := 0; pos < len(value); pos++ {
		switch value[pos] {
		case '\'':
			pos = skipSingleQuotedSQL(value, pos)
		case '"':
			pos = skipDoubleQuotedSQL(value, pos)
		case '-':
			if pos+1 < len(value) && value[pos+1] == '-' {
				pos = skipLineCommentSQL(value, pos)
			}
		case '/':
			if pos+1 < len(value) && value[pos+1] == '*' {
				pos = skipBlockCommentSQL(value, pos)
			}
		case '(':
			depth++
		case ')':
			if depth > 0 {
				depth--
			}
		case ',':
			if depth == 0 {
				result = append(result, strings.TrimSpace(value[start:pos]))
				start = pos + 1
			}
		}
	}
	tail := strings.TrimSpace(value[start:])
	if tail != "" {
		result = append(result, tail)
	}
	return result
}

func findTopLevelSQLKeyword(sqlText string, start int, keyword string) int {
	depth := 0
	for pos := start; pos < len(sqlText); pos++ {
		switch sqlText[pos] {
		case '\'':
			pos = skipSingleQuotedSQL(sqlText, pos)
		case '"':
			pos = skipDoubleQuotedSQL(sqlText, pos)
		case '-':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '-' {
				pos = skipLineCommentSQL(sqlText, pos)
			}
		case '/':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '*' {
				pos = skipBlockCommentSQL(sqlText, pos)
			}
		case '(':
			depth++
		case ')':
			if depth > 0 {
				depth--
			}
		default:
			if depth == 0 && sqlKeywordAt(sqlText, pos, keyword) {
				return pos
			}
		}
	}
	return -1
}

func findMatchingSQLParen(sqlText string, open int) int {
	depth := 0
	for pos := open; pos < len(sqlText); pos++ {
		switch sqlText[pos] {
		case '\'':
			pos = skipSingleQuotedSQL(sqlText, pos)
		case '"':
			pos = skipDoubleQuotedSQL(sqlText, pos)
		case '-':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '-' {
				pos = skipLineCommentSQL(sqlText, pos)
			}
		case '/':
			if pos+1 < len(sqlText) && sqlText[pos+1] == '*' {
				pos = skipBlockCommentSQL(sqlText, pos)
			}
		case '(':
			depth++
		case ')':
			depth--
			if depth == 0 {
				return pos
			}
		}
	}
	return -1
}

func readOracleIdentifierToken(value string, pos int) (oracleIdentifierToken, int, bool) {
	pos = skipSQLWhitespace(value, pos)
	if pos >= len(value) {
		return oracleIdentifierToken{}, pos, false
	}
	if value[pos] == '"' {
		end := pos + 1
		var builder strings.Builder
		for end < len(value) {
			if value[end] == '"' {
				if end+1 < len(value) && value[end+1] == '"' {
					builder.WriteByte('"')
					end += 2
					continue
				}
				return oracleIdentifierToken{Name: builder.String(), Text: value[pos : end+1], Quoted: true}, end + 1, true
			}
			builder.WriteByte(value[end])
			end++
		}
		return oracleIdentifierToken{}, pos, false
	}
	if !isOracleIdentifierStart(value[pos]) {
		return oracleIdentifierToken{}, pos, false
	}
	end := pos + 1
	for end < len(value) && isOracleIdentifierPart(value[end]) {
		end++
	}
	text := value[pos:end]
	return oracleIdentifierToken{Name: strings.ToUpper(text), Text: text}, end, true
}

func unquoteOracleIdentifierText(value string) string {
	value = strings.TrimSpace(value)
	if len(value) >= 2 && value[0] == '"' && value[len(value)-1] == '"' {
		return strings.ReplaceAll(value[1:len(value)-1], `""`, `"`)
	}
	return strings.ToUpper(value)
}

func oracleIdentifierKey(value string) string {
	return strings.ToUpper(strings.TrimSpace(value))
}

func oracleIdentifierIsClause(value string) bool {
	switch oracleIdentifierKey(value) {
	case "WHERE", "GROUP", "ORDER", "HAVING", "CONNECT", "START", "MODEL", "FETCH", "OFFSET", "FOR", "AS", "UNION", "MINUS", "INTERSECT":
		return true
	default:
		return false
	}
}

func nextKeywordIsOracleClause(value string) bool {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return true
	}
	token, _, ok := readOracleIdentifierToken(trimmed, 0)
	return ok && oracleIdentifierIsClause(token.Name)
}

func isOracleIdentifierStart(ch byte) bool {
	return (ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z') || ch == '_' || ch == '$' || ch == '#'
}

func isOracleIdentifierPart(ch byte) bool {
	return isOracleIdentifierStart(ch) || (ch >= '0' && ch <= '9')
}

func skipSQLWhitespace(value string, pos int) int {
	for pos < len(value) && isSQLWhitespace(value[pos]) {
		pos++
	}
	return pos
}

func isSQLWhitespace(ch byte) bool {
	return ch == ' ' || ch == '\t' || ch == '\r' || ch == '\n'
}

func skipSingleQuotedSQL(value string, pos int) int {
	pos++
	for pos < len(value) {
		if value[pos] == '\'' {
			if pos+1 < len(value) && value[pos+1] == '\'' {
				pos += 2
				continue
			}
			return pos
		}
		pos++
	}
	return len(value) - 1
}

func skipOracleAlternativeQuotedSQL(value string, pos int) (int, bool) {
	if pos+2 >= len(value) || (value[pos] != 'q' && value[pos] != 'Q') || value[pos+1] != '\'' {
		return pos, false
	}
	open := value[pos+2]
	close := open
	switch open {
	case '[':
		close = ']'
	case '{':
		close = '}'
	case '(':
		close = ')'
	case '<':
		close = '>'
	}
	for end := pos + 3; end+1 < len(value); end++ {
		if value[end] == close && value[end+1] == '\'' {
			return end + 1, true
		}
	}
	return len(value) - 1, true
}

func skipDoubleQuotedSQL(value string, pos int) int {
	pos++
	for pos < len(value) {
		if value[pos] == '"' {
			if pos+1 < len(value) && value[pos+1] == '"' {
				pos += 2
				continue
			}
			return pos
		}
		pos++
	}
	return len(value) - 1
}

func skipLineCommentSQL(value string, pos int) int {
	for pos < len(value) {
		if value[pos] == '\n' || value[pos] == '\r' {
			return pos
		}
		pos++
	}
	return len(value) - 1
}

func skipBlockCommentSQL(value string, pos int) int {
	end := strings.Index(value[pos+2:], "*/")
	if end < 0 {
		return len(value) - 1
	}
	return pos + end + 3
}

func (s *server) setSchema(schema string) error {
	sqlText := "ALTER SESSION SET CURRENT_SCHEMA = " + quoteIdentifier(schema)
	if s.manualTx != nil {
		_, err := s.manualTx.Exec(sqlText)
		return err
	}
	if s.manualConn != nil {
		_, err := s.manualConn.ExecContext(context.Background(), sqlText)
		return err
	}
	if s.statementConn != nil {
		_, err := s.statementConn.ExecContext(s.statementContext(), sqlText)
		if err == nil {
			s.statementSchema = schema
		}
		return err
	}
	db, err := s.requireDB()
	if err != nil {
		return err
	}
	_, err = db.Exec(sqlText)
	return err
}

// repinPoisonedStatementConn replaces the statement's pinned connection once a
// driver panic or a response decode error has poisoned it, so the fallbacks of
// the same statement do not fail on the dead session (issues #13455, #13667).
// The fresh connection gets the statement's CURRENT_SCHEMA back; the
// statement's owner closes it together with the original one.
func (s *server) repinPoisonedStatementConn() error {
	if s.manualTx != nil || s.statementConn == nil || !oracleConnPoisoned(s.statementConn) {
		return nil
	}
	db, err := s.requireDB()
	if err != nil {
		return err
	}
	conn, err := db.Conn(s.statementContext())
	if err != nil {
		return err
	}
	if s.statementSchema != "" {
		if _, err := conn.ExecContext(s.statementContext(), "ALTER SESSION SET CURRENT_SCHEMA = "+quoteIdentifier(s.statementSchema)); err != nil {
			_ = conn.Close()
			return err
		}
	}
	if s.repinnedStatementConn != nil {
		_ = s.repinnedStatementConn.Close()
	}
	s.repinnedStatementConn = conn
	s.statementConn = conn
	return nil
}

// takeRepinnedStatementConn hands a connection opened by
// repinPoisonedStatementConn to the statement's owner.
func (s *server) takeRepinnedStatementConn() *sql.Conn {
	conn := s.repinnedStatementConn
	s.repinnedStatementConn = nil
	s.statementSchema = ""
	return conn
}

func (s *server) queryRows(sqlText string, args []any) (*sql.Rows, error) {
	return s.queryRowsWithTimeout(sqlText, args, 0)
}

func (s *server) queryRowsWithTimeout(sqlText string, args []any, timeoutSecs int) (rows *sql.Rows, queryErr error) {
	if _, err := s.requireDB(); err != nil {
		return nil, err
	}
	if err := s.repinPoisonedStatementConn(); err != nil {
		return nil, err
	}
	ctx, cancel := context.WithCancel(s.statementContext())
	var timer *time.Timer
	if timeoutSecs > 0 {
		var t *time.Timer
		t = time.AfterFunc(time.Duration(timeoutSecs)*time.Second, func() {
			s.activeCancelMu.Lock()
			if s.activeTimer == t {
				s.activeTimedOut = true
				cancel()
			}
			s.activeCancelMu.Unlock()
		})
		timer = t
	}
	s.activeCancelMu.Lock()
	s.activeCancel = cancel
	s.activeTimer = timer
	s.activeTimedOut = false
	s.activeCancelMu.Unlock()
	defer func() {
		if recovered := recover(); recovered != nil {
			cancel()
			if timer != nil {
				timer.Stop()
			}
			if rows != nil {
				_ = rows.Close()
			}
			s.activeCancelMu.Lock()
			s.activeCancel = nil
			s.activeTimer = nil
			s.activeTimedOut = false
			if rows != nil {
				delete(s.activeRows, rows)
			}
			s.activeCancelMu.Unlock()
			rows = nil
			queryErr = oracleDriverPanicError{value: recovered}
		}
	}()
	if s.manualTx != nil {
		rows, queryErr = s.manualTx.QueryContext(ctx, sqlText, args...)
	} else if s.statementConn != nil {
		rows, queryErr = s.statementConn.QueryContext(ctx, sqlText, args...)
	} else {
		db, err := s.requireDB()
		if err != nil {
			cancel()
			return nil, err
		}
		rows, queryErr = db.QueryContext(ctx, sqlText, args...)
	}
	s.activeCancelMu.Lock()
	s.activeCancel = nil
	if s.activeTimer != nil {
		s.activeTimer.Stop()
		s.activeTimer = nil
	}
	timedOut := s.activeTimedOut
	if queryErr != nil {
		cancel()
	} else if timedOut {
		cancel()
		if rows != nil {
			rows.Close()
		}
		queryErr = fmt.Errorf("query timed out after %ds", timeoutSecs)
	} else {
		s.activeRows[rows] = cancel
	}
	s.activeCancelMu.Unlock()
	return rows, queryErr
}

func (s *server) cancelActiveQuery() {
	s.activeCancelMu.Lock()
	cancels := make([]context.CancelFunc, 0, len(s.activeRows)+1)
	if s.statementCancel != nil {
		cancels = append(cancels, s.statementCancel)
	}
	if s.activeCancel != nil {
		cancels = append(cancels, s.activeCancel)
	}
	for _, cancel := range s.activeRows {
		cancels = append(cancels, cancel)
	}
	s.activeCancelMu.Unlock()
	for _, cancel := range cancels {
		cancel()
	}
}

func (s *server) closeRows(rows *sql.Rows) error {
	if rows == nil {
		return nil
	}
	s.activeCancelMu.Lock()
	cancel := s.activeRows[rows]
	delete(s.activeRows, rows)
	s.activeCancelMu.Unlock()
	if cancel != nil {
		cancel()
	}
	return rows.Close()
}

func decodeParams(params map[string]json.RawMessage, target any) error {
	if params == nil {
		params = map[string]json.RawMessage{}
	}
	data, err := json.Marshal(params)
	if err != nil {
		return err
	}
	return json.Unmarshal(data, target)
}

func stringParam(params map[string]json.RawMessage, key string) string {
	if params == nil || len(params[key]) == 0 {
		return ""
	}
	var value string
	_ = json.Unmarshal(params[key], &value)
	return value
}

func stringSliceParam(params map[string]json.RawMessage, key string) []string {
	if params == nil || len(params[key]) == 0 {
		return nil
	}
	var value []string
	if err := json.Unmarshal(params[key], &value); err != nil {
		return nil
	}
	return value
}

func intParam(params map[string]json.RawMessage, key string) int {
	if params == nil || len(params[key]) == 0 {
		return 0
	}
	var value int
	_ = json.Unmarshal(params[key], &value)
	return value
}

func boolParam(params map[string]json.RawMessage, key string) bool {
	if params == nil || len(params[key]) == 0 {
		return false
	}
	var value bool
	_ = json.Unmarshal(params[key], &value)
	return value
}

func parseURLParams(raw string) map[string]string {
	result := map[string]string{}
	values, err := url.ParseQuery(raw)
	if err != nil {
		return result
	}
	for key, items := range values {
		if len(items) > 0 {
			result[key] = items[len(items)-1]
		}
	}
	return result
}

func trimStatementSQL(sqlText string) string {
	trimmed := stripTrailingSlashDelimiter(strings.TrimSpace(sqlText))
	if isOraclePlSQLBlock(trimmed) {
		return trimmed
	}
	return strings.TrimRight(trimmed, "; \t\r\n")
}

func stripTrailingSlashDelimiter(sqlText string) string {
	trimmed := strings.TrimSpace(sqlText)
	if !strings.HasSuffix(trimmed, "/") {
		return trimmed
	}
	slashStart := len(trimmed) - 1
	lineStart := strings.LastIndex(trimmed[:slashStart], "\n") + 1
	if strings.TrimSpace(trimmed[lineStart:slashStart]) != "" {
		return trimmed
	}
	beforeSlash := strings.TrimSpace(trimmed[:lineStart])
	// SQL*Plus uses a standalone slash to execute PL/SQL blocks; go-ora needs
	// only the block text and not that client-side delimiter.
	if isOraclePlSQLBlock(beforeSlash) {
		return beforeSlash
	}
	return trimmed
}

func isOraclePlSQLBlock(sqlText string) bool {
	trimmed := strings.TrimSpace(sqlText)
	start := trimLeadingSQLComments(trimmed)
	if !oraclePlSQLBlockStartRegexp.MatchString(start) {
		return false
	}
	upper := strings.ToUpper(trimmed)
	if oraclePlSQLBlockEndRegexp.MatchString(upper) {
		return true
	}
	matches := oracleNamedPlSQLBlockEndRegexp.FindStringSubmatch(upper)
	if len(matches) != 2 {
		return false
	}
	switch matches[1] {
	case "IF", "LOOP", "CASE":
		return false
	default:
		return true
	}
}

func isQuerySQL(sqlText string) bool {
	executable := trimLeadingSQLComments(sqlText)
	return startsWithSQLKeyword(executable, "select") || startsWithSQLKeyword(executable, "with")
}

func trimLeadingSQLComments(sqlText string) string {
	remaining := strings.TrimSpace(sqlText)
	for {
		switch {
		case strings.HasPrefix(remaining, "--"):
			lineEnd := strings.IndexAny(remaining, "\r\n")
			if lineEnd < 0 {
				return ""
			}
			remaining = strings.TrimSpace(remaining[lineEnd+1:])
		case strings.HasPrefix(remaining, "/*"):
			commentEnd := strings.Index(remaining[2:], "*/")
			if commentEnd < 0 {
				return ""
			}
			remaining = strings.TrimSpace(remaining[commentEnd+4:])
		default:
			return remaining
		}
	}
}

func startsWithSQLKeyword(sqlText, keyword string) bool {
	sqlText = strings.TrimSpace(sqlText)
	if len(sqlText) < len(keyword) || !strings.EqualFold(sqlText[:len(keyword)], keyword) {
		return false
	}
	if len(sqlText) == len(keyword) {
		return true
	}
	next := sqlText[len(keyword)]
	return !((next >= 'a' && next <= 'z') || (next >= 'A' && next <= 'Z') || (next >= '0' && next <= '9') || next == '_' || next == '$')
}

func sqlKeywordAt(sqlText string, pos int, keyword string) bool {
	if pos < 0 || pos+len(keyword) > len(sqlText) || !strings.EqualFold(sqlText[pos:pos+len(keyword)], keyword) {
		return false
	}
	if pos > 0 && isOracleIdentifierPart(sqlText[pos-1]) {
		return false
	}
	if pos+len(keyword) >= len(sqlText) {
		return true
	}
	return !isOracleIdentifierPart(sqlText[pos+len(keyword)])
}

func quoteIdentifier(value string) string {
	return `"` + strings.ReplaceAll(value, `"`, `""`) + `"`
}

func normalizeValue(value any, columnTypeName string) any {
	switch v := value.(type) {
	case nil:
		return nil
	case []byte:
		// Oracle RAW-like columns are binary data; decoding them as text produces mojibake.
		if isOracleBinaryColumnType(columnTypeName) {
			return bytesToHex(v)
		}
		return string(v)
	case time.Time:
		// Oracle DATE and plain TIMESTAMP are wall-clock values; adding an offset makes clients shift them.
		if isOracleTimezoneLessDateTime(columnTypeName) {
			return v.Format("2006-01-02T15:04:05.999999999")
		}
		return v.Format(time.RFC3339Nano)
	case int64, float64, bool, string:
		return v
	case fmt.Stringer:
		return v.String()
	default:
		return fmt.Sprint(v)
	}
}

func isOracleTimezoneLessDateTime(columnTypeName string) bool {
	normalized := strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(columnTypeName), " ", ""))
	if normalized == "DATE" || normalized == "TIMESTAMPDTY" || normalized == "TIMESTAMP" {
		return true
	}
	return strings.HasPrefix(normalized, "TIMESTAMP(") && strings.HasSuffix(normalized, ")")
}

func isOracleBinaryColumnType(columnTypeName string) bool {
	normalized := strings.ToUpper(strings.ReplaceAll(strings.TrimSpace(columnTypeName), " ", ""))
	switch normalized {
	case "RAW", "VARRAW", "LONGRAW", "LONGVARRAW", "BLOB", "BFILE", "OCIBLOBLOCATOR", "OCIFILELOCATOR":
		return true
	default:
		return false
	}
}

func bytesToHex(bytes []byte) string {
	const digits = "0123456789abcdef"
	result := make([]byte, 2+len(bytes)*2)
	result[0] = '0'
	result[1] = 'x'
	for i, b := range bytes {
		result[2+i*2] = digits[b>>4]
		result[3+i*2] = digits[b&0x0f]
	}
	return string(result)
}

func emptyIfNil[T any](values []T) []T {
	if values == nil {
		return []T{}
	}
	return values
}

func intPtrFromString(value string) *int {
	if value == "" {
		return nil
	}
	parsed, err := strconv.Atoi(value)
	if err != nil {
		return nil
	}
	return &parsed
}