oracle

package module
v1.6.6 Latest Latest
Warning

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

 Go to latest Published: Apr 27, 2024 License: MIT Imports: 18 Imported by: 3

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

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 added in v1.5.11 

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 added in v1.3.0 

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 added in v1.4.2 

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

ConvertToAssignments convert to update assignments

func Create 

func Create(db *gorm.DB)

func DelSessionParams added in v1.5.11 

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

DelSessionParams remove session parameters

func GetStringExpr added in v1.5.9 

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 added in v1.5.12 

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 added in v1.4.2 

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 added in v1.5.19 

type DataSet struct {
	go_ora.DataSet
}

type Dialector 

type Dialector struct {
	*Config
}

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 added in v1.3.39 

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
}

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

func (Migrator) AlterDataTypeOf added in v1.3.26 

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

func (Migrator) AutoMigrate added in v1.3.64 

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 added in v1.3.26 

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

func (Migrator) CreateTable 

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

func (Migrator) CurrentDatabase 

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

func (Migrator) DropColumn 

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

func (Migrator) DropConstraint 

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

func (Migrator) DropIndex 

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

func (Migrator) DropTable 

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

func (Migrator) FullDataTypeOf added in v1.5.3 

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

FullDataTypeOf returns field's db full data type

func (Migrator) GetTables added in v1.5.19 

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

func (Migrator) GetTypeAliases added in v1.4.0 

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

func (Migrator) HasConstraint 

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

func (Migrator) HasIndex 

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

func (Migrator) HasTable 

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

func (Migrator) MigrateColumn added in v1.5.7 

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 https://docs.oracle.com/database/121/SPATL/alter-index-rename.htm

func (Migrator) RenameTable 

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

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 schema.Namer

	CaseSensitive bool // whether naming is case-sensitive
}

func (Namer) CheckerName 

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

func (Namer) ColumnName 

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

func (Namer) ConvertNameToFormat added in v1.5.0 

func (n Namer) ConvertNameToFormat(x string) string

func (Namer) IndexName 

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

func (Namer) JoinTableName 

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

func (Namer) RelationshipFKName 

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

func (Namer) SchemaName added in v1.6.5 

func (n Namer) SchemaName(table string) string

func (Namer) TableName 

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

func (Namer) UniqueName added in v1.6.5 

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

type Out added in v1.5.19 

type Out struct {
	go_ora.Out
}

type RefCursor added in v1.5.19 

type RefCursor struct {
	go_ora.RefCursor
}

func (*RefCursor) Query added in v1.5.19 

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

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.