Unusual ORA-01438 Errors

From: MellyGirl <melly-girl_at_comcast.net>
Date: Thu, 18 May 2006 21:27:27 -0500
Message-ID: <KuGdnbrQsdOPsPDZRVn-qw@comcast.com>

I have some Pro C code that has been in production since 1999. Lately, our processes seem to run fine, but will occasionally produce an ORA-01438 : value larger than specified precision allows for this column. When I check the data that is in the database, the values do not seem to warrant this error. Furthermore, the input values are not large at all, so it is pretty perplexing.

I was wondering if there is a way to echo out the SQL statement with all of the values before it is executed. I know that sounds a bit silly, but another person working with me wants to see the values before the "UPDATE" takes place. I am going to print out all of the input values, but this person wants to see the the SQL statement with all of the values. Here is the SQL that is being used:

EXEC SQL UPDATE WHSE_ITEM_QUANTITY
SET SYS_UPDATE_DATE = SYSDATE,

        OPERATOR_ID = :pInput->lOperatorID,
        APPLICATION_ID = :pInput->szAppID,
        DL_SERVICE_CODE = :pInput->szDlSvcCode,
        DL_UPDATE_STAMP = :pInput->lDlUpdtStamp,
        QTY_COMMITED = ((NVL (QTY_COMMITED, 0)) + (:pInput->lQtyCommit)),
        QTY_ON_HAND = ((NVL (QTY_ON_HAND, 0)) + (:pInput->lQtyOnHand)),
        QTY_NOT_ON_SITE = ((NVL (QTY_NOT_ON_SITE, 0)) + 


(:pInput->lQtyNotOnSite)),

        USED_QUANTITY = ((NVL (USED_QUANTITY, 0)) + (:pInput->lUsedQty)),
        TRANS_OUT_QTY = ((NVL (TRANS_OUT_QTY, 0)) + (:pInput->lTrxOutQty)),
        EXPECT_TRANSIN_QTY = ((NVL (EXPECT_TRANSIN_QTY, 0)) + 


(:pInput->lExpectInQty)),

        WIQUAN_CHECKEDOUT_QTY = ((NVL (WIQUAN_CHECKEDOUT_QTY, 0)) + 


(:pInput->lChkedOutQty)),

        QTY_ON_LOAN = ((NVL (QTY_ON_LOAN, 0)) + (:pInput->lQtyOnLoan)),
         PENDING_QUANTITY = ((NVL (PENDING_QUANTITY, 0)) + 


(:pInput->lPendQty)),

        QTY_ALLOCATED = ((NVL (QTY_ALLOCATED, 0)) + 

(:pInput->lQtyAllocated))

WHERE
        WHSE_ID = :pInput->szWhsID
AND
         ITEM_ID = :pInput->szItemID;

This SQL runs pretty fast, but I do know that it is being hit over a million times and I am afraid that there is a conflict with two or more requests hitting the same WHSE_ID and ITEM_ID combination. If that is the case, I wonder why we are not experiencing a row lock.

Any suggestions would be appreciated.

Thanks! Received on Thu May 18 2006 - 21:27:27 CDT