My Oracle Support Banner

Resolve TimesTen Error "ORA-01400: TT0871: Column <col> cannot be set to null" With Bulk/array Insert In Embedded SQL (Doc ID 1675363.1)

Last updated on JUNE 05, 2019

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.2.6.0 and later
Information in this document applies to any platform.

Goal

In Pro*C applications, TimesTen could fail to use arrays of structs of indicators correctly. This resulted in
mismatches between the indicator and the array value, that could lead to a TT0871 error.

User test case example;

Use a host-array of size 30 for bulk inserting values in one
shot into TimesTen.

 

They have 2 host array variables, one for data, one to hold null-indicators
(there are many more columns, this is just to illustrate the logic).

struct Block

{

int X, int1, int2, int3, int4, int5;

char string1[FMS_LONG_VARCHAR_LEN], string2[FMS_LONG_VARCHAR_LEN];


} arHost[ENTITY_BATCH];

 

struct TbDef {

short X, int1, int2, int3, int4, int5;

short string1, string2, string3, string4, string5;

} arInd[ENTITY_BATCH];

 

The arInd array is initialized with -1, to indicate no values.

Then they populate the host array arHost with data for as many entries as
there are (max 30 in a batch), then also set the corresponding indicator
values to 0 in arInd array for all entries that have data in a column.

 

Then They call INSERT statement to do the bulk insert operation as follows:

exec sql for :cnt insert into entity

(

X, INT1,INT2,INT3,INT4,INT5,

STRING1,STRING2,STRING3,STRING4,STRING5)

values ( :arHost:arInd );

 

Result: For just one value the insert operation works fine, when using
multiple values I get an error during Insert:

ORA-01400: TT0871: Column X cannot be set to null

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.