go-goracle / goracle Goto Github PK
View Code? Open in Web Editor NEWGo database/sql driver for connecting to Oracle Database, using the ODPI-C library
Go database/sql driver for connecting to Oracle Database, using the ODPI-C library
Goracle is deprecated because of naming (trademark) issues. Please use github.com/godror/godror instead! You can use "sed" to change everything: sed -i -e 's,goracle "gopkg.in/goracle.v2",godror "github.com/godror/godror",g; s,gopkg.in/goracle.v2,github.com/godror/godror,g; s/"goracle"/"godror"/g; s/goracle[.]/godror./g' $(find . -type f -name '*.go') sed -i -e '/goracle.v2/d' go.mod To change everything using modules: for dn in $(fgrep -l goracle.v2 $(find . -type f -name 'go.mod') | sed -e 's,/go.mod$,,'); do (cd "$dn" && git pull && sed -i -e 's,goracle "gopkg.in/goracle.v2",godror "github.com/godror/godror",g; s,gopkg.in/goracle.v2,github.com/godror/godror,g; s/"goracle"/"godror"/g; s/goracle[.]/godror./g' $(find . -type f -name '*.go') && sed -i -e '/goracle.v2/d' go.mod && git commit -am 'goracle -> godror' && git push) done
Reproduce test
func TestFloat(t _testing.T) {
username, password, sid := oracle.SplitDSN(_dsn)
conn, err := oracle.NewConnection(username, password, sid, false)
if err != nil {
t.Fatalf("Connect error: "%s"", err.Error())
}
defer func() {
err := conn.Close()
if err != nil {
t.Errorf("Close error: \"%s\"", err.Error())
}
}()
cur := conn.NewCursor()
defer cur.Close()
{
if oci, client, db, err := conn.NlsSettings(cur); err != nil {
t.Logf("NlsSettings: %s", err)
} else {
t.Logf("NLS oci=%s client=%s database=%s", oci, client, db)
}
}
var v *oracle.Variable
v, err = cur.NewVariable(0, oracle.FloatVarType, 0)
if err != nil {
t.Fatalf("Error with NewVariable: %v", err)
}
err = v.SetValue(0, 0.5)
if err != nil {
t.Fatalf("Error with SetValue: %v", err)
}
vv, err1 := v.GetValue(0)
if err != nil {
t.Fatalf("Error with GetValue: %v", err1)
}
t.Log(vv)
err = v.SetValue(0, vv)
if err != nil {
t.Fatalf("Error with SetValue: %v", err)
}
}
oraex_test.go:60: Error with SetValue: @numberFromText 22062: [-1] ORA-22062: invalid input string [.5]
ORA-22062: invalid input string [.5]
Is there a way to configure the client to fetch CLOBs as strings ?
First of all, thank you for your work!
What are the differences between this driver and rana/ora
?
I see that you are working on that together as well so why this package?
For me this package is much easier to setup and work with but would like to hear if there are any other differences.
I've been able to get everything working and even used the sqlx struct scan and the standard library "manual" approach of populating a struct. I'm having no issues except for one.
I keep getting results back from Oracle 12c that are nill via SQLDeveloper UI but come out a {0 true} in my code.
Has there been any reports or issues with nill values not coming back that way?
Hi,
I tried to build the project after getting odpi and got the error
# gopkg.in/goracle.v2
../gopkg.in/goracle.v2/odpi/lib/libodpic.so: undefined reference to `dlopen'
../gopkg.in/goracle.v2/odpi/lib/libodpic.so: undefined reference to `dlclose'
../gopkg.in/goracle.v2/odpi/lib/libodpic.so: undefined reference to `dlerror'
../gopkg.in/goracle.v2/odpi/lib/libodpic.so: undefined reference to `dlsym'
collect2: error: ld returned 1 exit status
The reason for this error is a missing link flag which exists in the Makefile from the original ODPI repository, however it doesn't exists in goracle linked version. I fixed my issue for now, I am just opening the issue for future reference.
I was following the installation instructions for the package. I installed the oracle instant client and the sdk and then used the env script to verify that I had set the correct environment variables. When i try to go get the package I'm getting an error
warning: libnnz11.so, needed by /usr/lib/oracle/11.2/client64/lib/libclntsh.so, not found
I'm working on Ubuntu 14.04 and with instant client version 11.2.0.4
When I executing the query select originating_timestamp, message_text from v$diag_alert_ext
I receive the following error assignment to entry in nil map
.
panic: assignment to entry in nil map [recovered]
panic: assignment to entry in nil map
goroutine 22 [running]:
testing.tRunner.func1(0xc4203a40f0)
/home/mguggi/public/go-1.9.2/src/testing/testing.go:711 +0x2d2
panic(0xcf92c0, 0xe18a20)
/home/mguggi/public/go-1.9.2/src/runtime/panic.go:491 +0x283
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2.timeZoneFor(0x7fc76c1a0001, 0x7fc76c1aab68)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle.v2/drv.go:687 +0x3df
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2.(*rows).Next(0xc42010db20, 0xc42010af00, 0x27, 0x27, 0x27, 0x27)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle.v2/rows.go:394 +0x2155
database/sql.(*Rows).nextLocked(0xc420354b80, 0xc420420000)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:2463 +0xc4
database/sql.(*Rows).Next.func1()
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:2442 +0x3c
database/sql.withLock(0x12e1a00, 0xc420354bb0, 0xc420425a48)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:2867 +0x65
database/sql.(*Rows).Next(0xc420354b80, 0x1333c50)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:2441 +0x7a
github.com/mguggi/oraclebeat/module/oracle.Scan(0xc420354b80, 0x1333c50, 0xc420354b80, 0xc420425e40, 0x2, 0x2)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/oracle.go:82 +0xd27
github.com/mguggi/oraclebeat/module/oracle/alertlog.(*MetricSet).Fetch(0xc4201a8fa0, 0x0, 0x0, 0x0, 0x0, 0x0)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/alertlog/alertlog.go:130 +0x5f6
github.com/mguggi/oraclebeat/module/oracle/alertlog.TestFetch(0xc4203a40f0)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/alertlog/alertlog_integration_test.go:18 +0xef
testing.tRunner(0xc4203a40f0, 0xdd0840)
/home/mguggi/public/go-1.9.2/src/testing/testing.go:746 +0xd0
created by testing.(*T).Run
/home/mguggi/public/go-1.9.2/src/testing/testing.go:789 +0x2de
Process finished with exit code 2
In the debug trace above you can see that the issue is in
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2.timeZoneFor(0x7fc76c1a0001, 0x7fc76c1aab68)
In my opinion this function has two problems.
I use the following environment:
What do you think about creating a pure go version of the ODPI-C library (if possible)? Not sure if Oracle has any plans about it in the near future.
I think it will help improve build times and performance. Although I have limited C experience, I would like to help if there is a plan for such a library.
With the release of Go 1.10, there were some changes/improvements in the driver API as follows. Just leaving this here in case there needs to be work done given these changes.
Hi Tamas,
I am trying both the rana/ora.v4 and goracle.v2 for the new driver in an app. There is an inconsistency between them that causes the following database/sql error:
sql: statement expects 1 inputs; got 6
goracle.v2 counts the number of inputs as 6 although the query only has 1 bind parameter used in 6 different places.
rana/ora.v4 on the other hand gives me the more expected result of 1. The problem with this inconsistency is the intermediate library sqlx that I use as an lighweight ORM layer, counts the number of params as 6 also disregarding that the param names are duplicates.
I am not really sure which one is the more correct way of counting inputs but currently I changed the NumInput() function in rana/ora.v4 to disregard the duplicates and return the full number and filed a bug report in jmoiron/sqlx.
Is this a bug in goracle.v2 or simply a choice of the driver.
EDIT:
I found something that might be of help in the ODPI release notes. The function goracle.v2 uses to count bind variables has different results based on the parsed text being PL/SQL or SQL. Is it possible to use :func:`dpiStmt_getBindNames() instead to get the "correct" number of bound variables? I would be glad to submit a PR if you like.
You can find the related section in the ODPI release notes below:
Corrected determination of unique bind variable names. The function :func:
dpiStmt_getBindCount() returns a count of unique bind variable names for PL/SQL statements only. For SQL statements, this count is the total number of bind variables, including duplicates. The function :func:
dpiStmt_getBindNames() has been adjusted to return the actual number of unique bind variable names (parameter numBindNames is now a pointer instead of a scalar value).
I have a stored procedure that accepts multiple parameters that are all one of the following:
TYPE cx_array_date IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
TYPE cx_array_string IS TABLE OF VARCHAR2 (1000)
INDEX BY BINARY_INTEGER;
TYPE cx_array_num IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE P_BULK_INSERT_IMP (VIMP_DATES cx_array_date,
VIMP_KEYS cx_array_string,
VIMP_IP cx_array_num,
VIMP_ZONE cx_array_num,
VIMP_PLAN cx_array_num,
VIMP_BANNER cx_array_num,
VIMP_REFERRER cx_array_string,
VIMP_COUNTRY cx_array_string,
VIMP_REGION cx_array_string);
When I call this procedure with the new driver like below:
db.ExecContext(ctx, procInsertImpressions, goracle.PlSQLArrays, dates, keys, ips, zones, plans,
banners, referrers, countries, regions)
if the parameter arrays contains only 1 element they are not regarded as arrays althought the goracle.PlSQLArrays option is set, and I get the following error:
PLS-00306: wrong number or types of arguments in call to 'P_BULK_INSERT_IMP'
ORA-06550: line 1, column 7:
I think the following part in the newVar function in conn.go is responsible for deciding what is regarded as an array and what is not, if I change the if statement to account for arrays with size 1 everything works as expected.
CURRENT:
if isPlSQLArray && arraySize > 1 {
isArray = 1
} else if arraySize < 0 {
arraySize = 1
}
CHANGED:
if isPlSQLArray && arraySize > 0 {
isArray = 1
} else if arraySize < 0 {
arraySize = 1
}
While browsing your commits I saw one named "force array length to be at least 2" so I am guessing this is desired behaviour and not a bug by the driver. Can you let me know what is the correct way to deal with this.
I am using Cursor's NewVar for passing in is parameters to an oracle procedure. I saw some unintended log messages and it seems like these are produced by the goracle library. Is it possible to connect logging to some setting.
I'm having trouble when i'm trying to build application for Windows from MacOS. I use this command;
GOOS=windows GOARCH=386 go build -o main.exe main.go
then it returns like this;
# gopkg.in/goracle.v2
../gopkg.in/goracle.v2/orahlp.go:60:2: undefined: describeOnly
../gopkg.in/goracle.v2/orahlp.go:223:26: undefined: PlSQLArrays
../gopkg.in/goracle.v2/orahlp.go:246:32: undefined: VersionInfo
../gopkg.in/goracle.v2/orahlp.go:249:10: undefined: VersionInfo
../gopkg.in/goracle.v2/orahlp.go:255:32: undefined: VersionInfo
../gopkg.in/goracle.v2/orahlp.go:272:19: undefined: VersionInfo
../gopkg.in/goracle.v2/orahlp.go:273:31: undefined: ObjectType
../gopkg.in/goracle.v2/orahlp.go:274:31: undefined: Event
../gopkg.in/goracle.v2/orahlp.go:274:41: undefined: Subscription
../gopkg.in/goracle.v2/orahlp.go:284:27: undefined: conn
../gopkg.in/goracle.v2/orahlp.go:249:10: too many errors
PS: I can build or run application for MacOS or Linux platforms without errors.
Details;
MacOS 10.13.3
go1.9.5 darwin/amd64
Switching from rana/ora.v4 to goracle and changing some of my private microservice's code fixed the crashes I was seeing. This seems great! Thanks again for making this.
How to reproduce:
var x int
_, err := db.Exec(`
INSERT INTO dummy (name)
VALUES ('myName') RETURNING ID INTO :1;`
, &x)
fmt.Printf("-- %v --", x)
x
will always print
-- 0 --
How do we handle null values returned for timestamp fields?
I could not see a NullTime type defined (e.g. https://godoc.org/github.com/lib/pq#NullTime, https://godoc.org/github.com/go-sql-driver/mysql#NullTime).
Is there an alternative way to handle null values for timestamp columns?
I'm use the last version of the oracle Instant client 12.2.0.1 and go 1.9.2.
My code look like
// close db connection
if err = m.db.Close(); err != nil {
errors.Wrapf(err, "%s failed close connection", selector)
}
When I kill my program I receive this stacktrace:
goroutine 62 [syscall, locked to thread]:
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2._Cfunc_dpiConn_release(0x46da9c0, 0x0)
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle.v2/_obj/_cgo_gotypes.go:1448 +0x49
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2.(*conn).Close.func1(0x46da9c0, 0x2f5d950)
/home/mguggi/work/beats/src/github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle.v2/conn.go:112 +0x60
github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle%2ev2.(*conn).Close(0xc420230300, 0x0, 0x0)
/home/mguggi/work/beats/src/github.com/mguggi/oraclebeat/module/oracle/vendor/gopkg.in/goracle.v2/conn.go:112 +0xa9
database/sql.(*driverConn).finalClose.func2()
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:467 +0x49
database/sql.withLock(0x405f9e0, 0xc420011ce0, 0xc42019bd38)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:2867 +0x65
database/sql.(*driverConn).finalClose(0xc420011ce0, 0xc42019be58, 0xc42019be48)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:465 +0x128
database/sql.(finalCloser).(database/sql.finalClose)-fm(0xc42008d108, 0xc420346ea0)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:564 +0x2f
database/sql.(*DB).Close(0xc42008d0e0, 0x0, 0x0)
/home/mguggi/public/go-1.9.2/src/database/sql/sql.go:678 +0x31b
github.com/mguggi/oraclebeat/module/oracle/sgastats.(*MetricSet).Close(0xc42008d7c0, 0x406ec20, 0xc42008d7c0)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/module/oracle/sgastats/sgastats.go:110 +0xd7
github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module.(*metricSetWrapper).close(0xc420118d40, 0x0, 0x0)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:257 +0x64
github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module.(*Wrapper).Start.func1(0xc4200193e0, 0xc420020ba0, 0xc4202504e0, 0xc420118d40)
/home/mguggi/go/src/github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:111 +0xbe
created by github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module.(*Wrapper).Start
/home/mguggi/go/src/github.com/mguggi/oraclebeat/vendor/github.com/elastic/beats/metricbeat/mb/module/wrapper.go:106 +0x146
It looks like that the statement hangs in the dpiConn_release function. I've seen this behaviour only on two 12.1.0.2 databases. How can I get additionaly debug inoformation? Is there a debug mode or somthing else?
If I send array parameters that are larger than some internal limit currently the app panics with slice out of bounds error.
goroutine 43 [running]:
gopkg.in/goracle%2ev2.(*conn).newVar(0xc424d1e6c0, 0x7db00000001, 0xbbd, 0x2710, 0x0, 0xc431b74000, 0xf430, 0xc4335c8dc8, 0x3, 0x3, ...)
/home/kokteyl/go/src/gopkg.in/goracle.v2/conn.go:234 +0x9ef
gopkg.in/goracle%2ev2.(*statement).bindVars(0xc4362792b0, 0xc4202304e0, 0x9, 0xa, 0x957eb8, 0xc4335c9408, 0xc43036d620)
/home/kokteyl/go/src/gopkg.in/goracle.v2/stmt.go:643 +0x1f33
gopkg.in/goracle%2ev2.(*statement).ExecContext(0xc4362792b0, 0xda12a0, 0xc4200180a8, 0xc4202304e0, 0x9, 0xa, 0x0, 0x0, 0x0, 0x0)
/home/kokteyl/go/src/gopkg.in/goracle.v2/stmt.go:181 +0x196
database/sql.ctxDriverStmtExec(0xda12a0, 0xc4200180a8, 0xda15a0, 0xc4362792b0, 0xc4202304e0, 0x9, 0xa, 0x9, 0xa, 0x0, ...)
/usr/local/go/src/database/sql/ctxutil.go:66 +0x22e
database/sql.resultFromStatement(0xda12a0, 0xc4200180a8, 0xda0760, 0xc424d1e6c0, 0xc433443750, 0xc4335c9c50, 0xa, 0xa, 0x0, 0x0, ...)
/usr/local/go/src/database/sql/sql.go:2109 +0x184
database/sql.(*DB).execDC(0xc4201f4460, 0xda12a0, 0xc4200180a8, 0xc43c3cbd50, 0xc4249ce8a8, 0x9522ce, 0xa6, 0xc4335c9c50, 0xa, 0xa, ...)
/usr/local/go/src/database/sql/sql.go:1272 +0x2c7
database/sql.(*DB).exec(0xc4201f4460, 0xda12a0, 0xc4200180a8, 0x9522ce, 0xa6, 0xc4249cec50, 0xa, 0xa, 0x1, 0x882480, ...)
/usr/local/go/src/database/sql/sql.go:1238 +0x13e
database/sql.(*DB).ExecContext(0xc4201f4460, 0xda12a0, 0xc4200180a8, 0x9522ce, 0xa6, 0xc433443c50, 0xa, 0xa, 0x882480, 0x59fc6701, ...)
/usr/local/go/src/database/sql/sql.go:1216 +0xe1
database/sql.(*DB).Exec(0xc4201f4460, 0x9522ce, 0xa6, 0xc4249cec50, 0xa, 0xa, 0x3b, 0x0, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:1230 +0x85
Current limit in my application for array bulk insert is 10000, is the internal limit enforced by oracle or should it be adjustable by the library user.
If the former than maybe this limit should be handled and returned as an error to the caller. If the latter then a function or per context knob can be given to the caller. Or maybe simply a higher limit can be set as default and just the panic can be handled.
I'm testing goracle.v2 on a gorm branch to support Oracle. I've gotten the following error when executing a query (insert and update) that uses a pointer argument. For example:
t := time.Now()
rows, err := sql.DB.Query("SELECT * FROM animals WHERE updated_at=?", &t)
Error: 1. arg: unknown type *time.Time
The goracle.v2 return this error for all pointer types ...
s := "dog"
rows, err := db.DB().Query("SELECT * FROM animals WHERE name=?", &s)
Error: 1. arg: unknown type *string
The bindVars method checks only the "concrete" types, right?
Hi,
I'm trying to use the DescribeQuery function in orahlp.go. However I receive this error:
slice is bigger (32767) than the maximum (1024)
In drv.go, I can work around it by changing this:
DefaultArraySize = 1 << 10
To this:
DefaultArraySize = 1 << 15
However, I then receive this error:
dpiStmt_execute(mode=32 arrLen=32767): ORA-06561: given statement is not supported by package DBMS_SQL
Is there a working example somewhere of using DescribeQuery (e.g. csvdump) that I can reference? Looking at the underlying driver, it seems maybe utilizing DPI_MODE_EXEC_DESCRIBE_ONLY
would be applicable.
Also, in drv.go, it says:
const (
// DefaultFetchRowCount is the number of prefetched rows by default (if not changed through ContextWithFetchRowCount).
DefaultFetchRowCount = 1 << 8
// DefaultArraySize is the length of the maximum PL/SQL array by default (if not changed through ContextWithArraySize).
DefaultArraySize = 1 << 15
)
But I don't see ContextWithFetchRowCount
nor ContextWithArraySize
mentioned anywhere else in the source.
We are calling a stored procedure like so:
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
defer cancel()
_, err := db.updateLastScheduledStmt.ExecContext(ctx, mb.ID)
return err
This about 1-10 times per day, this call never returns for unknown reasons, causing our process to just hang there until our cluster kills the whole pod 30 minutes later for failing a health check.
Not sure if it matters, but updateLastScheduledStmt
is a *sqlx.Stmt
, which was successfully prepared and used successfully in previous calls. This is in the hot path of this app, and this call is being made about once per second or more, 100% successfully except when it doesn't return those few times per day.
I don't know if the issue is specific to prepared stored procedures, or if timeout-contexts generally fail to cancel all database calls.
I am using git commit 94abed0 of goracle.
I'm switching more and more queries over to this driver, and ran into the following issue with one of my queries.
For each row in the query's output, the driver is somehow outputting the word RAW
each time I call Next
This is extremely annoying and is causing our logs to get filled up with the majority of lines being just the word "RAW".
The oracle data type of one of the fields is RAW
, which is probably the cause of this. But no matter what, this output shouldn't happen.
I am not passing a logger in the context to the query, fyi.
Here is my code:
type IP struct {
IP string
ID int64
}
func (db DB) getIPList() ([]IP, error) {
rval := []IP{}
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
defer cancel()
var rset driver.Rows
_, err := db.conn.ExecContext(ctx, `begin auser.get_ip_list(:v1); end;`, sql.Out{Dest: &rset})
if err != nil {
return rval, err
}
defer func() {
if closeErr := rset.Close(); closeErr != nil {
log.WithError(closeErr).Error("Error closing driver rows")
}
}()
rows := make([]driver.Value, len(rset.Columns()))
for {
if err := rset.Next(rows); err != nil { // <------ Writes RAW to stdout
if err == io.EOF {
break
}
return rval, err
}
var ip IP
log.Debug("Scanning IP")
if scanErr := utilsql.Scan(rows, &ip.IP, &ip.ID); scanErr != nil {
return rval, scanErr
}
rval = append(rval, ip)
log.WithField("ip", ip).Debug("Scanned IP")
}
return rval, nil
}
The output:
RAW
RAW
RAW
RAW
RAW
...and so on....
The actual procedure:
PROCEDURE get_ip_list(o_csr OUT o_as_cur) IS
BEGIN
IF (o_csr%ISOPEN) THEN
CLOSE o_csr;
END IF;
OPEN o_csr FOR
SELECT
ip,
id
FROM
ip_map_t;
END;
The table:
CREATE TABLE "xxx"."IP_MAP_T"
( "IP" RAW(4),
"ID" NUMBER
)
I was using aa4eaea , and when I updated to 7a9e6c2 I noticed a huge performance hit.
To test I used the database/sql compatible driver, querying and fetching 1 million records. I'm using the same machine and database, just changing the repository between tests.
Old version: 56k rows/second
New version: 30k rows/second
I replaced the Next() func with the old one and the performance increased dramatically, but it is still a little slower than the old one, so I suspect something else is also to blame:
New version with old Next(): 48k rows/second
I know this is not a problem with goracle so just asking to get an idea and let you know.
In the past (v2.1.15 and below) it was possible to get goracle with dep
and copy the odpi
directory to get it working (dep was not able to get the submodule dependency).
When you copied the odpi
folder to the repo, I thought it be will solved but it fails in a different way now (see this issue)
Now, v2.1.16+ versions all fail.
Do you think this is because there are two references in project for odpi
? One copied folder and one submodule?
Hi,
Cannot get the latest master in go get. Is there any other way to be able to get it, there are some new bug fixes in master.
When scanning a NULL
number value, the value in a NUMBER
field that has precision comes in as 0
instead of NULL
. However, if the NUMBER
field is created without a precision value, goracle does recognize the value as NULL
.
Recreating
Create a new table and fill with dummy values
CREATE TABLE number_test (
precisionNum NUMBER(5),
precScaleNum NUMBER(5, 0),
normalNum NUMBER
);
INSERT ALL
INTO number_test (precisionNum, precScaleNum, normalNum) VALUES (4, 65, 123)
INTO number_test (precisionNum, precScaleNum, normalNum) VALUES (NULL, NULL, NULL)
SELECT 1 FROM DUAL
;
COMMIT;
Pull values in go
package main
import (
"database/sql"
"fmt"
_ "gopkg.in/goracle.v2"
)
func main() {
connString := "user/password@//host/service"
db, _ := sql.Open("goracle", connString)
defer db.Close()
rows, _ := db.Query("SELECT precisionNum, precScaleNum, normalNum FROM number_test")
defer rows.Close()
var precisionNum, recScaleNum, normalNum sql.NullInt64
for rows.Next() {
rows.Scan(&precisionNum, &recScaleNum, &normalNum)
fmt.Println(precisionNum, recScaleNum, normalNum)
}
}
Got:
{4 true} {65 true} {123 true}
{0 true} {0 true} {0 false}
Expected:
{4 true} {65 true} {123 true}
{0 false} {0 false} {0 false}
There are some types in the sql
package that I think should be available. The specific types that don't look like they are working are sql.NullString
, []sql.NullString
, and []sql.NullFloat64
.
[]sql.NullFloat64
seems to be handled in dataSetNumber
here, but isn't handled in the switch
statement starting here.
sql.NullString
and []sql.NullString
don't appear in either area.
Code that should work:
CREATE TABLE CHAR_TEST (
"CHARS" VARCHAR2(10 BYTE),
"FLOATS" NUMBER(10, 2)
);
package main
import (
"database/sql"
"fmt"
_ "gopkg.in/goracle.v2"
)
func main() {
connString := "user/password@//host/service"
db, _ := sql.Open("goracle", connString)
tx, _ := db.Begin()
_, err := tx.Exec(
"INSERT INTO CHAR_TEST VALUES(:CHARS, :FLOATS)",
[]sql.NullString{
sql.NullString{"dog", true},
sql.NullString{"", false},
sql.NullString{"cat", true},
},
[]sql.NullFloat64{
sql.NullFloat64{3.14, true},
sql.NullFloat64{12.36, true},
sql.NullFloat64{0.0, false},
},
)
fmt.Printf("Error: %v\n", err)
tx.Commit()
}
Expected
Error: <nil>
Got
Error: awaited [][]byte/[]string/[]Number, got []sql.NullString ([]sql.NullString{sql.NullString{String:"dog", Valid:true}, sql.NullString{String:"", Valid:false}, sql.NullString{String:"cat", Valid:true}})
Hi Tamas,
After some testing and with the final performance improvements I deployed our service to production last week. I wanted to report back some issues that I've observed. The previous version of this service was using goracle.v1 as the driver but because it used lots oracle specific features I couldn't use the database/sql compatible driver, and instead used the ora package for calling procedures and passing array variables to db. The problem with that was I had to implement the pooling since I needed to limit max connection and max idle connections from the db. The old service was working without issues but since goracle.v1 didn't support newer go versions I wanted to try goracle.v2 so that I can use the database/sql connection pooling along with calling procedures and out parameters and so on.
After the upgrade I realized our db server's CPU increased a lot compared to our other service, and the system started crashing after logging the following:
acquireConnection[oracle://db:password@SID))?connectionClass=GORACLE&poolIncrement=1&poolMaxSessions=1000&poolMinSessions=1&sysdba=0&sysoper=0]: ORA-12537: TNS:connection closed
After each restart getting the first connection from DB took somewhere between 1 to 30 seconds and couldn't balance itself under load and crashed again after a few minutes.
After converting to the old service everything went back to normal, and the CPU on the DB server decreased to 10% of what it was while handling much larger load (x5).
Below there is a sample of how I am using and pooling connections with goracle.v1, I thought it might give some ideas on this issue. Please let me know if you need anything else.
type conn struct {
db *DB
cx *oracle.Connection
id int
mu sync.Mutex
inUse bool
numCursors int
createdAt time.Time
}
func (c *conn) sessionLocked() *session {
if c.cx == nil {
return nil
}
if !c.cx.IsConnected() {
return nil
}
cu := c.cx.NewCursor()
c.numCursors++
//log.Printf("conn %d - new session: %d", c.id, c.numCursors)
if c.numCursors == c.db.maxCursors {
c.inUse = true
}
return &session{c, cu, false}
}
In goracle.v1 ora functions were defined on cursors not the connections. So I pooled connections but didn't remove them from the pool until number of open cursors reached a certain limit which is 5 currently. So if I had max open connections of 10 I could perform 50 oracle functions over them before running out of connections. I am not sure if this improves performance or the same in goracle.v2 also but there is definitely a regression in using or getting the connections from db in the new version.
All references -lclntsh are hardcoded. This causes problems on Windows because we need to change them to -loci, as described on the readme, or else it won't work.
Other drivers take different approaches:
Go-oci8 uses pkg-config and needs a oci8.pc file where the library name is specified:
Libs: -L${oralib} -L${gcclib} -loci
Rana suggests defining an environment variable with the library name (Goracle only needs -L):
$env:CGO_LDFLAGS="-LC:\Oracle\instantclient_12_1_64\sdk\lib\msvc -loci"
I like Rana's way of dealing with this, because it's simpler to implement and use.
@tgulacsi what do you think about removing the hardcoded references from Goracle and instead relying on the environment variable?
Hi, @tgulacsi
I have a program for IoT platform ,
When we store item into oracle using goracle,
program sometimes hang up and this may cause one core of cpus up to 100%
therefore when I use a 8-core cpu do benchmark test, cpu is continuous up to 12%
After dump pprof , I found that every thread is locked by goracle , see attachment
Last function call maybe : github.com/go-goracle/goracle._Cfunc_dpiPool_acquireConnection
Or: github.com/go-goracle/goracle._Cfunc_dpiConn_release
Does it support Windows?
Hi Tamas,
I just upgraded one of our services to go 1.9.2 after changing db side from goracle.v1 to goracle.v2. This service bulk inserts into DB in groups of max 10K items. The old service which used go1.5.4 and goracle.v1 completes the insert around 80ms as can be seen from log line below:
successfully inserted 6460 impressions generated query in 60.960046ms, inserted in 39.937928ms
The new service that uses go1.9.2 and goracle.v2 almost takes 300 ms for a similar group. Is it possible this is related to the driver or is this simply the overhead of using sql.Database.
successfully inserted 7493 impressions generated query in 1.192113ms, inserted in 272.313709ms
goracle v1 side used go 1.5.4
goracle v2 code uses 1.9.2
Below are the details of the db function parameters and their usage on the golang side.
ORACLE:
TYPE cx_array_date IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
TYPE cx_array_string IS TABLE OF VARCHAR2 (1000)
INDEX BY BINARY_INTEGER;
TYPE cx_array_num IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE P_BULK_INSERT_IMP (VIMP_DATES cx_array_date,
VIMP_KEYS cx_array_string,
VIMP_IP cx_array_num,
VIMP_ZONE cx_array_num,
VIMP_PLAN cx_array_num,
VIMP_BANNER cx_array_num,
VIMP_REFERRER cx_array_string,
VIMP_COUNTRY cx_array_string,
VIMP_REGION cx_array_string);
GO 1.5.4 Goracle V1:
dates := make([]interface{}, len(imps))
keys := make([]interface{}, len(imps))
ips := make([]interface{}, len(imps))
zones := make([]interface{}, len(imps))
plans := make([]interface{}, len(imps))
banners := make([]interface{}, len(imps))
referrers := make([]interface{}, len(imps))
countries := make([]interface{}, len(imps))
regions := make([]interface{}, len(imps))
for i, imp := range imps {
dates[i] = imp.CreDate
keys[i] = imp.UserKey
ips[i] = imp.IP
zones[i] = imp.ZoneID
plans[i] = imp.PlanID
banners[i] = imp.BannerID
referrers[i] = imp.Referrer
countries[i] = imp.Country
regions[i] = imp.Region
}
dateType := oracle.DateTimeVarType
strType := oracle.StringVarType
numType := oracle.Int64VarType
oraDates, err := session.cu.NewArrayVar(dateType, dates, 0)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraKeys, err := session.cu.NewArrayVar(strType, keys, 23)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraIPs, err := session.cu.NewArrayVar(numType, ips, 0)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraZones, err := session.cu.NewArrayVar(numType, zones, 0)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraPlans, err := session.cu.NewArrayVar(numType, plans, 0)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraBanners, err := session.cu.NewArrayVar(numType, banners, 0)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraReferrers, err := session.cu.NewArrayVar(strType, referrers, 1000)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraCountries, err := session.cu.NewArrayVar(strType, countries, 3)
if err != nil {
log.Println(err)
return err, 0, 0
}
oraRegions, err := session.cu.NewArrayVar(strType, regions, 100)
if err != nil {
log.Println(err)
return err, 0, 0
}
t2 := time.Now()
_, err = session.cu.CallProc(procInsertImpressions, []interface{}{oraDates, oraKeys, oraIPs, oraZones, oraPlans, oraBanners, oraReferrers, oraCountries, oraRegions}, nil)
t3 := time.Now()
Where session.cu is oracle.Cursor from goracle.v1.
GO 1.9.2 Goracle V2:
t1 := time.Now()
dates := make([]time.Time, len(imps))
keys := make([]string, len(imps))
ips := make([]int, len(imps))
zones := make([]int, len(imps))
plans := make([]int, len(imps))
banners := make([]int, len(imps))
referrers := make([]string, len(imps))
countries := make([]string, len(imps))
regions := make([]string, len(imps))
for i, imp := range imps {
dates[i] = imp.CreDate
keys[i] = imp.UserKey
ips[i] = int(imp.IP)
zones[i] = imp.ZoneID
plans[i] = imp.PlanID
banners[i] = imp.BannerID
referrers[i] = imp.Referrer
countries[i] = imp.Country
regions[i] = imp.Region
}
t2 := time.Now()
_, err := db.Exec(procInsertImpressions, goracle.PlSQLArrays, dates, keys, ips, zones, plans, banners, referrers, countries, regions)
t3 := time.Now()
Where db is sql.DB from standart library
When I map the results to a generic map[string]interface{}
, if I directly serialize it to json
I will get all values as strings so I need to do this funky conversion shown below.
Would it be possible for the driver to use native go types so that the result will be interpreted correctly after the query mapping?
I may be doing something wrong or this might be related to the wrapper library (sqlx
) I am using. Sorry if that is the case.
func ConvertGenericMapping(result map[string]interface{}, dbType string) map[string]interface{} {
converted := make(map[string]interface{})
switch dbType {
case ORACLE:
for k, v := range result {
switch v.(type) {
case goracle.Number:
value := string(v.(goracle.Number))
if strings.Contains(value, ".") {
converted[k], _ = strconv.ParseFloat(value, 64)
} else {
converted[k], _ = strconv.ParseInt(value, 10, 64)
}
default:
converted[k] = v
}
}
}
return converted
}
when i use the latest code from rana/ora and goracle today, It's better than before in performance
starting test oracle database driver: ora
start get sql.DB: 2017-12-14 14:05:36
starting query: 2017-12-14 14:05:37
query finished: 2017-12-14 14:05:42
read one primary key of type char(20),record count: 424698 用时 5162 ms
starting test oracle database driver: goracle
start get sql.DB: 2017-12-14 14:05:42
starting query: 2017-12-14 14:05:42
query finished: 2017-12-14 14:05:45
read one primary key of type char(20),record count: 424698 用时 2584 ms
Hi,
I have write a simple code to fetch the 2000000 rows from table use three golang oracle driver:
My ENV:
centos 6.6 64bit
golang 1.5.3
The code:
`func FetchData(db *sql.DB) []int {
var r []int
sqlstr := `SELECT id FROM xxxx.geolocation`
// Prepare statement for reading data
stmtOut, err := db.Prepare(sqlstr)
checkErr(err)
defer stmtOut.Close()
rows, err := stmtOut.Query()
checkErr(err)
for rows.Next() {
var id int
err = rows.Scan(&id)
checkErr(err)
r = append(r, id)
}
return r
}`
When building a project that uses goracle as a dependency, it fails:
/tmp/go-build975603239/b376/_x004.o: In function `dpiUtils__freeMemory':
../../../gopkg.in/goracle.v2/odpi/src/dpiUtils.c:62: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiUtils.c:63: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__allocateMem':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:519: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiGen__setRefCount':
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:241: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:242: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiGen__endPublicFn':
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:187: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:191: undefined reference to `dpiHandlePool__release'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:188: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__freeMem':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1247: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__reallocMem':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:2373: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiGlobal__initialize':
../../../gopkg.in/goracle.v2/odpi/src/dpiGlobal.c:208: undefined reference to `dpiDebug__initialize'
/tmp/go-build975603239/b376/_x004.o: In function `dpiError__set':
../../../gopkg.in/goracle.v2/odpi/src/dpiError.c:176: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiError.c:177: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiUtils__allocateMemory':
../../../gopkg.in/goracle.v2/odpi/src/dpiUtils.c:32: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiUtils.c:33: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiGen__allocate':
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:152: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:152: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:153: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__handleFree':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1280: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1281: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__descriptorFree':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1152: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1153: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__arrayDescriptorFree':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:589: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:590: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiOci__envNlsCreate':
../../../gopkg.in/goracle.v2/odpi/src/dpiOci.c:1171: undefined reference to `dpiDebugLevel'
/tmp/go-build975603239/b376/_x004.o: In function `dpiError__check':
../../../gopkg.in/goracle.v2/odpi/src/dpiError.c:48: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiError.c:49: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiStmt__close':
../../../gopkg.in/goracle.v2/odpi/src/dpiStmt.c:328: undefined reference to `dpiHandleList__removeHandle'
/tmp/go-build975603239/b376/_x004.o: In function `dpiLob__close':
../../../gopkg.in/goracle.v2/odpi/src/dpiLob.c:101: undefined reference to `dpiHandleList__removeHandle'
/tmp/go-build975603239/b376/_x004.o: In function `dpiContext_create':
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:130: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:137: undefined reference to `dpiDebug__print'
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:131: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiLob__allocate':
../../../gopkg.in/goracle.v2/odpi/src/dpiLob.c:39: undefined reference to `dpiHandleList__addHandle'
/tmp/go-build975603239/b376/_x004.o: In function `dpiVar__extendedPreFetch':
../../../gopkg.in/goracle.v2/odpi/src/dpiVar.c:564: undefined reference to `dpiHandleList__addHandle'
/tmp/go-build975603239/b376/_x004.o: In function `dpiStmt__prepare':
../../../gopkg.in/goracle.v2/odpi/src/dpiStmt.c:904: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiStmt.c:908: undefined reference to `dpiHandleList__addHandle'
../../../gopkg.in/goracle.v2/odpi/src/dpiStmt.c:905: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiEnv__initError':
../../../gopkg.in/goracle.v2/odpi/src/dpiEnv.c:174: undefined reference to `dpiHandlePool__acquire'
/tmp/go-build975603239/b376/_x004.o: In function `dpiGen__startPublicFn':
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:263: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiGen.c:264: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiContext_destroy':
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:156: undefined reference to `dpiDebugLevel'
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:163: undefined reference to `dpiDebug__print'
../../../gopkg.in/goracle.v2/odpi/src/dpiContext.c:157: undefined reference to `dpiDebug__print'
/tmp/go-build975603239/b376/_x004.o: In function `dpiEnv__init':
../../../gopkg.in/goracle.v2/odpi/src/dpiEnv.c:112: undefined reference to `dpiHandlePool__create'
/tmp/go-build975603239/b376/_x004.o: In function `dpiEnv(float, long double,...)(...)':
../../../gopkg.in/goracle.v2/odpi/src/dpiEnv.c:32: undefined reference to `dpiHandlePool(float, long double,...)(...)'
/tmp/go-build975603239/b376/_x004.o: In function `dpiPool_create':
../../../gopkg.in/goracle.v2/odpi/src/dpiPool.c:338: undefined reference to `dpiHandlePool__release'
/tmp/go-build975603239/b376/_x004.o: In function `dpiConn(float, long double,...)(...)':
../../../gopkg.in/goracle.v2/odpi/src/dpiConn.c:384: undefined reference to `dpiHandleList(float, long double,...)(...)'
../../../gopkg.in/goracle.v2/odpi/src/dpiConn.c:388: undefined reference to `dpiHandleList(float, long double,...)(...)'
/tmp/go-build975603239/b376/_x004.o: In function `dpiConn__create':
../../../gopkg.in/goracle.v2/odpi/src/dpiConn.c:239: undefined reference to `dpiHandleList__create'
../../../gopkg.in/goracle.v2/odpi/src/dpiConn.c:241: undefined reference to `dpiHandleList__create'
/tmp/go-build975603239/b376/_x004.o: In function `dpiConn_create':
../../../gopkg.in/goracle.v2/odpi/src/dpiConn.c:1244: undefined reference to `dpiHandlePool__release'
collect2: ld returned 1 exit status
Using gcc 4.4.7 on CentOS 6.9. Is there anything else that needs to be done in order to get this to build?
hi,thanks for your goracle, when i execute go get gopkg.in/goracle.v2 ,system prompt this
my os is windows 10,not support for windows?
c:\go\go get gopkg.in/goracle.v2
I:/mingw64/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/7.1.0/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -lodpic
I:/mingw64/mingw64/bin/../lib/gcc/x86_64-w64-mingw32/7.1.0/../../../../x86_64-w64-mingw32/bin/ld.exe: cannot find -ldl
collect2.exe: error: ld returned 1 exit status
The docs mention that prefetch count can be changed by adding a goracle.FetchRowCount(1000) argument to the call of Query
So following this is it enough to just do(?):
rows, err := db.Query(query.QueryText, goracle.FetchRowCount(1000) )
Sorry, I am a bit new to the database/sql
part of Go
I have a stored procedure that requires an 'out' parameter.
I am attempting to access it like so:
var data []struct {
RegexpID goracle.Number
Regexp string
OtherID goracle.Number
RegexpMatchType goracle.Number
}
result, err := conn.ExecContext(ctx, `begin myuser.get_regexs(:v1); end;`, goracle.PlSQLArrays, sql.Out{Dest: &data})
This gives me the following error: 1. arg: unknown type []struct { RegexpID goracle.Number; Regexp string; OtherID goracle.Number; RegexpMatchType goracle.Number }
I have also tried making data a []interface{}
, with similar results.
I tried reading the examples in your tests, but it looks like all of them are in-out variables, and not rset's.
The stored procedure looks approximately like this:
PROCEDURE get_regexs( o_csr OUT o_as_cur) IS
BEGIN
IF (o_csr%ISOPEN) THEN
CLOSE o_csr;
END IF;
OPEN o_csr FOR
SELECT
t1.regexp_id,
t1.regexp,
t1.other_id,
t1.regexp_match_type
FROM
my_regexp_t t1
WHERE
t1.status = 1;;
END;
And here is approximately the perl code I am trying to migrate to golang:
my $regex_sth = $dbh->prepare(
'begin myuser.get_regexs(:v1); end;'
);
$regex_sth->bind_param_inout(':v1', \$csr, 0, { ora_type => ORA_RSET });
$regex_sth->execute();
while ( my @rx_info = $csr->fetchrow_array() ) {
# stuff...
}
$regex_sth->finish();
How do I get this to work with this oracle driver?
p, err := ora.ParseConnString("cc/\\\"c@c*1\\\"@//192.168.1.1/cc")
if err != nil {
log.Fatalln(err)
}
log.Println(p.String())
db, err := sql.Open("goracle", "cc/\\\"c@c*1\\\"@//192.168.1.1/cc")
if err != nil {
log.Fatalln(err)
}
defer db.Close()
if err = db.Ping(); err != nil {
log.Fatalln(err)
}
log.Println("connect ok")
unable to connect
Could you extend oracle.Variable and add two methods:
func (v Variable) AllocatedElements() uint
func (v Variable) Size() uint
?
It will help me to create the mechanism of record of executed cursor and the subsequent reexec them for reproduction of load of a DB
This is more a question than an issue.
I've been using the mattn oracle driver for some time now, mostly because it compiles a lot faster than the rana driver.
However, it doesn't appear to support stored procedures, which I need to use.
So, I now need to choose between rana and goracle (and since you develop both, you are good person to ask).
Would you say goracle is production ready?
Are there any parts of goracle (or specific kinds of oracle queries or things) that I should avoid for production applications?
Are there any known bugs or major missing features?
thx!
Cannot figure out how to call stored procedure with mixed parameters and one of multiple cursors
create or replace procedure test_cursor(user_name in string, cur1 in out sys_refcursor, cur2 in out sys_refcursor) is
begin
open cur1 for select user_name as uuser, sysdate as today from dual;
open cur2 for select user_name as uuser, sysdate+1 as tomorrow from dual;
end test_cursor;
Is it possible?
Hi
I am trying to read columns that have type NVARCHAR2 on our oracle DB and I am getting ????.
However when I tried a method you suggested in one of the oci-8 issues
mattn/go-oci8#10
mainly selecting HEX from the DB and converting it back to string on the app things work ok.
I was just wondering is there any other way to do this, since it seems kind of a hacky way to do it.
I am porting one of our services to go, and I am not having this issue on .NET side for example. Maybe I am missing some extra configuration on the go side.
Any help would be appreciated.
Hi Tamas,
I am testing goracle.v2 with one of our apps which used goracle.v1 previously.
I get the following error when mapping a number column to an int field in a go struct. I am using sqlx for mappings.
sql: Scan error on column index 3: converting driver.Value type float64 ("2.934433e+06") to a int: invalid syntax
I remember we worked on this very issue when you were developing goracle.v1 and somehow fixed it. When I tried updating to rana/ora I again had the same issue and you couldn't fix it there. Is it possible to fix this in goracle.v2 and can I be of any help?
Hi,
I am trying to use the cursor.ExecuteMany function with an insert into statement. However I am getting a "Variable_SetSingleValue: array size exceeded" error.
My code is like the following:
insertParams := make([]map[string]interface{}, len(imps))
for i, imp := range imps {
insertParams[i] = map[string]interface{}{
"impdate": imp.CreDate,
"userkey": imp.UserKey,
"ip": imp.IP,
"zoneid": imp.ZoneID,
"planid": imp.PlanID,
"bannerid": imp.BannerID,
"referrer": imp.Referrer,
"country": imp.Country,
"region": imp.Region,
"server": bs.serverID,
"ismerge": imp.IsMergeExecute,
"sessionid": imp.SessionID,
}
}
err := session.ExecuteMany("INSERT INTO TBL_TEMP_IMPRESSION VALUES (:impdate,:userkey,:ip,:zoneid,:planid,:bannerid,:referrer,:country,:region,:server,:ismerge,:sessionid)", insertParams)
I tried to follow through the code and found a possible bug. And below is my findings.
ExecuteMany calls setVariablesByName which in turn calls setBindVariableHelper. While initializing the bind variables NewVariableByValue is called. Up to this point the number of elements the execute many statement contains is passed along correctly.
However inside NewVariableByValue the number of elements is overridden by this piece of code. I am not familiar with cx_oracle myself so I cannot be sure if this is a bug or expected behavior.
oracle/variable.go lines 626 - 628
if varType, size, numElements, err = VarTypeByValue(value); err != nil {
return
}
The VarTypeByValue function is not aware of numElements and thus returns 0 for types other than *oracle.Variable or oracle.Variable. In this case a new variable is created with allocatedelement count with 0.
This in turn causes the following piece of code to return the error
Variable_SetSingleValue: array size exceeded
oracle/variable.go lines 1315 - 1317 setSingleValue function
if arrayPos >= v.allocatedElements {
return errgo.New("Variable_SetSingleValue: array size exceeded")
}
It seems to me that VarTypeByValue function has a different meaning for numElements. It treats that variable as the length of the value if it is an array value. However up until that point that variable's meaning is the length of the variables the execute many statement should bind. It think this function should be somehow made aware of the numElements it is overriding or the returned value shouldn't override the numElements variable since they have completely different meanings. However since I am not familiar with the cx_oracle library I am not sure what kind of consequences that may have.
I know this is not really a necessity or priority, but is it possible to provide a test coverage info on the repo?
Trying to get accepted here: avelino/awesome-go#1841
I have some existing code that I would like to use where the procedures return a sys_refcursor.
I've tried using sql.Out() with various variable types, and while it doesn't produce an obvious error if I do something like:
var outRecs driver.Rows
_, err = stmt.ExecContext(ctx, sql.Out{Dest: &outRecs})
if err != nil {
fmt.Println("Error:", err)
}
It doesn't give me an error, and while outRecs has something useful -- I can see my field names if I do fmt.println(outRecs)
-- there is no obvious way to access the components, and I'm pretty sure accessing sql.driver isn't what I should be doing here anyway.
Any tips?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.