| Data Migration Problem [message #218947] |
Mon, 12 February 2007 03:32  |
Simmi
Messages: 4
Registered: February 2007
|
Junior Member |
|
|
Hi All,
I am migrating data from one database to other using a COPY command.
The source and target table are exactly the same...
the data in source table for a particular field named RATE is like:
1. 0.008865347563475
2. 7.0067823647364
3. 2.0899478865475678
etc
and the confusion is when i am using the copy script in SQLPLUS it is throwing INVALID NUMBER error but the same is working fine in TOAD using DBLINKS.
I want to run the script in SQLPLUS....please suggest;
the query used is:
COPY from passw/schema@database TO passw1/schema1@database1 INSERT schema.FOREX_MONTH (BASE_CURRENCY, CURRENCY, RATE, CURRENCY_MONTH) using select BASE_CURRENCY, CURRENCY, to_number(to_char(RATE)), CURRENCY_MONTH from QQ.FOREX_MONTH ;
|
|
|
|
|
|
|
|
| Re: Data Migration Problem [message #219056 is a reply to message #219055] |
Mon, 12 February 2007 13:36  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Is the precision of both number columns specified?
Reason I ask is this entry in the reference:
"To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified."
|
|
|
|
|
|
|
|
| Re: Data Migration Problem [message #219610 is a reply to message #219525] |
Thu, 15 February 2007 02:50 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, that's todays learning experience. Oracle does indeed support the ANSI FLOAT data type.
Do you have to use the COPY command - can you not just do a 'INSERT INTO table@dblink SELECT * FROM localtable'
|
|
|
|
| Re: Data Migration Problem [message #219843 is a reply to message #219610] |
Fri, 16 February 2007 05:54 |
Simmi
Messages: 4
Registered: February 2007
|
Junior Member |
|
|
ya...
DBLINK was the last option...
we have implemented the same 2 days bac...
but this means no solution to INVALID NUMBER ERROR in migrating float values....
Newazs Thanks All...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
