Q
Problem solve Get help with specific problems with your technologies, process and projects.

ORA-01840 error generated when running query

I have this query:

insert into gsm_dlylist select * from gsm_dlylist1@sw_lnk where 
gd_payment_date='12/10/2004'

When this query is executed, I get this error: "ORA-01840 input value not long enough for this date format." Both the above tables have identical structures and gsm_dlylist is on a remote machine connected through a dblink sw_lnk. Replacing the '*' with the field name of the table rectifies the error. What is the problem when using '*' since i want to insert all the data?

The problem is most likely a difference between the NLS_DATE_FORMAT variable in both the local and the remote server. You can set this format in your local session with the ALTER SESSION command. In the remote database, find out the NLS_DATE_FORMAT of that server. Then issue the following:

ALTER SESSION SET nls_date_format='MM/DD/YY';

But you'll have to use the format on the remote server.

Finally, I'd use the TO_DATE function in the WHERE clause to explicitly define the conversion from your character string to the DATE format.

WHERE gd_payment_date=TO_DATE('12/10/2004','MM/DD/YYYY')

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close