Oracle Error 1438 is given when trying to create a shipper (either manually or by running wh120).

Resolving The Problem

When this kind of error occurs, the following can be done to troubleshoot the problem:

1. Log into the Unix server running the Oracle database and run the following command "oerr ora 1438"
The following error message description displays:
01438, 00000, "value larger than specified precision allows for this column"
// *Cause:
// *Action:

This error states that some column in which an insert is happening has a value larger than the allowed precision. But we do not know which column it is.

2. Determine the table and column locate the package that is creating the error by viewing the error in the error log, PK72714542.PR72714542 SqlCode 0 SQLCD 1438.
This message means that the error occurred when a procedure named "PR72714542" was executed from package PK72714542. This means that the error is caused by a SQL statements from this procedure. In this example, the error message is being generated from a insert SQL statement that is inserting values based on some buffer that is being passed by the program. So, if the buffer is acquired correctly, then we will know what fields are causing this error to occur. This package must be modified so the buffer can be written to a file.

3. Add the following statements to the procedure in the package. These statements should be declared before the Begin statements.
/*Declare a variable that will hold the buffer that is written into by the program*/
WRT_DEBUG VARCHAR2(500) := ' ';
/*Dir name is used for specifying which directory to write. Make sure that Oracle has write permissions to write to this directory*/
DIR_NAME CONSTANT VARCHAR2(50):= '/yantra/prod/wmshome/util/support';
/*File name is the file that you want to write to */
FILE_NAM CONSTANT VARCHAR2(20) := 'laks_debug.log';
/*the handle for file operations*/
LOG_HANDLE UTL_FILE.FILE_TYPE;

Buffin is the variable that is used for writing the values into the insert statement.

4. Assign the buffin to wrt_debug and write wrt_debug information to file. This should be inserted wherever you feel appropriate.
/*Open file in archive mode*/
LOG_HANDLE := UTL_FILE.FOPEN(DIR_NAME, FILE_NAM,'a');
/*Assign the Buffin to WRT_DEBUG*/
WRT_DEBUG := BUFFIN;
/*Write WRT_DEBUG to file */
UTL_FILE.PUTF(LOG_HANDLE, '%s\n',WRT_DEBUG);
/*flush the file handle*/
UTL_FILE.FFLUSH(LOG_HANDLE);
/*close the handle to file */
UTL_FILE.FCLOSE(LOG_HANDLE);

A sample modified package is shown below. Make sure that these debug statements are placed in the appropriate places in the procedure. Note that this is an example and should be modified to suit your logic.

show error
/
CREATE OR REPLACE PACKAGE PK72714542
AS
PROCEDURE PR72714542
(
BUFFIN IN OUT LONG
,SQLCD IN OUT NUMBER
) ;
END;
/

DROP PACKAGE BODY PK72714542;
CREATE OR REPLACE PACKAGE BODY PK72714542
AS
PROCEDURE PR72714542
(
BUFFIN IN OUT LONG
,SQLCD IN OUT NUMBER
)
IS
WRT_DEBUG VARCHAR2(500) := ' ';
DIR_NAME CONSTANT VARCHAR2(50):= '/yantra/prod/wmshome/util/support';
FILE_NAM CONSTANT VARCHAR2(20) := 'laks_debug.log';
LOG_HANDLE UTL_FILE.FILE_TYPE;


BEGIN
LOG_HANDLE := UTL_FILE.FOPEN(DIR_NAME, FILE_NAM,'a');
WRT_DEBUG := BUFFIN;
UTL_FILE.PUTF(LOG_HANDLE, '%s\n',WRT_DEBUG);
UTL_FILE.FFLUSH(LOG_HANDLE);
UTL_FILE.FCLOSE(LOG_HANDLE);

INSERT INTO
WORK_ORDER_PICK_TICKET
(
WHSE
,WORK_ORDER_NO
,SEQ_NO
,PROBLEM_NO
,TRAILER_SEQ_NO
,QTY_REQUIRED
,QTY_COMPLETE
,SEQ_REFERENCE
,ORDER_NO
,ORDER_REL_NO
,ORDER_PRIME_LINE
,ORDER_SUB_LINE
,CREATE_TIME_STAMP
,MODIFY_TIME_STAMP
,USERID
,MOD_PROGRAM_ID
)
VALUES
(
SUBSTR(BUFFIN, 1, 5)
,SUBSTR(BUFFIN, 6, 20)
,TO_NUMBER(NVL(SUBSTR(BUFFIN,26,6), '0'))
,SUBSTR(BUFFIN, 32, 10)
,TO_NUMBER(NVL(SUBSTR(BUFFIN,42,4), '0'))
,TO_NUMBER(NVL(SUBSTR(BUFFIN,46,10), '0'))
,TO_NUMBER(NVL(SUBSTR(BUFFIN,56,10), '0'))
,NVL(RTRIM(SUBSTR(BUFFIN, 66, 20)),' ')
,SUBSTR(BUFFIN, 86, 30)
,SUBSTR(BUFFIN, 116, 30)
,TO_NUMBER(NVL(SUBSTR(BUFFIN,146,6), '0'))
,TO_NUMBER(NVL(SUBSTR(BUFFIN,152,6), '0'))
,TO_DATE(NVL(SUBSTR(BUFFIN, 158, 14),'0'),'YYYYMMDDHH24MISS')
,TO_DATE(NVL(SUBSTR(BUFFIN, 172, 14),'0'),'YYYYMMDDHH24MISS')
,SUBSTR(BUFFIN, 186, 8)
,SUBSTR(BUFFIN, 194, 10)
)
;

SQLCD := 0;
EXCEPTION
WHEN OTHERS THEN
SQLCD := SQLCODE;

END;
END PK72714542;
/