I’m older and somewhat wiser now, fortunately at no great cost this time.
I needed to copy the data from a table in one db to a similarly structured table
in another db. A dblink would have been lovely, but the DBA does not allow them.
L So I exported the data to a pipe-delimited txt file. I believe the delimiter
is the only thing I changed from the default settings. Dates exported as
mm/dd/yyyy hh:mi:ss pm. The import wizard default date delimiter is –
(hyphen). With all my imports from Excel spreadsheets I had never touched it.
Seems it’s a bit more significant in the case of txt files. My 3/5/2010
4:02:01 PM dates loaded as 5/3/2020, no timestamp. March 5 vs. May 3, and no
typo, 2010 vs. 2020.
So in the future I shall be paying closer attention.
Is it too late to request that the defaults for txt exports match the defaults
for txt imports?
Thanks much,
Dan
Daniel B Madvig
Computer Technologies
Northwestern College & Northwestern Media
3003 Snelling Ave.
St. Paul, MN 55113
www.nwc.edu
Dan, would “copy data to another schema” have worked for you? Right click option
in Tables on the schema browser. It uses two open connections and doesn’t need a
DB Link. I find it a lot easier than exporting and importing for many tasks.
Curious question: what is the NLS Date Format in each of the databases, are they
the same or a different format?
The reason I ask is that your data file is text based, not Oracle internal date
value based… which, of course, is why you can read the dates in the file.
When Oracle is doing an import of text data into a date column and a format is
not explicitly identified, the format is implicitly applied from what Oracle
knows… such as NLS Date Format.
Is this perhaps responsible for the issue you ran into?
