Forum Stats

  • 3,734,720 Users
  • 2,247,031 Discussions
  • 7,857,468 Comments

Discussions

Distinguish whole and real numbers in numeric expressions with OCI_ATTR_PRECISION/OCI_ATTR_SCALE

user11763611
user11763611 Member Posts: 38 Red Ribbon
edited Jan 7, 2020 5:00AM in Oracle Call Interface (OCI)

Hello!

In our OCI application we need to describe the column types of SQL statements.

We use the OCI APIs for that:

OCIParamGet(stmth, OCI_HTYPE_STMT, eh, &p, pos);

OCIAttrGet(p, OCI_DTYPE_PARAM, &data_type, 0, OCI_ATTR_DATA_TYPE, eh );

OCIAttrGet(p, OCI_DTYPE_PARAM, &precision, 0, OCI_ATTR_PRECISION, eh );

OCIAttrGet(p, OCI_DTYPE_PARAM, &scale, 0, OCI_ATTR_SCALE, eh );

When the SQL statement contains numeric expressions such as (col * 2), or aggregates like SUM(col), the data type is SQLT_NUM and the precision and scale is ALWAYS ZERO, no matter if the original column type is a NUMBER(P,0)/INTEGER, or NUMBER(P,S).

As result, we cannot distinguish NUMBER(P,0)/INTEGER results from NUMBER(P,S) to format the result with or without decimal digits.

Is there another OCI descriptor attribute that we could use?

SQLPLUS seems to deal well with this: The result of whole expressions is displayed without a decimal point and zero digit while the result of a real expression shows decimals:

SQL> create table mytable ( ci integer, cn number(10,2) );

SQL> insert into mytable values ( 123, 123.45 );

SQL> select sum(ci), sum(cn) from mytable;

   SUM(CI)    SUM(CN)

---------- ----------

       123     123.45

SQL> select ci * 0.3,  cn * 3 from mytable;

    CI*0.3     CN*3

---------- ----------

      36.9     370.35

How is this done?

We know the workaround is to use a CAST() but that means a lot of code change for what we consider as an OCI limitation...

Thanks!

Seb

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited Dec 23, 2019 2:36AM

    Creating a column as INTEGER is an alias for NUMBER(38). That means a NUMBER with variable "whole" number and precision up to 38 significant digits

    In sqlplus it is a "display" thing... It is not clear to me what it is you are trying to achieve.

  • user11763611
    user11763611 Member Posts: 38 Red Ribbon
    edited Jan 7, 2020 5:00AM

    Hi, it's ok: With OCI describe API we could distinguish INTEGER/NUMBER(38) (where precision=38 and scale=0) from numeric expression results (where precision=0 and scale=0).

    The fact is that an expression involving INTEGER/NUMBER(38) like SUM(integer_col) will also be described with precision=0 / scale=0 so it's not possible to distinguish SUM(integer_col) from SUM(decimal_col) where decimal_col is defined as NUMBER(10,2) for ex.

Sign In or Register to comment.