Five Stars

Insert Date values in Oracle Database

Team, I need to insert values for Date datatype column in Oracle. I’m using tOraclerow component to insert data into Oracle table. The target field in table is in DATE format. But when I try to insert I’m getting error like below, “ORA-01858 – a non-numeric character was found where a numeric was expected.” In the input flow of tOraclerow I tried to get the input in following formats, “YYYYMMDD”, “DD-MM-YYYY”, “DD-MMM-YY”, but getting the same error. Please let me know what is the format to insert data into Oracle date column. PS : The values in the table is like 06-Jul-17, 07-Jul-17.
3 REPLIES
Ten Stars

Re: Insert Date values in Oracle Database

You're feeding a string into a date, and that's what the error message describes. Dates are numeric values and require conversion from string values.

If the corresponding column in your Talend schema is a date, just pass that to Oracle.
Five Stars

Re: Insert Date values in Oracle Database

Can you provide a sample insert query for Date value ? EX : insert into emp (emp_id,date_of_join) values(??????????)
Four Stars

Re: Insert Date values in Oracle Database

Sample Oracle Insert as required

create table emp(emp_id number, date_of_join date);
insert into emp values (1,to_date('10-Jul-17','DD-MON-YY'));