IntegerPrecision isn´t working with Oracle functions

giordanocwb · Post by **giordanocwb** » Fri 23 Dec 2011 00:47

Hi,

I´m having a trouble with the IntegerPrecision parameter. I set it to 10, if I have a SELECT command in a NUMBER(10) field in a table, it´s ok, works perfectly, but, if I have a SELECT command in a NUMBER(10) field in a function, it´s not working. Instead of brings to the dataset a TIntegerField, the driver brings a TFloatField.

In almost all dataset that we have in our software, they are filled with the fields in design-time, so that, it´s important the driver brings us the correct field to mantain compatibility with SQL Server.

For example:

The select command is: select * from table(sp_teste('44'))

I made a sample Delphi project to ilustrate this problem, please let me know if you need it.

I´m using Delphi 2006, Oracle 11g XE R2 and 5.0.1 devArt driver for Oracle.

I created a function in Oracle called sp_teste, the code of this function is quite simple and it is underneath:

create or replace
TYPE "DT_SWT_SP_TESTE" AS OBJECT(i_number NUMBER(10,0));

create or replace
TYPE "SWT_SP_TESTE" AS TABLE OF DT_SWT_SP_TESTE;

create or replace
FUNCTION "SP_TESTE" (P_TESTE IN VARCHAR)
RETURN SWT_SP_TESTE PIPELINED
as
i_number NUMBER(10,0);
begin
i_number := 10;
PIPE ROW(DT_SWT_SP_TESTE(I_NUMBER));
RETURN;
end;

So, that´s it.

Thanks in advance

bork · Post by **bork** » Mon 26 Dec 2011 11:47

Hello

Oracle server returns the precision (38,0) for the query:

Code: Select all

  SELECT * FROM TABLE (SP_TESTE('test'))

And, unfortunately, we cannot change the Oracle server behavior.

We can propose you the following workaround: you should set the IntegerPrecision parameter to 38.

giordanocwb · Post by **giordanocwb** » Tue 27 Dec 2011 20:08

Hi,

We made the workaround that you suggested.

The thing is, if I have in the same Oracle function, a NUMBER(38,0) field which I want it to be a TIntegerField and a NUMBER(18,2) field which I want it to be a TFloatField.

It doesn´t work. The driver shows TIntegerField for every Oracle NUMBER field, regardless of the size.

This is a real problem for us because we use a lot of database functions that were converted from Interbase stored procedures.

Maybe it could be a problem of the driver which is not recognizing the real field size in that case or not??

I look forward to your answer.

Thank you again.

bork · Post by **bork** » Thu 29 Dec 2011 10:38

Hello

The OCIAttrGet2 function (it is Oracle API function) returns the same Scale and Precision for any NUMBER fields of object types:

Code: Select all

create or replace
TYPE DT_SWT_SP_TESTE3 AS OBJECT
(
  NUM1 NUMBER(10),
  NUM2 NUMBER(18, 2)
);
/
create or replace
TYPE SWT_SP_TESTE3 AS TABLE OF DT_SWT_SP_TESTE3;
/
create or replace
FUNCTION SP_TESTE3 (P_TESTE IN VARCHAR)
RETURN SWT_SP_TESTE3 PIPELINED
as
  NUM1 NUMBER(10);
  NUM2 NUMBER(18, 2);
begin
  NUM1 := 2;
  NUM2 := 19.4;

  PIPE ROW(DT_SWT_SP_TESTE3(NUM1, NUM2));
RETURN;
end;

If you execute SELECT * FROM TABLE (SP_TESTE3('a')) then you will get the same precision for NUM1 and NUM2 field. Unfortunately, we cannot change this Oracle behavior.

We can propose two workarounds:

1. Use TFloatFields in this case for all NUMBER data types.
2. Use the following query: SELECT cast(NUM1 as NUMBER(10)), cast(NUM2 as NUMBER(18,2)) FROM TABLE (SP_TESTE3('a')) - in this case Oracle returns correct Scale and Precision.

giordanocwb · Post by **giordanocwb** » Fri 30 Dec 2011 00:47

Hello,

We decided here to use the second option that you advised us.

So, we made some tests and we have something interesting happening.

If I set IntegerPrecision to 10, and I use the SELECT sentence that you suggested, the field that came to me was a TFloatField for both fields, so, it didn´t work.

But, "this is the interesting part", If I set IntegerPrecision to 11 and
instead of use "SELECT CAST(.. NUMBER(10))", I use, "SELECT CAST(.. NUMBER(11))", the first field now is a TIntegerField. In other words, it worked.

Or, if I change the type of the return field in the Oracle function to NUMBER(11), instead of NUMBER(10) and maintain the IntegerPrecision = 10 and SELECT CAST(.. NUMBER(10)), the field now is a TIntegerField as well.

It seems that, if the IntegerPrecision, the field in the Oracle function and the cast in the select clause are the same field precision, it, kind of, ignore something and the TFloatField comes to Delphi incorrectly.

I´m just curious, did this kind of thing happen to you??

Anyway, we are changing our fields in Oracle functions to NUMBER(11) to make it works.

Thank you very much for your attention.

bork · Post by **bork** » Fri 30 Dec 2011 12:16

Hello

I verified behavior with NUMBER(10) and setting coIntegerPrecision to 10: everything works correctly. If this issue makes any inconvenience, we can help you with resolving it.