Import fails with No such output/input Date format error

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

While doing an import of Jobs or objects the following error can be thrown:

ERROR at line 1:
ORA-20024: No such output format Date format "dd-MON-yyyy"
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 3544
ORA-06512: at "APPWORX_SCHEMA.AWAPI2", line 487
ORA-06512: at "APPWORX_SCHEMA.AWAPI2", line 460
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 112
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 3533
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 3670
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 3533
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 4467
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 4602
ORA-06512: at line 5

Or similarly:

ERROR at line 1:
ORA-20024: No such input format Date format "yyyy-mm-dd"
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 3544
ORA-06512: at "APPWORX_SCHEMA.AWAPI2", line 487
ORA-06512: at "APPWORX_SCHEMA.AWAPI2", line 460
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 112
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 3533
ORA-06512: at "APPWORX_SCHEMA.AWAPI", line 3670
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 3533
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 4467
ORA-06512: at "APPWORX_SCHEMA.AW_IMPORT", line 4602
ORA-06512: at line 5

Cause

In the exporting Applications Manager environment, the SO_DATE_FORMATS Table has additional entries that do not exist in the importing environment.

Below is a chart of the SO_DATE_FORMATS Table showing the default 9 date format values/rows

SO_DATE_FORMAT	SO_FORMAT_NUM	SO_JAVA_DATE_FORMAT
dd-mon-rr		dd-MMM-yy
dd-mon-yyyy		dd-MMM-yyyy
ddmmrr		ddMMyy
mm-dd-rr		MM-dd-yy
mm-dd-yyyy		MM-dd-yyyy
mm/dd/rr		MM/dd/yy
mmddrr		MMddyy
yyyymmdd		yyyyMMdd
yyyymmddhh24miss		yyyyMMddHHmmss

NOTE: By default Applications Manager does not add any additional date formats so additional date formats are likely user added

Resolution

Review the SO_DATE_FORMATS table on both the exporting environment and importing environment. There should be one or more rows in the exporting environment's table that does not exist in the importing environment's table.

Once found, insert the missing row into the importing environment's SO_DATE_FORMATS table using the below sql:
SQL> insert into so_date_formats values ('<so_date_format value>', null, '<so_java_date_format value>');
SQL> commit;

Below is simply an example, but the so_date_format and so_java_date_format values will differ depending on the value found in the exporting environment's SO_DATE_FORMATS table:

SQL> insert into so_date_formats values ('dd-MON-yyyy', null, 'dd-MMM-yyyy');
SQL> commit;

Once insert is complete, perform the import once more.