[Possible bug] Oracle stored procedure and null in/out value

[Kazun3500]

Hello. I have the procedure described below.

create or replace
PROCEDURE          test_golang(
  first in NUMERIC:=2,
  second in out  NUMERIC,
  third in out NUMERIC
  )
is
  BEGIN
      if second is null then
          second := first *25635;
          else
          second := second * first *25635;
      end if;
      if third is null then
          third := first *256;
          else
          third := third * first *256;
      end if;
  end;

The essence of the procedure is that the input variables are null values. Value null - the procedure works in one way. Otherwise - different. I call it from go as follows.

package main

import (
    "database/sql"
    "log"
    "os"
    _ "github.com/mattn/go-oci8"
)

const (
    DSN string = "connection_string"
)

func main() {
    os.Setenv("NLS_LANG", "")
    db, err := sql.Open("oci8", DSN)
    if err != nil {
        log.Print(err.Error())
    }
    defer db.Close()

    tx, err := db.Begin()
    if err != nil {
        log.Print(err.Error())
    }
    stmt, err := tx.Prepare("Begin test_golang(first=>:first, second=>:second, third=> :third); end;")
    if err != nil {
        log.Print(err.Error())
        tx.Rollback()
        return
    }
    var second, third sql.NullFloat64
    log.Printf("Out %f, %f", second, third)
    _, err = stmt.Exec(
        sql.Named("first", 3),
        sql.Named("second", sql.Out{Dest: &second, In: true}),
        sql.Named("third", sql.Out{Dest: &third, In: true}),
    )
    if err != nil {
        log.Print(err.Error())
    }
    log.Printf("Out %f, %f", second, third)

    err = tx.Commit()
    if err != nil {
        log.Print(err.Error())
    }

}

I got next output

2018/12/13 12:12:09 Out {0.000000 %!f(bool=false)}, {0.000000 %!f(bool=false)}
2018/12/13 12:12:09 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
2018/12/13 12:12:09 Out {0.000000 %!f(bool=false)}, {0.000000 %!f(bool=false)}

How can i avoid this error?

[Kazun3500]

There is a workaround for solving a problem which, by my opinion is a bug.

- var second, third sql.NullFloat64
+ var second, third string

We pass string variable into function which requires numeric/

[MichaelS11]

Looks like any pointer to nil is not supported for output at this time. Would need to add additional code to get pointer type and use that instead of the nil type. Nil type has no buffer and is always Oracle string type so not going to work correctly for returned value almost always.

https://github.com/mattn/go-oci8/blob/052f5d97b9b62bcb40dfacf7d582940e8d526d95/statement.go#L60-L78

Someone else is welcome to look into this more. If not, I will look into it more later, maybe in January.

[MichaelS11]

@Kazun3500 Please test https://github.com/mattn/go-oci8/pull/320

[Kazun3500]

All works ok. Thank you