OCI-22053 Overflow error

[Matz]

Hello,

I'am passing some sql-queries to an Oracle 8i Home Edition Database
using CSharp and the Oracle-Driver from Microsoft which is including
in the .NET Framewok 1.1. On the following query, I always get this
Error-Message:

OCI-22053: overflow error


This is the query:

select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 -
l_discount)) as sum_disc_price, sum(l_extendedprice * (1 -
l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc,
count(*) as count_order from lineitem where l_shipdate <= date
'1998-12-01' - interval '90' day (3) group by l_returnflag,
l_linestatus order by l_returnflag, l_linestatus

Does anyone has an idea?

Thanks!

 

[Miha Markic]

Hi Matz,

Since the error is thrown by Oracle I would suspect that at least one of the
fields is overflowing.
Does the same query run in SqlPlus?

 

[Sönke Matz]

Hi,
the query runs in SQL-Plus without problems! Do you have an idea how to
work around this problem?

Matz

 

[Miha Markic]

Hmmm, first try by elimination isolate the field giving problems - i suppose
it is one (or more) of functions.
Probably at least one of them is generating a big number that managed
provider can't chew.
You should somehow tell Oracle to cast down the number.
Sorry, I don't know the syntax - I've tried TRUNC and ROUND but both don't
work in this sense.

As other workarounds, you might try using Oracle's managed dataprovider.

 

[Sönke Matz]

Is the Oracle's managed dataprovider also available for 8i -
Home-Edition?

I always thought that the ODPNET-Drive is only working with 9i or
higher?

 

[Miha Markic]

Hi,

From Oracle's web site:
Major Features
ODP.NET makes using the Oracle database from .NET more flexible, fast, and
stable than other .NET providers. It is part of the Oracle9i Release 2
Client, but can be used with any Oracle8, *Oracle8i*, or Oracle9i database
server. Highlighted here are some of ODP.NET's key features

Since it is common for Oracle that the newer clients work with older servers
I will not wonder if it really works.
I haven't tried the combination though.

 

[Sönke Matz]

Is the Oracle's managed dataprovider also available for 8i -
Home-Edition?

I always thought that the ODPNET-Drive is only working with 9i or
higher?

 

[Miha Markic]

As in my previous reply - it seems so.

 

[Lyndon Hills]

Hi,

From Oracle's web site:
Major Features
ODP.NET makes using the Oracle database from .NET more flexible, fast, and
stable than other .NET providers. It is part of the Oracle9i Release 2
Client, but can be used with any Oracle8, *Oracle8i*, or Oracle9i database
server. Highlighted here are some of ODP.NET's key features

Since it is common for Oracle that the newer clients work with older servers
I will not wonder if it really works.
I haven't tried the combination though.

I have. it works fine for 8.1.6. Not tried lower or 8i

 

[egarzahan]

hi, i found the solution, i hope help you
http://social.msdn.microsoft.com/Fo...s/thread/4032e2fe-9f37-47d8-9af6-35f1675e9d84

 

[m_shahbazi]

answer

i dont know when you post this, but you must cast the result like this:

cast( avg(SPEED) as Integer) as a

error is because of conversion of integer field to double
i hope it be use full, and not too late