ORA-01830 (runs locally, but throws error on server)

Question

I have a python script that inserts records into a database from a dataframe using df.apply() and a sql file. There is a date field that is being inserted, and it is formatted like this:

df['some_date'] = pd.to_datetime(df['some_date'], format='%Y-%m-%dT%H:%M:%S.%f')
if 'datetime' in df.iloc[:, i].dtypes.name:
    log.debug("{col} was transformed to Oracle's date string format".format(col=df.columns[i]))
    # desired Oracle format is DD-MON-RR HH12:MI:SS:
    df.iloc[:, i] = df.iloc[:, i].dt.tz_convert(tz='UTC')
    df.iloc[:, i] = df.iloc[:, i].dt.strftime('%d-%b-%y %I.%M.%S.%f %p')

The sql file being used to perform the insert looks like this:

insert into some_schema.some_table(..., some_date, ..., insert_date)
    values (..., to_timestamp(to_char(:5), 'DD-Mon-YY HH.MI.SS.FF6 PM'), ..., trunc(sysdate, 'DD'))

I have also tried using these and had the same results:

to_timestamp(:5, 'DD-Mon-YY HH.MI.SS.FF6 PM')
to_timestamp(:5, 'DD-Mon-YY HH.MI.SS.FF PM')

For some reason, when I run this process to insert records locally, there is no issue, and records are correctly written. However, when this is run on our development server, the ORA-01830 error occurs.

'10-Aug-20 03.04.18.909028 PM' This is an example of what is being inserted.

These are some of the session informations (local and dev server session settings are the same):

('NLS_NUMERIC_CHARACTERS', '.,'), ('NLS_CALENDAR', 'GREGORIAN'), ('NLS_DATE_FORMAT', 'DD-MON-RR'), ('NLS_DATE_LANGUAGE', 'AMERICAN'), ('NLS_TIME_FORMAT', 'HH.MI.SSXFF AM'), ('NLS_TIMESTAMP_FORMAT', 'DD-MON-RR HH.MI.SSXFF AM'), ('NLS_TIME_TZ_FORMAT', 'HH.MI.SSXFF AM TZR'), ('NLS_TIMESTAMP_TZ_FORMAT', 'DD-MON-RR HH.MI.SSXFF AM TZR')

ORA-01830 means : Date format picture ends before conversion. You should crosscheck your input string ":5" in that case. Maybe CR/LF stuff or encoding issues — mbieren, Aug 12 '20 at 14:00
10-Aug-20 03.04.18.909028 PM This is an example of what is being inserted. — roshpod, Aug 12 '20 at 14:02
is NLS_DATE_FORMAT the same for local and development database? — Turo, Aug 12 '20 at 14:06
Both locally and on the development server, I am inserting into the same database. The difference is what machine is running the python script that inserts the data (local machine vs dev server). — roshpod, Aug 12 '20 at 14:08
check the length of your string *on the server*. The format seems to be correct — mbieren, Aug 14 '20 at 07:28

score 0 · Answer 1 · answered Aug 17 '20 at 07:19

The line

to_timestamp(to_char(:5), 'DD-Mon-YY HH.MI.SS.FF6 PM')

is dangerous, because the TO_CHAR has no format mask. That means the incoming date value in :5, will be converted to a string based on the session/client settings, which typically you might not have control over. Try this instead to ensure that the formats align

to_timestamp(to_char(:5,'DD-Mon-YY HH.MI.SS.FF6 PM'), 'DD-Mon-YY HH.MI.SS.FF6 PM')

But the thing that I find odd here is, if you are doing a TO_CHAR on an incoming timestamp, then why do you need the TO_TIMESTAMP/TO_CHAR stuff at all?

score 0 · Answer 2 · answered Aug 18 '20 at 12:55

0

After getting a DBA to help me understand what was actually being executed on the database, I found out that there was a properties file pointing to a sql file on the server that did not format the date.

answered Aug 18 '20 at 12:55

roshpod

1
1

ORA-01830 (runs locally, but throws error on server)

2 Answers2