Troubleshooting
Problem
Oracle Error 1438 is given when trying to create a shipper (either manually or by running wh120).
Symptom
"01 |1461439 |2 |WH2349 |120 |0 |3 |Oracle |Error |work_ord_pick_ticket.c |1018 |Oracle Err PK72714542.PR72714542 SqlCode 0 SQLCD -1438 SqlMessage . |2 |20010205201138 |wmsprod |02001" Error message is being given in the $BASE/log/app_log/$(WHSE)wms_alert_log when trying to create shipper using wh120.
Cause
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;
/
Historical Number
PRI48420
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21535397