Hi,

I have write a simple code to fetch the 2000000 rows from table use three golang oracle driver:

• gopkg.in/goracle.v1 ==> about 22 seconds
• github.com/mattn/go-oci8 ==> about 34 seconds
• gopkg.in/rana/ora.v3 ==> about 660 seconds

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 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}

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.

• Drivers that currently hold on to the destination buffer provided by driver.Rows.Next should ensure they no longer write to a buffer assigned to the destination array outside of that call. Drivers must be careful that underlying buffers are not modified when closing driver.Rows.
• Drivers that want to construct a sql.DB for their clients can now implement the Connector interface and call the new sql.OpenDB function, instead of needing to encode all configuration into a string passed to sql.Open.
• Drivers that want to parse the configuration string only once per sql.DB instead of once per sql.Conn, or that want access to each sql.Conn's underlying context, can make their Driver implementations also implement DriverContext's new OpenConnector method.
• Drivers that implement ExecerContext no longer need to implement Execer; similarly, drivers that implement QueryerContext no longer need to implement Queryer. Previously, even if the context-based interfaces were implemented they were ignored unless the non-context-based interfaces were also implemented.
• To allow drivers to better isolate different clients using a cached driver connection in succession, if a Conn implements the new SessionResetter interface, database/sql will now call ResetSession before reusing the Conn for a new client.

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.

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.

Is there a way to configure the client to fetch CLOBs as strings ?

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
}

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.

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,

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.

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!

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.

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?

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.

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?

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?

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.

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,

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.

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

goroutine.txt

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

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).

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 --

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'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?

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 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 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 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 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

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?

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?

Does it support Windows?

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?

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

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.

1. The timezones map will never initialized.
2. The timezonesMu.Unlock() call is outside from the if statement.

I use the following environment:

• oracle database 12.1.0.2
• oracle client: 12.2.0.1
• goracle 2.1.12

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

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?

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,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

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

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

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]

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 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

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?

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.

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

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?