Ora 01438 ошибка - Исправление ошибок и поиск оптимальных решений проблем

You may also want to
see this article about the ORA-12899 which is returned if a value larger than
column’s width is inserted in the column. Similarly, ORA-01438 is returned if value being inserted is larger than what
is defined for the NUMBER datatype column. Number datatype columns are defined
in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(5,2)”,
it would mean that maximum width of the data could be 5 digits, out of which 2
will be decimal part (for example 123.45). Following are some examples that
explain this concept further.

— In the following example,
the inserted value should only have 2 digits, and both digits should be in
the decimal part of the number

SQL>
create table test (sal number(2,2));

Table
created.

SQL>
insert into test values(2.3333);

insert
into test values(2.3333)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2.3);

insert
into test values(2.3)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2);

insert
into test values(2)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(.2);

1
row created.

SQL>

— In the following example,
the inserted value should only have 2 digits for precision part only as no
decimal could be inserted in this column, although INSERT statement would
succeed if decimal value is mentioned.

SQL>
create table test (sal number(2));

Table
created.

SQL>
insert into test values(2.1);

1
row created.

SQL>
insert into test values(2.11);

1
row created.

SQL>
insert into test values(2.111);

1
row created.

SQL>
insert into test values(2.1110000);

1
row created.

SQL>
insert into test values(22.1110000);

1
row created.

SQL>
insert into test values(223.1110000);

insert
into test values(223.1110000)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
select * from test;

SAL

———-

— In the following example,
maximum 6 digits could be inserted in the column out of which 4 have to be
decimal part. As soon as we try to insert 3 digits in precision part,
ORA-01438 would be returned because 4 places have to be reserved for the decimal
part.

SQL>
create table test (sal number(6,4));

Table
created.

SQL>
insert into test values(25.65743);

1
row created.

SQL>
insert into test values(2534333.65743);

insert
into test values(2534333.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(253433.65743);

insert
into test values(253433.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2534.65743);

insert
into test values(2534.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(2.65743);

1
row created.

SQL>
insert into test values(22.65743);

1
row created.

SQL>
insert into test values(223.65743);

insert
into test values(223.65743)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
insert into test values(243.5);

insert
into test values(243.5)

ERROR
at line 1:

ORA-01438:
value larger than specified precision allowed for this column

SQL>
select * from test;

SAL

———-

25.6574

2.6574

22.6574

Источник

September 17, 2020

Hi,

I got ” ORA-01438: value larger than specified precision allowed for this column” error in Oracle database.

ORA-01438: value larger than specified precision allowed for this column

Details of error are as follows.

SQL> create table test_table (id number(3,3));

Table created.

SQL> insert into test_table values(3.333);
insert into test_table values(3.333)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>

The ORA-01438 error is related with the value larger than column’s width is inserted in the column.

Number datatype columns are used in precision and scale (NUMBER(p,s)). If you define a number column as “NUMBER(4,2)”, you need to use 4 maximum width of the data and 2 for decimal part like (123.45)

ORA-01438

To solve The ORA-01438 error, use the correct value as follows.

SQL> insert into test_table values(0.3);

1 row created.


SQL> insert into test_table values(0.2);

1 row created.

SQL> insert into test_table values(0.1);

1 row created.

SQL>

Second example is as follows.

SQL> CREATE TABLE test
2 ( id number(6) not null,
3 name varchar2(40) not null
4 );

Table created.

SQL> insert into test values(1453634,'Mehmet Salih');
insert into test values(1453634,'Mehmet Salih')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

To solve this error use only 6 digits for the first column as follows.

SQL> insert into test values(145363,'Mehmet Salih');

1 row created.

SQL>

Or another example is as follows.

SQL> create table test (id number(6,3));

Table created.

SQL> insert into test values(12.3456);

1 row created.

SQL> insert into test values(123.4567);

1 row created.

SQL> insert into test values(1234.567);
insert into test values(1234.567)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into test values(123.45678);

1 row created.

SQL> insert into test values(123.456789);

1 row created.

SQL> insert into test values(123.4567890);

1 row created.

SQL> insert into test values(123.4567890123);

1 row created.

SQL>

Do you want to learn Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

14,021 views last month, 2 views today

Источник

Wikitechy | 1883 Views | oracle | 28 May 2016

ORA-01438 Error

Scenario:

Imagine a situation with an insert of 6-character string into a varchar column of size 5.

Error:

In such cases the error occurs as: ORA-01438: value larger than specified precision allowed for this column.

Fix:

Try to provide data input according to the column specifications mentioned for the table.

[Fix]-ORA-01882 timezone region not found

[Fix]-ORA-00911 invalid character-newline

Источник

Error Message

Using cov2sde command may generate the following error: SDE Error(-51) Underlying DBMS error Extended error code(1438): ORA-01438: value larger than specified precision allows for this column.

Cause

Attribute values of the coverage in one or more NUMBER fields are too large for the new fields created in the Oracle table.

Cov2sde first creates an Oracle table to receive the data. This new table’s NUMBER fields are defined from the output/display column and decimals fields specified for the coverage’s INFO table.

To illustrate, assume we have a single parcel coverage with an AREA field whose output/display width is 3, and its decimal column is set to 1 decimal place. When this AREA field is added to the new Oracle table it will be defined as NUMBER(3,1). The 3 indicates the maximum number of digits Oracle will store in this number field. The 1 means that one of those three digits is reserved for a position to the right of the decimal point. Thus, if the area value in the coverage is 12.3, it would be a legitimate number, but an area value of 123.4 would not be¹.
¹Koch, G., and K. Loney, «Creating, Dropping, and Altering Tables and Views,» Oracle: The Complete Reference, Electronic Edition (1997):383.

Solution or Workaround

Changing the output/display column width in the INFO table to a width larger than the attribute value before loading the coverage into the Oracle table eliminates the error, and should allow the data to load.

In the previous example, changing the column width from 3 to 4 or higher will correct the problem.
Find offending INFO column(s) and increase output/display width size using ArcCatalog™

or INFO

.

Note: ArcINFO™ Workstation refers to the column as Output width.
ArcInfo™ Desktop refers to the column as Display width.

Last Published: 5/5/2016

Article ID: 000002107

Источник

Problem

16:59:00 [43446] ERROR SQLExecute: oexec(1:13) failed, ORA-01438: value larger than specified precision allows for this column.
16:59:00 [43446] ERROR ODBC [01000] ORA-01438: value larger than specified precision allows for this column (DBError=1438)
16:59:00 [43446] ERROR MAD_PkgExecStmt: SQLExecute(mpi_memhead) failed.
16:59:00 [43446] ERROR MAD_PkgDbxPutAcb: tabName ‘mpi_memhead’, row 0, op ‘UPDATE’ failed.
16:59:00 [43446] ERROR MPI_MemDbSegPut: MAD_PkgDbxPutAcb(mpi_memhead) failed.
16:59:00 [43446] ERROR MPI_MemDbPut: MPI_MemDbSegPut(101) failed.
16:59:00 [43446] ERROR USR=rwuser, IXN=MEMPUT, ERR=EODBC, MSG=member 00999:023729180013118, unable to insert/update/delete member data.
16:59:00 [43446] ERROR MPI_IxnExec_ODBC: ixn failed due to ODBC error, disconnecting.

ORA-01438: value larger than specified precision allowed for this column

Resolving The Problem

This error was a result of using the same memrecno in testing an excessive number of updates. The excessive amount of updates to the single member caused the memseqno field to be constantly updated to the point it exceeded the number size of the database field, in this case it was Oracle number(5) making the max number 99999 (sql server would have been 32767).

[{«Product»:{«code»:»SSLVY3″,»label»:»Initiate Master Data Service»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Version Independent»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]

Источник

Hello Everybody,

When you work in large enterprises where source systems sometimes forget to inform the datawarehouse about changes on data format / size or you are aggregating a continuously enlarging data, Getting an ORA-01438 becomes routine in once in a month.

Within this post i’ll try to explain a practical method to make your ODI scenarios debug themselves .

ORA-01438: value larger than specified precision allows for this column

Without ODI:

When dealing with sql statements we can use the benefits of DBMS_ERRLOG package.
[doc. http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_errlog.htm#ARPLS680]

if you have a huge Insert statement which throws an ORA-01438 you can simply create a log table via

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(‘<TARGET_TABLE_SCHEMA>.<TARGET_TABLE_NAME>‘,’<LOG_TABLE_NAME>‘);
/*LOG_TABLE_NAME is usually named with a convention like ERR_<TARGET_TABLE_NAME>*/

then all you have to do is call your Insert statement by adding «LOG ERRORS INTO ….»
as shown below to log all the erroneous records.

INSERT INTO <TARGET_TABLE_SCHEMA>.<TARGET_TABLE_NAME> (…………)
SELECT ………..
FROM <SOURCE_TABLE_SCHEMA>.<SOURCE_TABLE_NAME>
LOG ERRORS INTO <TARGET_TABLE_SCHEMA>.<LOG_TABLE_NAME> (‘Label to specify for which run the records are logged‘) REJECT LIMIT <Number of rejected records>;

Then you can examine the log to specify the problem.

But it is important to remember that running all insert statements with LOG ERRORS adds an expense at the moment of inserting. This method should be used only for detecting the problem.

With ODI:
So we can not just embed the phrases into a KM and wait for the error happen because of the performance. But we can integrate this facility into our KM’s and use parameter to invoke or revoke error logging on demand.

But how?
Simplest way is adding a declare type of variable to your packages to manage on demand run type. Then pass this variable to KM through an option. When we edit the KM in the proper way then we are done.

Below are this steps in detail:

1. Create the variable:

Select The KM to edit. I am editing the basic IKM SQL Control Append. Don not forget duplicating the KM and renaming it to make your remember its function.

I renamed it in order to make it understandable that this KM has Oracle Specific template.

Add an option with Value Type and set the default value as the variable:

Add a new detail to the KM for creating the log tables if necessary This detail must be added to the first order:

BEGIN
<@ if («1».equals(«<%=odiRef.getOption(«DEBUG_MODE»)%>») ) { @>
DBMS_ERRLOG.CREATE_ERROR_LOG(‘<%=odiRef.getTable(«L»,»TARG_NAME»,»A»)%>’,’ERR_<%=odiRef.getTargetTable(«RES_NAME»)%>’);
<@ } @>
NULL;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL;
END IF;
NULL;

END;

Modify «insert new rows» detail the way the logging statement will be added to the regular insert statement when requested via #DEBUG_MODE=1

<@ if («1».equals(«<%=odiRef.getOption(«DEBUG_MODE»)%>») ) { @>
LOG ERRORS INTO <%=odiRef.getTargetTable(«SCHEMA»)%>.ERR_<%=odiRef.getTargetTable(«RES_NAME»)%>(‘<%=odiRef.getSession(«SESS_NO»)%>’) REJECT LIMIT 10

<@ } @>

Caution! Once you add the variable and KM to your Packages do not forget to send #DEBUG_MODE=0 to the scenarios on regular runs!.

Источник

ORA-01438: value larger than specified precision allowed for this column

ORA-01438

Do you want to learn Oracle SQL, then read the following articles.

Scenario:

Error:

Fix:

Error Message

Cause

Solution or Workaround

Problem

Resolving The Problem

Читайте также: