oracle

package module
v0.9.0 Latest Latest
Warning

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

 Go to latest Published: Mar 10, 2025 License: MIT Imports: 18 Imported by: 0

README

GORM Oracle Driver

Description

GORM Oracle driver for connect Oracle DB and Manage Oracle DB, Based on CengSin/oracle and sijms/go-ora (pure go oracle client)，not recommended for use in a production environment.

Required dependency Install

  • Oracle 11g + (v1.6.3 and earlier versions support only 12c +)
  • Golang
    • v1.6.1: go1.16 +
    • v1.6.2: go1.18 +
  • gorm 1.24.0 +

Quick Start

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

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

func main() {
	options := map[string]string{
		"CONNECTION TIMEOUT": "90",
		"LANGUAGE":           "SIMPLIFIED CHINESE",
		"TERRITORY":          "CHINA",
		"SSL":                "false",
	}
	// oracle://user:password@127.0.0.1:1521/service
	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
		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",
	})
	db, err := gorm.Open(dialector, &gorm.Config{
		SkipDefaultTransaction:                   true, // 是否禁用默认在事务中执行单次创建、更新、删除操作
		DisableForeignKeyConstraintWhenMigrating: true, // 是否禁止在自动迁移或创建表时自动创建外键约束
		// 自定义命名策略
		NamingStrategy: schema.NamingStrategy{
			NoLowerCase:         true, // 是否不自动转换小写表名
			IdentifierMaxLength: 30,   // Oracle: 30, PostgreSQL:63, MySQL: 64, SQL Server、SQLite、DM: 128
		},
		PrepareStmt:     false, // 创建并缓存预编译语句，启用后可能会报 ORA-01002 错误
		CreateBatchSize: 50,    // 插入数据默认批处理大小
	})
	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":               "+08:00",                       // ALTER SESSION SET TIME_ZONE = '+08:00';
			"NLS_DATE_FORMAT":         "YYYY-MM-DD",                   // ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
			"NLS_TIME_FORMAT":         "HH24:MI:SSXFF",                // ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS.FF3';
			"NLS_TIMESTAMP_FORMAT":    "YYYY-MM-DD HH24:MI:SSXFF",     // ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
			"NLS_TIME_TZ_FORMAT":      "HH24:MI:SS.FF TZR",            // ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS.FF3 TZR';
			"NLS_TIMESTAMP_TZ_FORMAT": "YYYY-MM-DD HH24:MI:SSXFF TZR", // ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3 TZR';
		})
	}

	// do somethings
}

Questions

ORA-01000: 超出打开游标的最大数

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-01000: 超出打开游标的最大数

show parameter OPEN_CURSORS;

alter system set OPEN_CURSORS = 1000; -- or bigger
commit;
ORA-01002: 提取违反顺序

如果重复执行同一查询，第一次查询成功，第二次报 ORA-01002 错误，可能是因为启用了 PrepareStmt，关闭此配置即可。

推荐配置：

&gorm.Config{
    SkipDefaultTransaction:                   true, // 是否禁用默认在事务中执行单次创建、更新、删除操作
    DisableForeignKeyConstraintWhenMigrating: true, // 是否禁止在自动迁移或创建表时自动创建外键约束
    // 自定义命名策略
    NamingStrategy: schema.NamingStrategy{
        NoLowerCase:         true, // 是否不自动转换小写表名
        IdentifierMaxLength: 30,   // Oracle: 30, PostgreSQL:63, MySQL: 64, SQL Server、SQLite、DM: 128
    },
    PrepareStmt:     false, // 创建并缓存预编译语句，启用后可能会报 ORA-01002 错误
    CreateBatchSize: 50,    // 插入数据默认批处理大小
}

Contributors

iTanken
iTanken 		stevefan1999-personal
stevefan1999-personal 		CengSin
CengSin 		jinzhu
jinzhu 		dzwvip
dzwvip 		miclle
miclle
dk333
dk333 		cloorc
cloorc

LICENSE

MIT license

Documentation

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ReservedWords = hashset.New(func() []interface{} {
	reservedWords := make([]interface{}, len(ReservedWordsList))
	for i, word := range ReservedWordsList {
		reservedWords[i] = word
	}
	return reservedWords
}()...)
View Source 
var ReservedWordsList = []string{}/* 143 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 ConvertNameToFormat deprecated 

func ConvertNameToFormat(x string) string

Deprecated: As of v1.5.0, use the Namer.ConvertNameToFormat instead.

func ConvertToAssignments 

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

ConvertToAssignments convert to update assignments

func Create 

func Create(db *gorm.DB)

func DelSessionParams 

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

DelSessionParams remove session parameters

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 IsReservedWord 

func IsReservedWord(v string) bool

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 Update 

func Update(config *callbacks.Config) func(db *gorm.DB)

Types

type Config 

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

	IgnoreCase          bool // warning: may cause performance issues
	NamingCaseSensitive bool // whether naming is case-sensitive
	// 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
}

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(writer clause.Writer, str string)

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

type Migrator 

type Migrator struct {
	migrator.Migrator
}

Migrator implement gorm migrator interface

func (Migrator) AddColumn 

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

AddColumn create "name" column for value

func (Migrator) AlterColumn 

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

AlterColumn alter value's "field" column's type based on schema definition

func (Migrator) AlterDataTypeOf 

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

func (Migrator) AutoMigrate 

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

AutoMigrate 自动迁移模型为表结构 

// 迁移并设置单个表注释
db.Set("gorm:table_comments", "用户信息表").AutoMigrate(&User{})

// 迁移并设置多个表注释
db.Set("gorm:table_comments", []string{"用户信息表", "公司信息表"}).AutoMigrate(&User{}, &Company{})

func (Migrator) ColumnTypes 

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

ColumnTypes return columnTypes []gorm.ColumnType and execErr error

func (Migrator) CreateConstraint 

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

CreateConstraint create constraint

func (Migrator) CreateTable 

func (m Migrator) CreateTable(values ...interface{}) (err 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 drop value's "name" column

func (Migrator) DropConstraint 

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

DropConstraint drop constraint

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 check has column "field" for value or not

func (Migrator) HasConstraint 

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

HasConstraint check has constraint or not

func (Migrator) HasIndex 

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

HasIndex check has index "name" or not

func (Migrator) HasTable 

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

HasTable returns table exists or not for value, value could be a struct or string

func (Migrator) MigrateColumn 

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

MigrateColumn migrate column

func (Migrator) RenameIndex 

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

RenameIndex rename index from oldName to newName

see also: https://docs.oracle.com/database/121/SPATL/alter-index-rename.htm

func (Migrator) RenameTable 

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

RenameTable rename table from oldName to newName

func (Migrator) TryQuotifyReservedWords 

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

func (Migrator) TryRemoveOnUpdate 

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

type Namer 

type Namer struct {
	// NamingStrategy use custom naming strategy in gorm.Config on initialize
	NamingStrategy schema.Namer
	// CaseSensitive determines whether naming is case-sensitive
	CaseSensitive bool
}

Namer implement gorm schema namer interface

func (Namer) CheckerName 

func (n Namer) CheckerName(table, column string) (name string)

CheckerName generate checker name

func (Namer) ColumnName 

func (n Namer) ColumnName(table, column string) (name string)

ColumnName convert string to column name

func (Namer) ConvertNameToFormat 

func (n Namer) ConvertNameToFormat(x string) string

ConvertNameToFormat return appropriate capitalization name based on CaseSensitive

func (Namer) IndexName 

func (n Namer) IndexName(table, column string) (name string)

IndexName generate index name

func (Namer) JoinTableName 

func (n Namer) JoinTableName(table string) (name string)

JoinTableName convert string to join table name

func (Namer) RelationshipFKName 

func (n Namer) RelationshipFKName(relationship schema.Relationship) (name string)

RelationshipFKName generate fk name for relation

func (Namer) SchemaName 

func (n Namer) SchemaName(table string) string

SchemaName generate schema name from table name, don't guarantee it is the reverse value of TableName

func (Namer) TableName 

func (n Namer) TableName(table string) (name string)

TableName convert string to table name

func (Namer) UniqueName 

func (n Namer) UniqueName(table, column string) string

UniqueName generate unique constraint name

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)
Example 
db, err := dbNamingCase, dbErrors[0]
if err != nil || db == nil {
	log.Fatal(err)
}
if err = db.Exec(procCreateExamplePagingQuery).Error; err != nil {
	log.Fatal(err)
}
var (
	totalNum  uint
	resCursor RefCursor

	values = []any{
		"SELECT * FROM USER_TABLES",
		"TABLE_NAME",
		1,
		10,
		sql.Out{Dest: &totalNum},
		sql.Out{Dest: &resCursor.RefCursor},
	}
)
// 执行存储过程
if err = db.Exec(`
	BEGIN
		PRO_EXAMPLE_PAGING_QUERY(:BASIC_SQL, :ORDER_FIELD, :PAGE_NUM, :PAGE_SIZE, :TOTAL_NUM, :RES_CURSOR);
	END;`, values...).Error; err != nil {
	log.Fatal(err)
}
defer func(cursor *RefCursor) {
	_ = cursor.Close()
}(&resCursor)

// 读取游标
var dataset *DataSet
if dataset, err = resCursor.Query(); err != nil {
	log.Fatal(err)
}
defer func(dataset *DataSet) {
	_ = dataset.Close()
}(dataset)

var dataRows []map[string]any
columns := dataset.Columns()
dest := make([]driver.Value, len(columns))
for {
	if err = dataset.Next(dest); err != nil {
		if errors.Is(err, io.EOF) {
			err = nil
		}
		break
	}
	dataRow := make(map[string]any, len(columns))
	for i, v := range dest {
		dataRow[columns[i]] = v
	}
	dataRows = append(dataRows, dataRow)
}
if err != nil {
	log.Fatal(err)
}
fmt.Println(len(dataRows) > 0)

Output:

true

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.