All Apps and Add-ons

DB Connect 2 error with timestamp format: ORA-01843: not a valid month

cscaldeira
Explorer

Hi, I have a ticket open (Case #: 252924) where we are trying to use DBX v2 – Output to ingest data to a relational database but it's not working. On the logs we see Error during DBX v2 output execution: ORA-01843: not a valid month

Follow below the details:

SPL:
..| stats earliest(_time) as startTime by transactionId, component
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(startTime)
| table startTime transactionId
| sort transactionId startTime | where transactionId!=""

Output Table and map:
Column Type Field Map
FEEDNAME VARCHAR2 transactionId
START_TS TIMESTAMP(6) startTime

Sample:
FEEDNAME START_TS

AAA 2015-06-29 00:10:11
BBB 2015-06-29 00:10:24
CCC 2015-06-29 00:25:07

Tags (1)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Sounds like an Oracle issue where the RDBMS doesn't understand your timestamp. Output a timestamp format appropriate for your DB and you should be good.

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Sounds like an Oracle issue where the RDBMS doesn't understand your timestamp. Output a timestamp format appropriate for your DB and you should be good.

View solution in original post

0 Karma

cscaldeira
Explorer

Spunk Inc response from Case #252924:

In regards to the error code "ora-01843" this error is due to an invalid month format. The date you specified in your sample is an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON. The reason a ORA-01843 occurs includes these reasons
• Using an incorrect NLS_DATE_FORMAT
This happens a lot when you see data coming from US
(where the Month is normally written before the day)
but the format in the application is still the "European" style.
(where the day is written before the month).
· Using a incorrect NLS_DATE_FORMAT:
Such as trying to insert a "written" month name but are using a "numeric" month in the mask

Based on that, I made the changes below which worked for me:

Previous
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(startTime)

Current
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime(startTime)
| fieldformat startTime=strftime(startTime,"%d-%B-%Y %I:%M:%S")

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Great to hear you finally found a timestamp your RDBMS can understand!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Your manual query appears to perform a TO_DATE() conversion, suggesting the date string is not understood by the DB as-is. Perform this conversion before sending the date to the DB and you should be good.

0 Karma

cscaldeira
Explorer

You are not understanding. I'm already performing this conversion on my SPL: convert timeformat="%Y-%m-%d %H:%M:%S" ctime(startTime).
Have you used this DB Connect v2 and worked with this timestamp fields?
Maybe I have to perform a different format, but it's not clear which one.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Yeah, you're converting your timestamp into a string... which then makes Oracle return an ORA-01843 claiming it doesn't understand your string. Use a string format your Oracle can understand, or even epoch time - talk to your DBAs about what your Oracle can understand.

0 Karma

cscaldeira
Explorer

But I'm already outputting a timestamp format:
convert timeformat="%Y-%m-%d %H:%M:%S" ctime(startTime)

I also perform a manual INSERT on the table to make sure it's all correct:
INSERT INTO VALUES ('XXXX', TO_DATE('2015-07-10 11:32:00','yyyy-mm-dd HH24:MI:SS'))

Any other idea?

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!