Re: Pro*C overflow problem converting int 18 in table to Long in C host variable

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/04/26
Message-ID: <33620342.1403428@newshost>#1/1

if you

exec sql begin declare section;

   VARCHAR theNumber[40];
exec sql end declare section;

        exec sql select thatBigNumber into :theNumber from theTable;

It will be no different then if you declared theNumber as a long (except that the varchar can hold the 18 digit number whereas the long cannot).....

If you need to use theNumber in a where clause just:

    exec sql select * into ... from T

              where thatBigNumber = to_number(:theNumber);

There won't be any issues with indexes on the SELECT list columns (apply all the functions you want in a select, it won't affect the plan). It's only when you use them in a where clause that indexes cannot be used. By using the to_number() in the where clause, it will be as if you are comparing to a number so the index will be used.

On Fri, 25 Apr 1997 14:41:16 -0600, djose_at_att.com wrote:

>Hi everyone,
>
>I have a Pro*C code which does a FETCH of a column which is declared
>as number(18) into a Pro*C host variable which is declared as long and
>I get the following error.
>
>SQLCODE = -1455
>SQLERRMC = ORA-01455: converting column overflows
>integer datatype
>
>I could use to_char in my select stmt and declare the
>host variable as char. But the problem is the index on the
>table gets disabled and adversely affects the
>performance.
>
>Any help with the above problem would be appreciated.
>Thanks,
>Jude
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Apr 26 1997 - 00:00:00 CDT