KB275448: In MicroStrategy 10.x, Enterprise Manager Data Loads fail on Oracle with "ORA-01400: cannot insert NULL into ("MSTR_STATS"."IS_SESSION"."EM_CLIENT_MACHINE")..."

SYMPTOM:
In Strategy 10.x where the Enterprise Manager repository database is Oracle, a data load failure is encountered with the following error present in the MAEntMgr.xml log:
 
 

[Strategy][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("MSTR_STATS"."IS_SESSION"."EM_CLIENT_MACHINE")
ORA-06512: at "MSTR_STATS.SP_IS_SESSION", line 71

 
CAUSE: 
This issue is encountered when a NULL values is entered into the CLIENTMACHINE column of IS_SESSION_STATS table.
 
ACTION:
Connect to the Enterprise Manager database using a tool such as Strategy DB Query Tool and run the following SQL statement:
 

CREATE OR REPLACE PROCEDURE SP_IS_SESSION IS BEGIN 
  DELETE FROM IS_SESSION_TMP1; 
  
  INSERT INTO IS_SESSION_TMP1(
  EM_RECORD_TS,
  EM_LOAD_TS,
  IS_SESSION_ID,
  IS_SERVER_ID,
  EM_APP_SRV_MACHINE,
  EM_WEB_SRV_MACHINE,
  EM_USER_ID,
  EM_CLIENT_MACHINE,
  IS_CONNECT_TS,
  IS_DISCONNECT_TS,
  IS_TMP_DISCON_TS,
  IS_CONNEC_DH_ID,
  IS_DISCON_DH_ID,
  EM_CONNECT_SOURCE,
  DAY_ID,
  HOUR_ID,
  MINUTE_ID,
  IS_REPOSITORY_ID
  )
  SELECT MAX(ST.RECORDTIME),
  MAX(LU.IS_PROC_BEGIN),
  ST.SESSIONID, 
  MAX(CASE WHEN S.IS_SERVER_ID IS NOT NULL THEN S.IS_SERVER_ID ELSE -1 END),
  MAX(CASE WHEN ST.SERVERMACHINE IS NULL THEN 'N/A' ELSE UPPER(ST.SERVERMACHINE) END),
  MAX(CASE WHEN ST.WEBMACHINE IS NULL THEN 'N/A' ELSE UPPER(ST.WEBMACHINE) END),
  MAX(CASE WHEN U.EM_USER_ID IS NOT NULL THEN U.EM_USER_ID ELSE -1 END), 
  MAX(CASE 
   WHEN EVENTSOURCE = 7 THEN UPPER(ST.SERVERMACHINE) 
   WHEN ST.CLIENTMACHINE IS NULL THEN 'N/A' 
   WHEN TRIM(ST.CLIENTMACHINE) IS NULL THEN 'N/A'
   /*If neither of server machine nor client machine is provided*/
   WHEN UPPER(ST.CLIENTMACHINE) = 'SERVER MACHINE:  CLIENT MACHINE: ' THEN 'N/A'
   /*If client machine is provided, use it*/
   WHEN SUBSTR(UPPER(ST.CLIENTMACHINE),1,15) = 'SERVER MACHINE:' AND (INSTR(UPPER(ST.CLIENTMACHINE),'CLIENT MACHINE:',1,1) + 15) <> LENGTH(ST.CLIENTMACHINE)
    THEN SUBSTR(UPPER(ST.CLIENTMACHINE), INSTR(UPPER(ST.CLIENTMACHINE),'CLIENT MACHINE:',1,1) + 16, LENGTH(ST.CLIENTMACHINE) - INSTR(UPPER(ST.CLIENTMACHINE),'CLIENT MACHINE:',1,1))
   /*If client machine is not provided, and server machine is provided, use server machine, just for compatibility*/
   WHEN SUBSTR(UPPER(ST.CLIENTMACHINE),1,15) = 'SERVER MACHINE:' 
    THEN UPPER(SUBSTR(ST.CLIENTMACHINE,17,
     CASE WHEN INSTR(UPPER(ST.CLIENTMACHINE),'CLIENT MACHINE:',1,1)= 0 THEN LENGTH(ST.CLIENTMACHINE) 
     ELSE INSTR(UPPER(ST.CLIENTMACHINE),'CLIENT MACHINE:',1,1) - 18   END)) 
   ELSE UPPER(ST.CLIENTMACHINE) END),
  MAX(ST.CONNECTTIME),
  MAX(ST. DISCONNECTTIME),
  MAX(CASE WHEN ST.DISCONNECTTIME < ST.CONNECTTIME OR ST.DISCONNECTTIME IS NULL THEN LU.IS_WIN_END ELSE ST.DISCONNECTTIME END), 
  MAX(TO_NUMBER(TO_CHAR(ST.CONNECTTIME,'YYYYMMDDHH24'))),
  MAX(CASE 
   WHEN ST.DISCONNECTTIME < ST.CONNECTTIME OR ST.DISCONNECTTIME IS NULL THEN TO_NUMBER(TO_CHAR(LU.IS_WIN_END,'YYYYMMDDHH24'))
   ELSE TO_NUMBER(TO_CHAR(ST.DISCONNECTTIME,'YYYYMMDDHH24')) END),  
  MAX(CASE WHEN ST.EVENTSOURCE IS NULL THEN -1 ELSE ST.EVENTSOURCE END),
  MAX(ST.DAY_ID),
  MAX(ST.HOUR_ID),
  MAX(ST.MINUTE_ID),
  MAX(CASE WHEN MD.REP_ID IS NOT NULL THEN MD.REP_ID ELSE -1 END)
  FROM IS_SESSION_ST_VW ST 
  LEFT JOIN EM_MD MD ON MD.REP_GUID = ST.REPOSITORYID
  LEFT OUTER JOIN EM_USER U ON ST.USERID = U.EM_USER_GUID AND MD.REP_ID = U.IS_REPOSITORY_ID
  LEFT OUTER JOIN IS_SERVER S ON ST.SERVERID = S.IS_SERVER_GUID
  CROSS JOIN EM_IS_LAST_UPD_1 LU
  WHERE ((ST.CONNECTTIME >= LU.IS_WIN_BEGIN AND ST.CONNECTTIME < LU.IS_WIN_END) 
   OR (ST.RECORDTIME >= LU.IS_WIN_BEGIN  AND ST.RECORDTIME < LU.IS_WIN_END))
   AND ST.CONNECTTIME IS NOT NULL
  GROUP BY ST.SESSIONID;

DELETE FROM IS_SESSION WHERE IS_SESSION_ID IN (SELECT IS_SESSION_ID FROM IS_SESSION_TMP1);

INSERT INTO IS_SESSION (EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID,
  EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID,
  IS_SESSION_TM_SEC, EM_CONNECT_SOURCE, DAY_ID, HOUR_ID, MINUTE_ID, IS_REPOSITORY_ID) 
  SELECT EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE,
  EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, 
  (CAST(IS_TMP_DISCON_TS AS DATE ) - CAST(IS_CONNECT_TS AS DATE )) * 86400, 
  EM_CONNECT_SOURCE, DAY_ID, HOUR_ID, MINUTE_ID, IS_REPOSITORY_ID  FROM IS_SESSION_TMP1;

END;

 
If no CLIENTMACHINE information is provided, the Server machine information is recorded.
 
IMPORTANT NOTE: This is specific to Oracle Databases and a back up of the database should be taken prior to executing.