Tag Archives: LastRunDateTime

Scribe Insight: Error in accessing System variable -ORA-01861 Literal does not match format string

by

Day before yesterday we saw one of the integration processes in our Scribe Console was giving error “Message Processing failed”. At first sight we thought it was the same “Message Processor” error which we were getting earlier this week. We simply restarted the Process.

But yesterday we saw the same error again. On further investigation we found it had to do something with the Oracle source. It was unusual as this package was running perfectly fine for quite some time.

We opened the DTS and tried testing the custom source query; it gave error saying “ORA-01861 Literal does not match format string”. We tried running the query in Oracle without “where” clause and it ran. We thought this has to do something with the conditions used in the where clause. Since we want our query to return rows which have come after the last time our package has ran, we are using two Scribe variables like “:LastRunDateTime” (returns a timestamp for the last date and time the DTS was run) and “:ThisRunDateTime” (returns the current execution date and time as a timestamp).

WHERE DATE BETWEEN :LastRunDateTime AND :ThisRunDateTime

At first, we thought something has changed in the Oracle view which we were using! Hence we verified the data types of the field which we were referring. But it was of type “Date”. So we opened another DTS package with similar logic embedded; it was still working. We thought there is some issue with the DTS package itself. So we copied the custom query in a new package, and to our surprise it worked!

Bingo!! We got our solution….

Last week we had network outage for almost 2-3 days! During that period the integration processes were time and again trying to access the Oracle source and they failed. This kept going on until we paused the packages. It was at this time that scribe had lost track of the two system variables for this particular package. Hence the variables were not returning proper value to the query.

After knowing the cause of the error, we simply ran the DTS without the where clause. This initialised the :LastRunDateTime value. We uncommented the where clause and tested the query and as expected, this time it worked.