I have some web logs that I'm trying to import into Oracle SQL table. The table has timestamp and varchar for when request was made and url. I formatted the logs to looks like this:
"Nov 1 2021 2:12:54.856 CDT","/webfolder1/file.html"
"Dec 11 2021 5:32:13.34 CDT","/webfolder1/file2.html"
I used SQL developer tool to import this file to Oracle table, however it did not like the date in the first column.
VALUES (to_timestamp('Nov 1 2021 2:12:54.856 CDT'), '/webfolder1/file.html')
...
ERROR:
ORA-01858: a non-numeric character was found where a numeric was expected
Do I need to use sed/awk or some other utility to change the date into some other format that Oracle would accept?
CodePudding user response:
Use TO_TIMESTAMP_TZ
and specify a format model and language:
CREATE TABLE table_name (
ts TIMESTAMP WITH TIME ZONE,
filename VARCHAR2(50)
);
Then:
INSERT INTO table_name (ts, filename) VALUES (
to_timestamp_tz(
'Nov 1 2021 2:12:54.856 CDT',
'Mon DD YYYY HH24:MI:SS.FF3 TZD',
'NLS_DATE_LANGUAGE=English'
),
'/webfolder1/file.html'
);
db<>fiddle here