Oracle NUMBER in OTL

Today, a colleague of mine is using OTL (Oracle Template Library) to access our Oracle stg database. We encountered a strange problem when accessing a table column typed NUMBER (a 38-bit numeric type, why 38-bit? -_-|||). We can get the value of this column in the first record in otl output stream, but fail with all the left with zero. Digging into the problem in almost 3 hours, I eventually found it was type compatibility issue.

 

In OTL (for Oracle), output column typed NUMBER will be mapped to OTL’s built in data type otl_var_double. (We can verify that via "otl_column_desc* od = i.describe_select(desc_len);") But actually, double in C++ is only 32-bit widden. So how to deal with the numeric that is larger enough and cause double overflow? I got the answer after ready black-white, tedious document patiently, in the section of discussing function set_column_type:

 

"It is recommended that this function and data type overrides be used with caution. This feature is introduced to address issues like: NUMBER is too large to fit into the otl_var_double container and it is necessary to convert the NUMBER into otl_var_char. Or, for small enough LONG or TEXT columns, sometimes it is more convenient to use the otl_var_char container."

 

So the code should be written as following:

 

otl_stream i;

          i.set_column_type(1, otl_var_char, 20);

          i.open(128, "select f1,f2 from test_tab", db);

 

          char f1[20];

          char f2[31];

 

          /*int desc_len = 2;

          otl_column_desc* od = i.describe_select(desc_len);

          od++;*/

          while(!i.eof()){ // while not end-of-data

                   i>>f1>>f2;

                   cout<<"f1="<<f1<<", f2="<<f2<<endl;

          }

 

column f1 is of type NUMBER in table test_tab. Thus, we can turn f1 to other numeric type via atoi, atodbl, etc.

 

Austin.D

 

PS: I still love ADO.NET (with C++/CLI), which provides strongly typed database access, and we can find the issue during code compling.