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.