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
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.
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.
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")
Great to hear you finally found a timestamp your RDBMS can understand!
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.
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.
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.
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?