handling of NUMBER for uint64 payloads

[sbinet]

hi,

with a uint64-large NUMBER as payload, go-ora will usually decide it should decode it as a float64. godror (based on the C driver+lib) will correctly decode it as a uint64.

could this be fixed ? (perhaps introduce a go-ora/v2.Number type that implements driver.Value so it can be passed to Scan and friends ?)

ex:

decoding value "STATUS" (NUMBER)... 9.439544818968568e+18 | [202 10 44 96 45 82 90 69 57 82 30]
decoding value "STATUS" (NUMBER)... 9.439544818968568e+18 | [202 10 44 96 45 82 90 69 57 81 66]

whereas, with godror:

decoding value "STATUS" (NUMBER)... 9439544818968568129 | [202 10 44 96 45 82 90 69 57 82 30]
decoding value "STATUS" (NUMBER)... 9439544818968568065 | [202 10 44 96 45 82 90 69 57 81 66]

[travelliu]

#245 @sbinet you try this. https://github.com/travelliu/go-ora/blob/mtk/v2/converters/oracle_number.go

I tried to rewrite it. convert number to string

		case NUMBER:
			// Scale = 0 and Precision <18 --> int64
			if par.Scale == 0 && par.Precision <= 18 {
				tempVal, err = converters.NumberToInt64(par.BValue)
				if err != nil {
					return nil, err
				}
			} else if par.Scale > 0 {
				tempVal, err = converters.NumberToString(par.BValue)
				if err != nil {
					return tempVal, err
				}
			} else {
				tempVal = converters.DecodeNumber(par.BValue)
			}

[sbinet]

actually, with your code, this could be simplified to:

                case NUMBER:
-                       tempVal = converters.DecodeNumber(par.BValue)
+                       tempVal = converters.Number(par.BValue)

the underlying database/sql/driver machinery would take care of converting the Number representation to what the user would provide (uint64, int64, float64, []byte, ...)

@travelliu you should consider sending a pull request.

[travelliu]

@sbinet Let the author take a look. There are also performance issues. Converting to int64 has better performance than string, and uint64 will be added later.

[travelliu]

It is not recommended to use uint64. The number of oracle is too large. A data error will occur when a calculation occurs to generate a float. see #245

If the number is determined to be smaller than int64 max values, converting to int64 performs better than string

pkg: github.com/sijms/go-ora/v2/converters
cpu: Intel(R) Core(TM) i7-8559U CPU @ 2.70GHz
Benchmark_Number_To_String
Benchmark_Number_To_String-8   	24530916	        45.04 ns/op
Benchmark_Number_To_Byte
Benchmark_Number_To_Byte-8     	27383768	        43.23 ns/op
Benchmark_Number_Int64
Benchmark_Number_Int64-8       	62228034	        17.89 ns/op
Benchmark_DecodeNumber
Benchmark_DecodeNumber-8       	31382976	        36.28 ns/op
PASS

[sbinet]

in my use case, the db I am reading from is storing a 64b-wide bit pattern as a NUMBER.

alternatively, converters.Number could implement driver.ValueConverter so the extra costly and round about path of going through a string + strconv.ParseXXX would be bypassed.

[travelliu]

Give a sample. In my scenario, the number of the database is read into a text file, and string is the most suitable.

Godror converts according to type, this is because ODPI converts a layer, godror involves CGO and there is a performance bottleneck.

This driver returns the Oracle internal storage data format, which needs to be converted into a normal data format, so the problem of string conversion is designed.

[jedib0t]

@travelliu I have a table with a column containing uint64 values such as 16646959493074124803.

With sijms/go-ora/[email protected], row.Scan ends up giving me a float64 with value 16646959493074125000.

With travelliu/go-ora/v2@mtk, row.Scan ends up giving me a string with the real value.

[travelliu]

@jedib0t

I remember switching to type uint64 based on number length

[jedib0t]

Hey @travelliu, using the debugger I could see that it was going into the par.Scale > 0 block since Scale was 255 and Precision was 38 even though the column is defined as having data_length=22 with data_precision and data_scale being null.

For now, it is all good from my end... I'm using your fork. When you submit this change to the main repo, I'll switch back. Thanks!