PeopleSoft Trace Example: Inserting NULL into Non-Nullable Columns

When PeopleSoft comes across a column with null value in an insert or update situation, it ignores it. If the column itself does not allow null values, it can be confusing what’s going on. The following trace snippet, showing an insert of 13 values into the Y_SHC_PLAN table, illustrates the problem:

INSERT INTO PS_Y_SHC_PLAN (STRM,Y_PLAN_NUMBER,ACTIVE_FLAG,Y_PLAN_TYPE,AMT,Y_POLICY_CODE,Y_NUMBER_COVERED,MAR_STATUS,Y_UCA_SUBCODE,Y_MATCH_MARITAL,Y_REQ_DAY_ELIG,Y_REQ_ZERO_HRS,Y_AFC_MONTHS) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
Bind-1 type=2 length=4 value=2105
Bind-2 type=2 length=3 value=001
Bind-3 type=2 length=1 value=A
Bind-4 type=2 length=1 value=D
Bind-5 type=19 length=3 value=212
Bind-6 type=2 length=14 value=SSA BYS LLINDD
Bind-7 type=19 length=1 value=1
Bind-8 type=2 length=1 value=S
Bind-9 type=2 length=5 value=80025
Bind-10 type=2 length=1 value=Y
Bind-11 type=2 length=1 value=Y
Bind-12 type=2 length=1 value=N
Bind-13 type=2 length=1 value=0

The problem is that there are actually 15 columns in the table. App Designer and the database both show the two “missing” columns to be EFFDT_FROM and EFFDT_TO, but neither column shows up in the insert statement. To confuse matters more, the trace file shows that the insert failed because, at first glance, PeopleSoft doesn’t seem to know anything about the two missing but required columns. The error in the trace file is

Return: 1400 - ORA-01400: cannot insert NULL into ("SYSADM"."PS_Y_SHC_PLAN"."EFFDT_FROM")

In reality, though, PeopleSoft knew all about the columns, but it simply chose to ignore them (to leave them out of the insert statement)—it turns out—because their values were null. Specifically, in the section of PeopleCode that tries to update or insert Y_SHC_PLAN records, a SELECT statement had attempted to populate the EFFDT_FROM and EFFDT_TO fields from values in the PS_TERM_TBL, but it had been unsuccessful, because the specified term did not exist in PS_TERM_TBL.

We’ve now fixed the code to throw an error if a user specifies a term which does not exist in the PS_TERM_TBL. And, after a bit of head scratching, we now know why PeopleSoft behaved the way it did by ignoring the null-valued columns.