oracle

package module
v0.7.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Feb 23, 2026 License: MIT Imports: 28 Imported by: 0

README

GORM Oracle Driver

Description

GORM Oracle driver for connect Oracle DB and Manage Oracle DB, Based on godoes/oracle with changes to support partial indexes and any [16]byte type-alias ( ex: uuid / ulid / etc ) to / from raw(16) conversion.

Required dependency Install

Note: oracle client is not needed
  • Oracle 12c (or higher)
  • Golang v1.24+
  • gorm 1.24.0 + (tested with 1.31.0)

Quick Start

How to install
go get -d github.com/cmmoran/gorm-oracle
Usage
package main

import (
	oracle "github.com/cmmoran/gorm-oracle"
	"gorm.io/gorm"
)

func main() {
	options := map[string]string{
		"CONNECTION TIMEOUT": "90",
		"SSL":                "false",
	}
	// oracle://user:[email protected]:1521/service_name
	url := oracle.BuildUrl("127.0.0.1", 1521, "service", "user", "password", options)
	dialector := oracle.New(oracle.Config{
		DSN:                     url,
		IgnoreCase:              false, // query conditions are not case-sensitive
		PreferedCase:           oracle.ScreamingSnakeCase, // this matches oracles default handling of identifiers; other options are SnakeCase, CamelCase which will force NamingCaseSensitive to true
		NamingCaseSensitive:     true,  // whether naming is case-sensitive
		VarcharSizeIsCharLength: true,  // whether VARCHAR type size is character length, defaulting to byte length

		// RowNumberAliasForOracle11 is the alias for ROW_NUMBER() in Oracle 11g, defaulting to ROW_NUM
		RowNumberAliasForOracle11: "ROW_NUM",
	})
	cfg := &gorm.Config{
      SkipDefaultTransaction:                   true,
      DisableForeignKeyConstraintWhenMigrating: true,
      NamingStrategy: schema.NamingStrategy{
        IdentifierMaxLength: 30,   // Oracle >= 12.2: 128, Oracle < 12.2: 30, PostgreSQL:63, MySQL: 64, SQL Server、SQLite、DM: 128
      },
      PrepareStmt:     false,
      CreateBatchSize: 50,
    }
	db, err := gorm.Open(dialector, cfg)
	if err != nil {
		// panic error or log error info
	}

	// set session parameters
	if sqlDB, err := db.DB(); err == nil {
		_, _ = oracle.AddSessionParams(sqlDB, map[string]string{
			"TIME_ZONE":                "+0:00",                                 // ALTER SESSION SET TIME_ZONE = '+0:00';
			// try to match go defaults as closely as possible
			"NLS_DATE_FORMAT":          `YYYY-MM-DD"T"HH24:MI:SS`,               // ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS'; 
			"NLS_TIMESTAMP_FORMAT":     `YYYY-MM-DD"T"HH24:MI:SS.FF6`,           // ALTER SESSION SET NLS_TIME_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF6';
			"NLS_TIMESTAMP_TZ_FORMAT":  `YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM`,    // ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF6TZH:TZM';
			"NLS_TIME_FORMAT":          `HH24:MI:SS.FF6`,                        // ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS.FF6';
			"NLS_TIME_TZ_FORMAT":       `HH24:MI:SS.FF6TZH:TZM`,                 // ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'HH24:MI:SS.FF6TZH:TZM';
			//"NLS_DATE_FORMAT":         "YYYY-MM-DD",                   
			//"NLS_TIME_FORMAT":         "HH24:MI:SSXFF",                
			//"NLS_TIMESTAMP_FORMAT":    "YYYY-MM-DD HH24:MI:SSXFF",     
			//"NLS_TIME_TZ_FORMAT":      "HH24:MI:SS.FF TZR",            
			//"NLS_TIMESTAMP_TZ_FORMAT": "YYYY-MM-DD HH24:MI:SSXFF TZR", 
		})
	}

	// do stuff
}

Questions

Upsert Semantics

  • Clauses(clause.OnConflict{...}).Create(...) uses Oracle MERGE when the configured conflict target columns are present in the insert payload.
  • OnConflict.Where is mapped to WHEN MATCHED THEN UPDATE ... WHERE ... (matched-row update predicate).
  • OnConflict.TargetWhere is intentionally unsupported in the Oracle MERGE path and returns: oracle: OnConflict.TargetWhere is unsupported in MERGE path due to semantic ambiguity
ORA-01000: maximum open cursors exceeded

ORA-00604: error occurred at recursive SQL level 1

ORA-01000: maximum open cursors exceeded

show parameter OPEN_CURSORS;

alter system set OPEN_CURSORS = 1000; -- or bigger
commit;
ORA-01002: fetch out of sequence

If the same query is executed repeatedly, and the first query is successful but the second one returns an ORA-01002 error, it might be because PrepareStmt is enabled. Disabling this configuration should resolve the issue.

Recommended configuration:

&gorm.Config{
    SkipDefaultTransaction:                   true, // Should single create, update, and delete operations be disabled from automatically executing within transactions?
    DisableForeignKeyConstraintWhenMigrating: true, // Is it possible to disable the automatic creation of foreign key constraints when automatically migrating or creating tables?
    // Custom naming strategy
	NamingStrategy: oracle.NamingStrategy{
            PreferredCase:       oracle.ScreamingSnakeCase, 
            IdentifierMaxLength: 128,   
    },
    PrepareStmt:     false, // Create and cache precompiled statements.  Enabling this may result in an ORA-01002 error.
    CreateBatchSize: 50,    // Default batch size for inserting data
}

Documentation

Index

Constants

View Source 
const (
	NullNoop nullAction = iota
	NullSetNull
	NullSetNotNull
)

Variables

View Source 
var ReservedWords = hashset.New[string](ReservedWordsList...)
View Source 
var ReservedWordsList = []string{}/* 112 elements not displayed */

Functions

func AddSessionParams 

func AddSessionParams(db *sql.DB, params map[string]string, originals ...bool) (keys []string, err error)

AddSessionParams setting database connection session parameters, the value is wrapped in single quotes.

If the value doesn't need to be wrapped in single quotes, please use the go_ora.AddSessionParam function directly, or pass the originals parameter as true.

func BuildUrl 

func BuildUrl(server string, port int, service, user, password string, options map[string]string) string

BuildUrl create databaseURL from server, port, service, user, password, urlOptions this function help build a will formed databaseURL and accept any character as it convert special charters to corresponding values in URL

func ConvertMapToValuesForCreate added in v0.4.0 

func ConvertMapToValuesForCreate(stmt *gorm.Statement, mapValue map[string]interface{}) (values clause.Values)

ConvertMapToValuesForCreate convert map to values

func ConvertSliceOfMapToValuesForCreate added in v0.4.0 

func ConvertSliceOfMapToValuesForCreate(stmt *gorm.Statement, mapValues []map[string]interface{}) (values clause.Values)

ConvertSliceOfMapToValuesForCreate convert slice of map to values

func ConvertToAssignments 

func ConvertToAssignments(stmt *gorm.Statement) (set clause.Set)

ConvertToAssignments convert to update assignments

func ConvertToCreateValues added in v0.4.0 

func ConvertToCreateValues(stmt *gorm.Statement) (values clause.Values)

func Create 

func Create(db *gorm.DB)

func DelSessionParams 

func DelSessionParams(db *sql.DB, keys []string)

DelSessionParams remove session parameters

func Delete added in v0.2.1 

func Delete(db *gorm.DB)

func ExplainSQL added in v0.2.1 

func ExplainSQL(sql string, numericPlaceholder *regexp.Regexp, escaper string, avars ...interface{}) string

ExplainSQL generate SQL string with given parameters, the generated SQL is expected to be used in logger, execute it might introduce a SQL injection vulnerability

func GetStringExpr 

func GetStringExpr(value string, quotes ...bool) clause.Expr

GetStringExpr replace single quotes in the string with two single quotes and return the expression for the string value 

quotes : if the SQL placeholder is ? then pass true, if it is '?' then do not pass or pass false.

func IsExplicitQuoted added in v0.2.4 

func IsExplicitQuoted(part string) (inner string, ok bool)

IsExplicitQuoted Detects explicit user-quoted literal: (example: "Name")

func IsReservedWord 

func IsReservedWord(v string) bool

func IsSafeOracleUnquoted added in v0.2.4 

func IsSafeOracleUnquoted(s string) bool

IsSafeOracleUnquoted

Unquoted identifiers: - are stored uppercase - must begin with a letter - may contain A–Z, 0–9, _, $, # - must not be a reserved word

Input s must already be in its target case for the chosen mode.

Returns true if s can be emitted unquoted safely.

func MergeCreate 

func MergeCreate(db *gorm.DB, onConflict clause.OnConflict, values clause.Values)

func New 

func New(config Config) gorm.Dialector

func Open 

func Open(dsn string) gorm.Dialector

func Query added in v0.2.2 

func Query(db *gorm.DB)

func Scan added in v0.4.0 

func Scan(rows gorm.Rows, db *gorm.DB, mode gorm.ScanMode)

func Update 

func Update(db *gorm.DB)

Types

type Case added in v0.2.2 

type Case int
const (
	ScreamingSnakeCase Case = iota
	SnakeCase
	CamelCase
)

type Config 

type Config struct {
	DriverName        string
	DSN               string
	Conn              gorm.ConnPool //*sql.DB
	DefaultStringSize uint
	DBVer             string

	// IgnoreCase applies to data; not identifiers
	IgnoreCase bool // warning: may cause performance issues
	// NamingCaseSensitive applies to identifiers
	NamingCaseSensitive bool // whether naming is case-sensitive
	// PreferredCase determines the strategy for naming identifiers; Note that setting PreferredCase to CamelCase or SnakeCase will override the NamingCaseSensitive setting; ScreamingSnakeCase is the default and works with both case-sensitive and case-insensitive naming
	PreferredCase Case

	// whether VARCHAR type size is character length, defaulting to byte length
	VarcharSizeIsCharLength bool

	// RowNumberAliasForOracle11 is the alias for ROW_NUMBER() in Oracle 11g, defaulting to ROW_NUM
	RowNumberAliasForOracle11 string
	UseClobForTextType        bool
	// time conversion for all clauses to ensure proper time rounding
	TimeGranularity time.Duration
	// use this timezone for the session
	SessionTimezone string
	// contains filtered or unexported fields
}

type DataSet 

type DataSet struct {
	go_ora.DataSet
}

type Dialector 

type Dialector struct {
	*Config
}

Dialector implement GORM database dialector

func (Dialector) BindVarTo 

func (d Dialector) BindVarTo(writer clause.Writer, stmt *gorm.Statement, _ interface{})

func (Dialector) ClauseBuilders 

func (d Dialector) ClauseBuilders() (clauseBuilders map[string]clause.ClauseBuilder)

func (Dialector) DataTypeOf 

func (d Dialector) DataTypeOf(field *schema.Field) string

func (Dialector) DefaultValueOf 

func (d Dialector) DefaultValueOf(*schema.Field) clause.Expression

func (Dialector) DummyTableName 

func (d Dialector) DummyTableName() string

func (Dialector) Explain 

func (d Dialector) Explain(sql string, vars ...interface{}) string

func (Dialector) Initialize 

func (d Dialector) Initialize(db *gorm.DB) (err error)

func (Dialector) Migrator 

func (d Dialector) Migrator(db *gorm.DB) gorm.Migrator

func (Dialector) Name 

func (d Dialector) Name() string

func (Dialector) QuoteTo 

func (d Dialector) QuoteTo(w clause.Writer, s string)

QuoteTo writes a SQL-quoted identifier (or dotted path) to writer. When NamingCaseSensitive is true, every dot-separated part is wrapped in double quotes and any internal `"` are escaped as `""`. Existing outer quotes around parts are normalized (removed then re-applied).

func (Dialector) RewriteLimit 

func (d Dialector) RewriteLimit(c clause.Clause, builder clause.Builder)

func (Dialector) RewriteLimit11 

func (d Dialector) RewriteLimit11(c clause.Clause, builder clause.Builder)

RewriteLimit11 rewrite the LIMIT clause in the query to accommodate pagination requirements for Oracle 11g and lower database versions

Limit and Offset 

SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER (ORDER BY column) AS ROW_NUM FROM table_name T)
WHERE ROW_NUM BETWEEN offset+1 AND offset+limit

Only Limit 

SELECT * FROM table_name WHERE ROWNUM <= limit ORDER BY column

Only Offset 

SELECT * FROM table_name WHERE ROWNUM > offset ORDER BY column

func (Dialector) RollbackTo 

func (d Dialector) RollbackTo(tx *gorm.DB, name string) error

func (Dialector) SavePoint 

func (d Dialector) SavePoint(tx *gorm.DB, name string) error

func (Dialector) Translate added in v0.2.1 

func (d Dialector) Translate(err error) error

type Migrator 

type Migrator struct {
	migrator.Migrator
	// contains filtered or unexported fields
}

Migrator implement gorm migrator interface

func (Migrator) AddColumn 

func (m Migrator) AddColumn(value interface{}, field string) error

AddColumn adds a column using Oracle syntax:

ALTER TABLE <t> ADD (<col …>) Then (optionally) enforce NOT NULL via a separate MODIFY to avoid data population issues.

func (Migrator) AlterColumn 

func (m Migrator) AlterColumn(value interface{}, field string) error

AlterColumn

ALTER TABLE <t> MODIFY (<col …>) Identity add/drop is done as a separate MODIFY.

func (Migrator) AlterDataTypeOf 

func (m Migrator) AlterDataTypeOf(stmt *gorm.Statement, field *schema.Field) (expr clause.Expr)

AlterDataTypeOf builds "<datatype> [DEFAULT ...] [NOT NULL]" for Oracle. It is used by generic migrator code paths; AlterColumn/AddColumn should still call their own builders.

func (Migrator) AutoMigrate 

func (m Migrator) AutoMigrate(dst ...interface{}) error

AutoMigrate automatically migrate model to table structure 

// Migrate and set single table comment
db.Set("gorm:table_comments", "User Information Table").AutoMigrate(&User{})

// Migrate and set multiple table comments
db.Set("gorm:table_comments", []string{"User Information Table", "Company Information Table"}).AutoMigrate(&User{}, &Company{})

func (Migrator) BuildIndexOptions added in v0.2.4 

func (m Migrator) BuildIndexOptions(opts []schema.IndexOption, stmt *gorm.Statement) (results []interface{})

BuildIndexOptions builds the per-column list for CREATE INDEX on Oracle. Notes: - Ignore Length and Collate (not applicable). - Keep raw expressions as-is. - Use NamingStrategy to render identifiers (avoids quotes unless required). - Allow ASC/DESC [NULLS FIRST|LAST].

func (Migrator) ColumnTypes 

func (m Migrator) ColumnTypes(value interface{}) ([]gorm.ColumnType, error)

ColumnTypes via USER/ALL_TAB_COLUMNS (no driver metadata).

func (Migrator) CreateConstraint 

func (m Migrator) CreateConstraint(value interface{}, name string) error

CreateConstraint ensure FK names follow oracle.NamingStrategy (genToken), and strip unsupported ON UPDATE clauses.

func (Migrator) CreateIndex 

func (m Migrator) CreateIndex(value interface{}, name string) error

func (Migrator) CreateTable 

func (m Migrator) CreateTable(values ...interface{}) error

CreateTable create table in database for values

func (Migrator) CurrentDatabase 

func (m Migrator) CurrentDatabase() (name string)

CurrentDatabase returns current database name

func (Migrator) DropColumn 

func (m Migrator) DropColumn(value interface{}, name string) error

DropColumn ALTER TABLE <table> DROP COLUMN <col>

func (Migrator) DropConstraint 

func (m Migrator) DropConstraint(value interface{}, name string) error

DropConstraint ALTER TABLE <table> DROP CONSTRAINT <name>

func (Migrator) DropIndex 

func (m Migrator) DropIndex(value interface{}, name string) error

DropIndex DROP INDEX <name>

func (Migrator) DropTable 

func (m Migrator) DropTable(values ...interface{}) error

DropTable drop table for values

func (Migrator) FullDataTypeOf 

func (m Migrator) FullDataTypeOf(field *schema.Field) (expr clause.Expr)

FullDataTypeOf returns field's db full data type

func (Migrator) GetTables 

func (m Migrator) GetTables() (tableList []string, err error)

GetTables returns tables under the current user database

func (Migrator) GetTypeAliases 

func (m Migrator) GetTypeAliases(databaseTypeName string) (types []string)

GetTypeAliases return database type aliases

func (Migrator) HasColumn 

func (m Migrator) HasColumn(value interface{}, field string) bool

HasColumn returns whether a column exists on the target table.

func (Migrator) HasConstraint 

func (m Migrator) HasConstraint(value interface{}, name string) bool

HasConstraint USER_CONSTRAINTS / ALL_CONSTRAINTS with dictionary casing

func (Migrator) HasIndex 

func (m Migrator) HasIndex(value interface{}, name string) bool

HasIndex USER_INDEXES / ALL_INDEXES with dictionary casing for table and index

func (Migrator) HasTable 

func (m Migrator) HasTable(value interface{}) bool

HasTable returns table existence using Oracle data dictionary. Uses dictQualifiedParts to compare OWNER/TABLE_NAME correctly for quoted vs unquoted identifiers.

func (Migrator) MigrateColumn 

func (m Migrator) MigrateColumn(value interface{}, field *schema.Field, _ gorm.ColumnType) error

MigrateColumn Oracle-specific. 1) ALTER via your AlterColumn (MODIFY ...). 2) Sync COMMENT ON COLUMN if model comment differs.

func (Migrator) RenameIndex 

func (m Migrator) RenameIndex(value interface{}, oldName, newName string) error

RenameIndex ALTER INDEX <old> RENAME TO <new>

func (Migrator) RenameTable 

func (m Migrator) RenameTable(oldName, newName interface{}) (err error)

RenameTable rename table from oldName to newName

type NamingStrategy added in v0.2.2 

type NamingStrategy struct {
	TablePrefix         string
	SingularTable       bool
	NameReplacer        Replacer
	IdentifierMaxLength int

	PreferredCase       Case // default is SCREAMING_SNAKE_CASE
	NamingCaseSensitive bool // whether naming is case-sensitive
	// contains filtered or unexported fields
}

NamingStrategy tables, columns naming strategy

func (*NamingStrategy) CheckerName added in v0.2.2 

func (ns *NamingStrategy) CheckerName(table, column string) string

CheckerName builds a CHECK constraint name: CK_<TABLE>_<COLUMN...>, capped to Oracle limits.

func (*NamingStrategy) ColumnName added in v0.2.2 

func (ns *NamingStrategy) ColumnName(_, column string) string

ColumnName convert string to column name

func (*NamingStrategy) IndexName added in v0.2.2 

func (ns *NamingStrategy) IndexName(table, column string) string

IndexName builds a unique index name(table, hint) -> IDX_<TABLE>_<HINT>_<FNV8>, capped to IdentifierMaxLength

func (*NamingStrategy) JoinTableName added in v0.2.2 

func (ns *NamingStrategy) JoinTableName(joinTable string) string

JoinTableName applies the same rules as TableName for join tables.

func (*NamingStrategy) RelationshipFKName added in v0.2.2 

func (ns *NamingStrategy) RelationshipFKName(rel schema.Relationship) string

RelationshipFKName builds a deterministic FK constraint name honoring Oracle's 30-byte cap. We generate an unqualified, safe token (A–Z, 0–9, _) and let QuoteTo add quotes only if required elsewhere.

func (*NamingStrategy) SchemaName added in v0.2.2 

func (ns *NamingStrategy) SchemaName(table string) string

SchemaName returns the normalized OWNER/SCHEMA portion for a possibly-qualified table. If no explicit owner is provided, returns "".

func (*NamingStrategy) TableName added in v0.2.2 

func (ns *NamingStrategy) TableName(str string) string

TableName convert string to table name

func (*NamingStrategy) UniqueName added in v0.2.2 

func (ns *NamingStrategy) UniqueName(table, column string) string

UniqueName builds a unique index/constraint name: UK_<TABLE>_<COLUMN...>, capped to Oracle limits.

type Out 

type Out struct {
	go_ora.Out
}

type RefCursor 

type RefCursor struct {
	go_ora.RefCursor
}

func (*RefCursor) Query 

func (cursor *RefCursor) Query() (dataset *DataSet, err error)

type Replacer added in v0.2.2 

type Replacer interface {
	Replace(name string) string
}

Replacer replacer interface like strings.Replacer

type Returning added in v0.2.4 

type Returning struct {
	Names []string
	// contains filtered or unexported fields
}

func ReturningFieldsWithDefaultDBValue added in v0.2.4 

func ReturningFieldsWithDefaultDBValue(sch *schema.Schema, values *clause.Values) Returning

func ReturningWithColumns added in v0.2.4 

func ReturningWithColumns(cols []clause.Column) Returning

func ReturningWithPrimaryFields added in v0.2.4 

func ReturningWithPrimaryFields(sch *schema.Schema) Returning

func (Returning) Build added in v0.2.4 

func (returning Returning) Build(builder clause.Builder)

func (Returning) MergeClause added in v0.2.4 

func (returning Returning) MergeClause(clause *clause.Clause)

MergeClause merge order by clauses

func (Returning) Name added in v0.2.4 

func (returning Returning) Name() string

Name where clause name

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.