LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sgolding@mda-data.com

Hi,

Has anyone come across this issue before?

Every time I load a date field into an Oracle database (from LAE) I have to either:

A) Convert the date into the default Oracle date format (e.g. DD/MON/YYYY) as a string

OR

B) Convert the field using the TO_DATE() function in Oracle insert statement.

If I don't do this then I get a ": cannot reconcile type differences between database type 'date' and type to be loaded 'string'" error.

Usually I don't really mind however I have over 10 date fields that I need convert and maintenance will be an issue.

I know if you run the SQL command "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';" before the insert then dates will insert without errors however I'm not sure if this is possible with the DB_STORE node

Thank you in advance,

Stephen.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Stephen,

Hello.
Oracle stores "date" type information in a specific internal format. If you want to load "dates" into an oracle database, you have to use the to_date function. Otherwise your data will just be considered a string type (and you will hit type miss-match type errors). Now, oracle does try to do some parsing and some level of conversion if you try to put a string into a date field, but if it doesn't match the internal format close enough it won't fly.

So what you're seeing is actually normal for any oracle interaction.

At the moment I'm not certain of the best way to make handling multiple date fields as you are easier. I'll look into it.

Regards,
Timon Koufopoulos
MDA Support

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: sgolding@mda-data.com

No worries. Thanks Timon