Does oci8 have a property or something could set number of format?

[jliang888]

Does oci8 have property to set number of format: It outputs e format by default: ruby -r oci8 -e "OCI8.new('scott/tiger').exec('select count(1) from all_users') do |r| puts r.join(','); end" 0.14E2 I am expecting: 14

Thanks.

[kubo]

count(1) is fetched as a BigDecimal. You can customize it by setting OCI8::BindType::Mapping. Look at the following draft, which will be put here later.

Number Type Mapping between Oracle and Ruby

Default mapping

Oracle numbers in select statements are fetched as followings by default:

Oracle Data Type	Ruby Class
NUMBER(prec) or NUMBER(prec, 0)	Integer
NUMBER(prec, scale) where prec < 15 and scale != 0	Float
NUMBER(prec, scale) where prec >= 15 and scale != 0	BigDecimal
FLOAT or FLOAT(prec)	Float
NUMBER without precision and scale	BigDecimal
Number type returned by functions or calculated number	BigDecimal
BINARY_FLOAT	Float
BINARY_DOUBLE	Float

When the data type is within Integer or Float class, it is fetched as Integer or Float. Otherwise, BigDecimal.

Note that the mapping is determined by the column definition in select statements, not by the actual value fetched. For example the column in select count(*) from table_name is fetched as BigDecimal because it is returned from count function.

The mapping is customizable by OCI8::BindType::Mapping.

The default values of Oracle number data type mapping are:

# NUMBER or FLOAT data type, used for the first six rows in the above table
OCI8::BindType::Mapping[:number] = OCI8::BindType::Number
# BINARY_FLOAT data type, used for the seventh row in the above table
OCI8::BindType::Mapping[:binary_float] = OCI8::BindType::BinaryDouble
# BINARY_DOUBLE data type, used for the eighth row in the above table
OCI8::BindType::Mapping[:binary_double] = OCI8::BindType::BinaryDouble

OCI8::BindType::Number checks precision and scale to determine ruby class. The first four rows in the above table are hard-coded. The fifth and sixth rows are customizable by OCI8::BindType::Mapping[:number_no_prec_setting] and OCI8::BindType::Mapping[:number_unknown_prec] respectively.

The default values are:

OCI8::BindType::Mapping[:number_no_prec_setting] = OCI8::BindType::BigDecimal
OCI8::BindType::Mapping[:number_unknown_prec] = OCI8::BindType::BigDecimal

Customize mapping

Add the following code to fetch all number or float columns as OraNumber.

OCI8::BindType::Mapping[:number] = OCI8::BindType::OraNumber

Otherwise, add the following code to customize the fifth and sixth rows only in the above table.

OCI8::BindType::Mapping[:number_no_prec_setting] = OCI8::BindType::OraNumber
OCI8::BindType::Mapping[:number_unknown_prec] = OCI8::BindType::OraNumber

Use the following code if you want to fetch numbers as Integer or Float by its actual value, not by column data type definition.

OCI8::BindType::Mapping[:number] = OCI8::BindType::BasicNumberType

[jliang888]

Ah, thank you very much.

[jliang888]

One more question about this, does the setting of the order matter? Say: OCI8::BindType::Mapping[:number] = OCI8::BindType::BasicNumberType OCI8.properties[:tcp_connect_timeout] = 10 OCI8.properties[:connect_timeout] = 15 OCI8.properties[:send_timeout] = 60 OCI8.properties[:recv_timeout] = 60 ..... Must be set before initiate: conn = OCI8.new("scott/tiger") or it does't matter? i.e. I can put those OCI8::Bind.../OCI8.peoperties... after OCI8.new()?

Thanks.

[kubo]

OCI8::BindType::Mapping is referenced when a OCI8::Cursor#exec is called. You can set it any time.

Timeout parameters in OCI8.properties are referenced when OCI8.new() is called. They don't affect established connections. You can set send and receive timeouts by OCI8#send_timeout= and OCI8#recv_timeout= respectively.

[jliang888]

Thank you.