#3 BUG: converting column overflows integer datatype

[Anonymous]

ORA-01455: converting column overflows integer datatype - Heppends when data in NUMBER(15) column
Oracle 11g

[Vincent Rogier]

Hi,

Are you trying to fetch a big value from NUMBER(15) column with OCI_GetInt() ?
If so, the error code would be normal if the value could not fit into an integer.
Use OCI_GetBigInt() instead.

could you give more details ?

[Nobody/Anonymous]

So as you can see below I tried to get 368574164300 number by all of OCI_GET*.
And it's successful only for Double & of course for String.
Is there something bigger than OCI_GetUnsignedBigInt for not float numbers?

COLLATERAL FETCH

Field : #1 - Name : MAX(AMOUNT)

ERROR - MSG : OCI-22053: overflow error

As BigInt : 2147483647

ERROR - MSG : OCI-22053: overflow error

As Unsigned BigInt : -1
As Double : 3.68574e+11
As String : 368574164300

1 row(s) fetched

2 errors encountered

[Vincent Rogier]

the output you posted shows clearly (value of 2147483647 for big_int) that big_int type is declared as int and not long long (in order to support 64bits integers).

Apparently ocilib.h failed to detect long long support...

Can you post :

• your version of OCILIB
• your OS (type and version)
• your compiler (type and version)

some ideas :

• Is your compiler support long long type (C99) ?
• Are you using OCILIB < 3.0.0 (prior version didn't include limits.h and could fail to detect LLONG_MAX)
• Is LLONG_MAX defined within your standard c library
• did you compile OCILIB with C89 option ?
• ...

Vincent

[Nobody/Anonymous]

OCILIB - 3.0.1
OS - SlackWare 11
Compiler - gcc 3

Yes, you right seems to be big_int is int.
I examine my limits.h and find out that it reference to bits/wordsize.h
and define some __WORDSIZE const that 32 in my env than limits.h compare __WORDSIZE and if
that 64 than LONG_MAX set's correct.

Thank's Vincent, but I still can't get my number(15) value:)
Do you know how to force gcc to use 64 bits words?

[Vincent Rogier]

__WORDSIZE is used to detect 32/64 bits plaforms...

that's weird that LLONG_LMAX is not defined on your platform..

Is LONG_LONG_MAX defined ? if yes, you can modify ocilib.h and replace at line 1053 LLONG_MAX with LONG_LONG_MAX

I'll be interested that you send me your limits.h to have a loook at it.

if LON_LONG_MAX is not defined and you're sure that long long type is supported by your platform, you can force the declaration of big_int as long long

--enable-long-long

[Vincent Rogier]

opps... I' pain that comment cannot edited...

As weel you could try the option "CFLAGS=--enable-long-long" for configuring OCILIB.

the end of my previous comment is not clear : --enable-long-long and forcing big_int as long long are not related !

[Vincent Rogier]

Humm... I'm currently having my first coffee (7:30 am in Paris) of the day...

I've just read my 2 last commnents and they are not really to understand (crazy keyboard !)

Sorry about that !

[Nobody/Anonymous]

Hello Vincent! I find that gcc correct support long long vars & that LLONG_MAX set's correct because of that I realy dont think that problem in my compiler configuration. I rewrite ocilib's ocilib_demo.c to show you how it all works on my env:
ocilib_demo.c:

---

include "ocilib_demo.h"

/* ------------------------------------------------------------------------ *

• prototypes
• ------------------------------------------------------------------------ */

void err_handler(OCI_Error *err);
void print_version(void);

void cleanup(void);
void disconnect(void);

void test_fetch(void);

/* ------------------------------------------------------------------------ *

• variables
• ------------------------------------------------------------------------ */

static OCI_Connection cn = NULL;
static OCI_Statement st = NULL;
static OCI_Resultset *rs = NULL;

static mtext str[SIZE_STR+1];
static dtext temp[SIZE_STR+1];

static int nb_err = 0;

/* ------------------------------------------------------------------------ *

• err_handler
• ------------------------------------------------------------------------ */

void err_handler(OCI_Error *err)
{
print_text("\n");

if (OCI_ErrorGetType(err) == OCI_ERR_ORACLE)
{
    const mtext *sql = OCI_GetSql(OCI_ErrorGetStatement(err));

    if (sql != NULL)
    {
        print_text("> ERROR - SQL : "); print_mstr(sql);
        print_text("\n");
    }
}

print_text("> ERROR - MSG : ");
print_mstr(OCI_ErrorGetString(err));
print_text("\n");

nb_err++;

}

/* ------------------------------------------------------------------------ *

• main
• ------------------------------------------------------------------------ */

int mtmain(int argc, mtarg argv[])
{
/ CHECK COMMAND LINE --------------------------------------------------- */

if (argc < (ARG_COUNT-1))
{
    return EXIT_FAILURE;
}

/* INITIALIZE OCI ------------------------------------------------------- */

if (!OCI_Initialize(err_handler, (argc==ARG_COUNT) ? argv[ARG_HOME]:NULL,
                    OCI_ENV_DEFAULT))
    return EXIT_FAILURE;

/* CONNECTION TO SERVER ------------------------------------------------- */

print_text("Connecting to ");
print_args(argv[ARG_USER]);
print_text("/");
print_args(argv[ARG_PWD]);
print_text("@");
print_args(argv[ARG_DB]);
print_text("\n\n");

cn = OCI_ConnectionCreate(argv[ARG_DB], 
                          argv[ARG_USER], 
                          argv[ARG_PWD],
                          OCI_SESSION_DEFAULT);

if (cn)
{
    st = OCI_StatementCreate(cn);

    print_version();

    test_fetch();

    disconnect();
}
else
{
    print_mstr(OCI_ErrorGetString(OCI_GetLastError()));
}

cleanup();

print_text("\npress any key to exit...");

getchar();

return EXIT_SUCCESS;

}

/* ------------------------------------------------------------------------ *

• cleanup
• ------------------------------------------------------------------------ */

void cleanup(void)
{
OCI_Cleanup();

print_frmt("\n%i errors encountered\n\n", nb_err);

}

/* ------------------------------------------------------------------------ *

• disconnect
• ------------------------------------------------------------------------ */

void disconnect(void)
{
OCI_ConnectionFree(cn);
}

/* ------------------------------------------------------------------------ *

• print_version
• ------------------------------------------------------------------------ */

void print_version(void)
{
/ print server string version /
print_mstr(OCI_GetVersionServer(cn));
print_text("\n\n");

print_text("\n>>>>> VERSIONS INFORMATION \n\n");

print_frmt("OCILIB major    version : %i\n",   OCILIB_MAJOR_VERSION);
print_frmt("OCILIB minor    version : %i\n",   OCILIB_MINOR_VERSION);
print_frmt("OCILIB revision version : %i\n\n", OCILIB_REVISION_VERSION);

/* print all versions */
print_frmt("OCI compile     version : %i\n",   OCI_GetOCICompileVersion());
print_frmt("OCI runtime     version : %i\n\n", OCI_GetOCIRuntimeVersion());

print_frmt("Server major    version : %i\n",   OCI_GetServerMajorVersion(cn));
print_frmt("Server minor    version : %i\n",   OCI_GetServerMinorVersion(cn));
print_frmt("Server revision version : %i\n\n", OCI_GetServerRevisionVersion(cn));

print_frmt("Connection      version : %i\n\n", OCI_GetVersionConnection(cn));

}

/* ------------------------------------------------------------------------ *

• test_fetch
• ------------------------------------------------------------------------ */

void test_fetch(void)
{
int i, n;
long long ll5, ll;
big_int bi5, bi;

ll5=500000000000000LL;
bi5=500000000000000LL;

print_text("\n>>>>> SIMPLE TEST FETCH WITH META DATA\n\n");

/* execute query in one go */
OCI_ExecuteStmt(st, MT("select max(id) from utest"));

rs = OCI_GetResultset(st);
n  = OCI_GetColumnCount(rs);

/* print resultset columns info */
for(i = 1; i <= n; i++)
{
    OCI_Column *col = OCI_GetColumn(rs, i);

    print_frmt("> Field : #%i ", i);
    print_text("- Name  : "); print_mstr(OCI_ColumnGetName(col));
    print_text("\n");
}

print_text("\n");


    print_frmt("Constant long long : %Ld\n", ll5);
    print_frmt("Constant  BigInt   : %Ld\n", bi5);

/* print resultset content */
while (OCI_FetchNext(rs))
{
    ll=OCI_GetBigInt(rs, 1);
    bi=OCI_GetBigInt(rs, 1);

    print_frmt("Result As long long var : %Ld\n", ll);
    print_frmt("Result As BigInt var : %Ld\n", bi);

    print_frmt("Field As BigInt : %Ld\n", OCI_GetBigInt(rs, 1));

    print_text("Field As String : "); print_dstr(OCI_GetString(rs, 1));
    print_text("\n");
}

print_frmt("\n%d row(s) fetched\n", OCI_GetRowCount(rs));

}

---

in the table "utest" one row that has 500000000000000 value of id (number (15))

Here is output of ocilib_demo execution:

Oracle Database 11g Release 11.1.0.6.0 - Production

VERSIONS INFORMATION

OCILIB major version : 3
OCILIB minor version : 0
OCILIB revision version : 1

OCI compile version : 11
OCI runtime version : 11

Server major version : 11
Server minor version : 1
Server revision version : 0

Connection version : 11

SIMPLE TEST FETCH WITH META DATA

Field : #1 - Name : MAX(ID)

Constant long long : 500000000000000
Constant BigInt : 500000000000000

ERROR - MSG : OCI-22053: overflow error

ERROR - MSG : OCI-22053: overflow error

Result As long long var : 2147483647
Result As BigInt var : 2147483647

ERROR - MSG : OCI-22053: overflow error

Field As BigInt : 2147483647
Field As String : 500000000000000

1 row(s) fetched

3 errors encountered

Do you have any ideas?

[Vincent Rogier]

Hi,

The same code runs fine on my configs (vista and opensuse...)

Can you output sizeof(big_int) ?

Vincent

[Nobody/Anonymous]

damn! I'am wrong about my "correct" cfg-

SizeOf big_int...= 4 bytes
SizeOf long long.= 8 bytes

[Vincent Rogier]

You could check in which header file in defined LLONG_MAX in your config ?

ocilib.h includes limits.h and apperently can't fint it in this header (where it's supposed to be defined).

Have you tried to rebuib ocilib ?

[Nobody/Anonymous]

Thanks Vincent! I just commented "#ifdef LLONG_MAX ..." and all become work fine.

[Vincent Rogier]

Ok, but it does not solve the problem properly....

Can you answer my previous questions... ?

[Nobody/Anonymous]

Answering your last question
Yes, i tried rebuild ocilib with all posible long_long parameters, but it's not helped.

LLONG_MAX is defined in limits.h:

root@lisys:~# cat ///limits.h* | grep LLONG_MAX

define LLONG_MAX 9223372036854775807LL

define LLONG_MIN (-LLONG_MAX - 1LL)

define ULLONG_MAX 18446744073709551615ULL

LLONG_MAX, and ULLONG_MAX. Instead only the values gcc defined for

define LLONG_MIN (-LLONG_MAX-1)

ifndef LLONG_MAX

define LLONG_MAX LONG_LONG_MAX

ifndef ULLONG_MAX

define ULLONG_MAX (LLONG_MAX * 2ULL + 1)

---

Here is my limits.h

/* Copyright (C) 1991, 1992, 1996, 1997, 1998, 1999, 2000, 2005
Free Software Foundation, Inc.
This file is part of the GNU C Library.

The GNU C Library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

The GNU C Library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with the GNU C Library; if not, write to the Free
Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
02111-1307 USA. */

/*

• ISO C99 Standard: 7.10/5.2.4.2.1 Sizes of integer types <limits.h>
  */</limits.h>

ifndef LIBC_LIMITS_H

define LIBC_LIMITS_H 1

include <features.h></features.h>

/ Maximum length of any multibyte character in any locale.
We define this value here since the gcc header does not define
the correct value. /

define MB_LEN_MAX 16

/ If we are not using GNU CC we have to define all the symbols ourself.
Otherwise use gcc's definitions (see below). /

if !defined GNUC || GNUC < 2

/ We only protect from multiple inclusion here, because all the other
#include's protect themselves, and in GCC 2 we may #include_next through
multiple copies of this file before we get to GCC's. /

ifndef _LIMITS_H

define _LIMITS_H 1

include <bits wordsize.h=""></bits>

/ We don't have #include_next.
Define ANSI <limits.h> for standard 32-bit words. </limits.h>/

/ These assume 8-bit char's, 16-bitshort int's,
and 32-bit int's andlong int's. /

/ Number of bits in a `char'. /

define CHAR_BIT 8

/ Minimum and maximum values a `signed char' can hold. /

define SCHAR_MIN (-128)

define SCHAR_MAX 127

/ Maximum value an `unsigned char' can hold. (Minimum is 0.) /

define UCHAR_MAX 255

/ Minimum and maximum values a `char' can hold. /

ifdef CHAR_UNSIGNED

define CHAR_MIN 0

define CHAR_MAX UCHAR_MAX

else

define CHAR_MIN SCHAR_MIN

define CHAR_MAX SCHAR_MAX

endif

/ Minimum and maximum values a `signed short int' can hold. /

define SHRT_MIN (-32768)

define SHRT_MAX 32767

/ Maximum value an `unsigned short int' can hold. (Minimum is 0.) /

define USHRT_MAX 65535

/ Minimum and maximum values a `signed int' can hold. /

define INT_MIN (-INT_MAX - 1)

define INT_MAX 2147483647

/ Maximum value an `unsigned int' can hold. (Minimum is 0.) /

define UINT_MAX 4294967295U

/ Minimum and maximum values a `signed long int' can hold. /

if __WORDSIZE == 64

define LONG_MAX 9223372036854775807L

else

define LONG_MAX 2147483647L

endif

define LONG_MIN (-LONG_MAX - 1L)

/ Maximum value an `unsigned long int' can hold. (Minimum is 0.) /

if __WORDSIZE == 64

define ULONG_MAX 18446744073709551615UL

else

define ULONG_MAX 4294967295UL

endif

ifdef __USE_ISOC99

/ Minimum and maximum values a `signed long long int' can hold. /

define LLONG_MAX 9223372036854775807LL

define LLONG_MIN (-LLONG_MAX - 1LL)

/ Maximum value an `unsigned long long int' can hold. (Minimum is 0.) /

define ULLONG_MAX 18446744073709551615ULL

endif / ISO C99 /

endif / limits.h /

endif / GCC 2. /

endif / !LIBC_LIMITS_H /

/* Get the compiler's limits.h, which defines almost all the ISO constants.

We put this #include_next outside the double inclusion check because
it should be possible to include this file more than once and still get
the definitions from gcc's header.  */

if defined GNUC && !defined GCC_LIMITS_H

/ `GCC_LIMITS_H' is what GCC's file defines. /

include_next <limits.h></limits.h>

endif

/ The <limits.h> files in some gcc versions don't define LLONG_MIN,
LLONG_MAX, and ULLONG_MAX. Instead only the values gcc defined for
ages are available. </limits.h>/

if defined USE_ISOC99 && defined __GNUC

ifndef LLONG_MIN

define LLONG_MIN (-LLONG_MAX-1)

endif

ifndef LLONG_MAX

define LLONG_MAX LONG_LONG_MAX

endif

ifndef ULLONG_MAX

define ULLONG_MAX (LLONG_MAX * 2ULL + 1)

endif

endif

ifdef __USE_POSIX

/ POSIX adds things to <limits.h>. </limits.h>/

include <bits posix1_lim.h=""></bits>

endif

ifdef __USE_POSIX2

include <bits posix2_lim.h=""></bits>

endif

ifdef __USE_XOPEN

include <bits xopen_lim.h=""></bits>

endif

---

[Vincent Rogier]

Hi,

I add a look at youer limitS.h file and LLONG_MAX is not necessarily defined.

To defined, basically, the following condition must be true :

( !defined GNUC || GNUC < 2) && (defined USE_ISOC99 && defined __GNUC)

Have your tried to configure OCILIB with addin gcc flag -std=c99 ?

if you make the test with LONG_LONG_MAX is that working (wihtout c99 flag) ?

Vincent

[Vincent Rogier]

No anwser from the poster.
The problem was probably linked to long long support/detection on its platform in order to handle 15 digits numbers (64bits)