KB295381: "ORA-01830: date format picture ends before converting entire input string" error occurs when running DML script during MicroStrategy Enterprise Manger upgrade from 9.x to 10.1 against an Oracle database

• Strategy

In MicroStrategy 10.0 - 10.1, the following error occurs when running the SQL upgrade script ‘statistics_upgrade_non_partitioned_9xto10_DML_Oracle.sql’ against a non-partitioned Oracle database. A solution/code fix for this issue is available in the MicroStrategy 10.2 release.

SUMMARY
This issue has been classified as a defect by Strategy. The impact is limited to Strategy 10.0 - 10.1 Enterprise Manger running against a non-partitioned Oracle database. A workaround is available in the current document. A solution/code fix for this issue is available in the Strategy 10.2 release.

SYMPTOM
In Strategy 10.0 - 10.1, the following error occurs when running the SQL upgrade script ‘statistics_upgrade_non_partitioned_9xto10_DML_Oracle.sql’ against a non-partitioned Oracle database.


ORA-01830: date format picture ends before converting entire input string

CAUSE
This is a known issue with Strategy 10.0 - 10.1 DML scipts for upgrading non-partitioned Oracle Enterprise Manager warehouse.

ACTION
This issue has been fixed in Strategy Secure Enterprise 10.2. Upgrade to this version to take advantage of the fix.

The Strategy Internal Reference Number for the issue discussed in this technical note is DE25955.
WORKAROUND
In the script ‘statistics_upgrade_non_partitioned_9xto10_DML_Oracle.sql’, locate the line highlighted below:


SELECT
CASE WHEN A.DAY_ID IS NULL THEN TO_DATE(CONNECTTIME) ELSE TO_DATE (TO_CHAR(A.DAY_ID, 'MMDDYY') || ' ' || '000000', ' MMDDYY HH24:MI:SS') END,
CASE WHEN A.HOUR_ID IS NULL THEN TO_NUMBER(TO_CHAR(CONNECTTIME, 'HH24')) ELSE MOD (A.HOUR_ID, 100) END,
CASE WHEN A.MINUTE_ID IS NULL THEN TO_NUMBER(TO_CHAR(CONNECTTIME, 'HH24')) * 100 + TO_NUMBER(TO_CHAR(CONNECTTIME, 'MI')) ELSE MOD (A.MINUTE_ID, 10000) END,
A.SESSIONID,
A.SERVERID,

Change the above to the script given below:


SELECT
CASE WHEN A.DAY_ID IS NULL THEN TO_DATE(TO_CHAR(A.CONNECTTIME, 'MMDDYYYY') || '000000', ' MMDDYYYY HH24:MI:SS') ELSE TO_DATE (TO_CHAR(A.DAY_ID, 'MMDDYYYY') || '000000', ' MMDDYYYY HH24:MI:SS') END,
CASE WHEN A.HOUR_ID IS NULL THEN TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'HH24')) ELSE MOD (A.HOUR_ID, 100) END,
CASE WHEN A.MINUTE_ID IS NULL THEN TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'HH24')) * 100 + TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'MI')) ELSE MOD (A.MINUTE_ID, 10000) 
END,
A.SESSIONID,
A.SERVERID,

Comment

0 comments

Details

Knowledge Article

Published:

May 17, 2017

Last Updated:

May 17, 2017

SELECT CASE WHEN A.DAY_ID IS NULL THEN TO_DATE(CONNECTTIME) ELSE TO_DATE (TO_CHAR(A.DAY_ID, 'MMDDYY') || ' ' || '000000', ' MMDDYY HH24:MI:SS') END, CASE WHEN A.HOUR_ID IS NULL THEN TO_NUMBER(TO_CHAR(CONNECTTIME, 'HH24')) ELSE MOD (A.HOUR_ID, 100) END, CASE WHEN A.MINUTE_ID IS NULL THEN TO_NUMBER(TO_CHAR(CONNECTTIME, 'HH24')) * 100 + TO_NUMBER(TO_CHAR(CONNECTTIME, 'MI')) ELSE MOD (A.MINUTE_ID, 10000) END, A.SESSIONID, A.SERVERID,

SELECT CASE WHEN A.DAY_ID IS NULL THEN TO_DATE(TO_CHAR(A.CONNECTTIME, 'MMDDYYYY') || '000000', ' MMDDYYYY HH24:MI:SS') ELSE TO_DATE (TO_CHAR(A.DAY_ID, 'MMDDYYYY') || '000000', ' MMDDYYYY HH24:MI:SS') END, CASE WHEN A.HOUR_ID IS NULL THEN TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'HH24')) ELSE MOD (A.HOUR_ID, 100) END, CASE WHEN A.MINUTE_ID IS NULL THEN TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'HH24')) * 100 + TO_NUMBER(TO_CHAR(A.CONNECTTIME, 'MI')) ELSE MOD (A.MINUTE_ID, 10000) END, A.SESSIONID, A.SERVERID,