ORA-01455: converting column overflows integer datatype

[Former Member]

Hi All,

Recently we have faced an issue in our SAP BW environment (SAP BW 7.3 and Oracle 11.2.3). We were getting runtime errors DBIF_DSQL2_SQL_ERROR with this Oracle Error:

ORA-01455: converting column overflows integer datatype

Source code is like that:

  EXEC SQL.

    select sum(inserts), sum(updates), sum(deletes)

     into :ins_rows, :upd_rows, :del_rows

     from user_tab_modifications

     where table_name  = :i_tablnm

  ENDEXEC.

i_Tablnm is /BIC/FGPAC13A.

After some research I figured out that the table statistics  look not good:

After update the statistics with BrConnect issue was solved.

Any idea about what can be the root cause of this incident and how we can prevent it. I have never seen before that a program fails because a table hasn't statistics. Performance problems yes, but run time errors ....

Kind Regards.

[stefan_koehler · Accepted solution]

Hi Jose,

it looks like the SAP developer (assuming that this native SQL code is written by SAP) has not expected that huge amount of data changes

USER_TAB_MODIFICATIONS tracks the changes since the last statistics run. If you change a lot of data the amount of INSERTs, UPDATEs and DELETEs can become quite large and do not fit into the provided variables.

Your issue was solved after the brconnect run as the tracking values drop to 0 in USER_TAB_MODIFICATIONS and start counting again, but the root cause is still the same.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

The strange think, and something that I have forgotten to mention before, is that the USER_TAB_MODIFICATIONS hadn't got any entry for the mentioned table (/BIC/FGPAC13A). So we tried to reproduce the error through SQL Plus and the result was null:

thanks

Jose

[stefan_koehler · Accepted solution]

Hi Jose,

the screenshots are the current state after running brconnect, right?

A NULL value (depending on the definition of ":ins_rows, :upd_rows, :del_rows") may also cause that issue, but we need to know the definition of the target variables and how the returned values looked like when the dump occurred.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Not actually, the screenshots were taked before the Brconnect run. As you say, it looks like the problem was the NULL value in the table USER_TAB_MODIFICATIONS and the solution the statistics update.

thanks for the support.

[nirmal_konchada]

Hello ,

Please check the below note :

1557321 - Numeric Overflow during standard BW processes

This talks about the issue that you are facing.

Regards,

Nirmal

[Former Member]

Hi Nirmal,

We checked it but we didn't find the solution of our problem there.

Many thanks for your help.

Kind Regards