Error ORA-01438 when update

[Chartier Jerome]

Hi all,

We have an C process which tries to update an table but at the commit statement ORACLE has occured the following error message:

00101 Check_OCI_Error() : Oracle Error ORA-01438: value larger than specified precision allows for this column

Here are the details traces written in the log file.

686960 AddRecordToElements(): STRC_STREAM_NUMBER:-1074790400
686967 Check_OCI_Error() : Oracle Error ORA-01438: value larger than specified precision allows for this column

686968 Force_Statement(): Failure during KCHECK (line 1612)
686969 librp0002() : start

Error Executing s23cpp0201.exe


What bothers us the most is this line:
686960 AddRecordToElements(): STRC_STREAM_NUMBER:-1074790400

It seems that the STRC_STREAM_NUMBER field is creating the problem: it should actually contain a value of 0 or -1 but not such a big number. In the C program the variable which contains the STRC_STREAM_NUMBER value is defined as follows:

long lStreamNumber = 0L;

This field value (type NUMBER) comes from the s23_calls_for_rating view which is related to the physical table s23_stream_companion (in this table, the field has got NUMBER(3) type) as shown on the below:

SQL> desc s23_calls_for_rating
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_ROWID VARCHAR2(18)
STRC_STREAM_NUMBER NUMBER

SQL> desc s23_stream_companion
Name Null? Type
----------------------------------------- -------- ----------------------------
CALL_CC_RUN_NO NOT NULL NUMBER(9)
CUST_BILL_KEY NOT NULL CHAR(6)
PT_PARTITION NOT NULL NUMBER(3)
STRM_STREAM_KEY NOT NULL CHAR(20)
STRC_STREAM_NUMBER NUMBER(3)

SQL> select TEXT from all_views where VIEW_NAME='S23_CALLS_FOR_RATING';
SELECT --+ ORDERED USE_HASH(s23uc) USE_NL(s23cbkp)
rowidtochar(s23uc.rowid) table_rowid,
nvl(s23sc.strc_stream_number,-1) strc_stream_number,
etc....
FROM s23_stream_companion s23sc,
s23_unbilled_calls s23uc,
s23_cust_bill_key_periods s23cbkp
WHERE s23uc.call_cc_run_no = s23sc.call_cc_run_no(+)
AND s23uc.cust_bill_key = s23sc.cust_bill_key(+)
AND s23uc.pt_partition = s23sc.pt_partition(+)
AND s23uc.s23_subscriber = s23sc.strm_stream_key(+)
AND s23uc.cust_bill_key = s23cbkp.cust_bill_key(+)


Somehow in the view, the field value is converted but not properly... We have investigated a bit and it seems that 1074790400 is the default number of bytes (initial size) for a field of type integer (or number). So we wonder why such a value is put instead of the expected '0' or '-1'.

So far this is all we have discovered...

If you have any idea, thanks in advance for your help

Regards

J??r??me

[Massimo Bianchi]

Hi,
i know it may sound silly, but is the variable
STRC_STREAM_NUMBER

inizialized before using ?

It looks like garbage in the cimpiling, like a standard value put from the compiler in it.

HTH,
Massimo

[Massimo Bianchi]

Hi,
another thought: can you put the DDL of the view ?

Maybe when the view was created there were some option to change it.

HTH,
Massimo

[Chartier Jerome]

Hi,

i know it may sound silly, but is the variable
STRC_STREAM_NUMBER

inizialized before using ?

It looks like garbage in the cimpiling, like a standard value put from the compiler in it.

STRC_STREAM_NUMBER is not a variable, actually we use an OCI function to execute a SQL request to collect STRC_STREAM_NUMBER from the view S23_CALLS_FOR_RATING and we store the result of the request into the variable lStreamNumber.

ET

Hi,
another thought: can you put the DDL of the view ?

Maybe when the view was created there were some option to change it.

CREATE OR REPLACE VIEW s23_calls_for_rating AS
SELECT --+ ORDERED USE_HASH(s23uc) USE_NL(s23cbkp)
rowidtochar(s23uc.rowid) table_rowid,
nvl(s23sc.strc_stream_number,-1) strc_stream_number,
etc....
FROM s23_stream_companion s23sc,
s23_unbilled_calls s23uc,
s23_cust_bill_key_periods s23cbkp
WHERE s23uc.call_cc_run_no = s23sc.call_cc_run_no(+)
AND s23uc.cust_bill_key = s23sc.cust_bill_key(+)
AND s23uc.pt_partition = s23sc.pt_partition(+)
AND s23uc.s23_subscriber = s23sc.strm_stream_key(+)
AND s23uc.cust_bill_key = s23cbkp.cust_bill_key(+)

EXIT

Thanks

J??r??me

[Yogeeraj_1]

hi,

can you trace the program using SQL_TRACE and post the tkprof output?

one simple way will be to just use a LOGON database trigger to issue an alter session to enable sql_trace.

regards
Yogeeraj

[Massimo Bianchi]

Hi,
doing a serach in metalink i found some info relating to similar errors.

in one case was classified as a program error, because there was a memory leak in the software.


In the other they mentioned a pathset.
For the 8.0.5 release, for example, you read:

OCI
687764+ Will get an incorrect result when trying to insert or retrieve a
number greater than 100000000 into(from) the database. This
problem is intermittent.


Could you please post db and os version, just to have a further check ?

Another thought:

You say:
" We have investigated a bit and it seems that 1074790400 is the default number of bytes (initial size) for a field of type integer (or number). So we wonder why such a value is put instead of the expected '0' or '-1'. "

But NVL returns the value of the field, if not null, or -1 if null. So why do you say zero ?


Last thing: what is the default value for that column ?

HTH,
Massimo