Home » SQL & PL/SQL » SQL & PL/SQL » Data Migration Problem
Data Migration Problem [message #218947] Mon, 12 February 2007 03:32 Go to next message
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 #219028 is a reply to message #218947] Mon, 12 February 2007 11:00 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi,

If both the table structure are identical why is that to_char & to_number needed. Can you try it without this.

Thanks
Panneer Selvam
Re: Data Migration Problem [message #219055 is a reply to message #219028] Mon, 12 February 2007 13:27 Go to previous messageGo to next message
Simmi
Messages: 4
Registered: February 2007
Junior Member
i have even tried without using to_number or to_char...
but still the same invalid number error.
Re: Data Migration Problem [message #219056 is a reply to message #219055] Mon, 12 February 2007 13:36 Go to previous messageGo to next message
skooman
Messages: 912
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 #219507 is a reply to message #219056] Wed, 14 February 2007 12:26 Go to previous messageGo to next message
Simmi
Messages: 4
Registered: February 2007
Junior Member
Actuaaly...the datatypes of source field and target field is FLOAT and not number.
Re: Data Migration Problem [message #219525 is a reply to message #219507] Wed, 14 February 2007 14:28 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sorry, I haven't come across that format before, so hopefully someone else can help?
Re: Data Migration Problem [message #219610 is a reply to message #219525] Thu, 15 February 2007 02:50 Go to previous messageGo to next message
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 Go to previous message
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...Smile
Previous Topic: Running Total
Next Topic: How to get missing values
Goto Forum:
  


Current Time: Fri Dec 09 07:47:38 CST 2016

Total time taken to generate the page: 0.13138 seconds