issue with exec function calls and binds about ora HOT 8 CLOSED

I've never tried such a thing through database/SQL. With database/SQL, use
Query and Scan, otherwise use ora.OpenEnv ... ses.PrepAndExe.

Maybe we can hack a solution through database/SQL, but that always will be
riddled with hidden traps.
See the issue about selecting a LOB with QueryRow, for example.

MichaelS11 [email protected] ezt írta (időpont: 2015. okt. 26., H
4:22):

Greetings,

Trying to run a PL/SQL function and get the value back but it does not
seem to be working. Trying to call something like:

begin :1 = MY_FUNC(); end;

The bind variable passed does not get updated with the value from the
PL/SQL function. For testing, made the PL/SQL simpler ran the following Go:

...

var int1 int64
var pInt1 *int64
int1 = 5
pInt1 = &int1

_, err = db.Exec("begin select 10 into :1 from dual; end;", pInt1)

...

fmt.Println(int1)
fmt.Println(pInt1)
fmt.Println(*pInt1)

The output was still 5, instead of the wanted 10.

So I did some digging and found that "database/sql" does a convert of
values before passing them to the ora driver. Here are a couple of notes if
you want to did into the database/sql" code. In convert.go, driverArgs gets
called, which for each arg, calls
driver.DefaultParameterConverter.ConvertValue which is in types.go. That in
turn does a rv := reflect.ValueOf(v) and then calls
defaultConverter{}.ConvertValue(rv.Elem().Interface()). So basically
returns the interface of the pointer. The short version is it converts any
pointers into non-pointers. For example a *int64 into int64.

So on to the ora code. Looking at stmt.go there is a huge switch statement
that for each params does switch value := params[n].(type). The issue is
that the pointer types will never get called because the params have been
converted to non-pointers. So for example, the case *int64: will never get
called. The solution there might be to use reflect type of and get the
kind, which will still be equal to reflect.Ptr. Then you can make to sets
of switch value := params[n].(type) statements, one for pointers and one
for other. That seems pretty easy and straight forward, however was not
sure if doing that would break other code. Plus would still need to covert
the non-pointer back to a pointer.

I did some testing (hack job) in bndInt64.go and found if you passed
C.OCINumberToInt a valid pointer, it did return the wanted 10. So the good
news is it seems quite fixable and one do not have to mess with the C code.

Hope that provided enough information to fix it. Happy to help more if I
can.

Best regards,

Michael

—
Reply to this email directly or view it on GitHub
#48.

from ora.

If you use the driver directly, without database/sql, are you able to receive the PL/SQL function output in Go?

from ora.

Yes, using the driver directly works.

Another option to get the database/sql to work is to implement ColumnConverter. From driver.go in database/sql/driver, it says:

// ColumnConverter may be optionally implemented by Stmt if the
// statement is aware of its own columns' types and can convert from
// any type to a driver Value.
type ColumnConverter interface {
// ColumnConverter returns a ValueConverter for the provided
// column index. If the type of a specific column isn't known
// or shouldn't be handled specially, DefaultValueConverter
// can be returned.
ColumnConverter(idx int) ValueConverter
}

from ora.

Good to know it works with the driver directly.
I use the direct driver for my projects as well.
At the moment, I don't have a lot of bandwidth to implement the feature.

from ora.

How'd a ColumnConverter help in this case? It gets a column index only, and would return an int64 in this case; the database/sql which drops the pointer...

from ora.

It seems like one could create a custom ColumnConverter that would return a *int64 instead of a int64.

from ora.

And what about the check of the returned type from that custom ColumnConverter in database/sql/convert.go#L53 ? It does not allow an *int64!

from ora.

Ah, bummer :(

from ora.