-
Data Conversion Problem
Hi all,
I have one table in Access and that needs to be migrated over to Oracle. this table had two columns ( one is text and the other one is MEMO) and the MEMO field had a lot of character( more than 4000 characters one some of the rows). So I created a table in Oracle with one columns is varchar2(4000) and the other columns is LONG datatype.
I insert the data from Access to Oracle using ODBC, the data migrated over to Oracle; HOWEVER, some of the data was cut off in some rows. I don't know what happen.
Can someone tell me what happened here ???
-
You would do better with a CLOB data type if it is supported on your version -- LONG is very difficult to handle.
-
Thanks for your advise,
I did try to use CLOB before but it's not working. I tried to insert into Oracle table from selecting data from Access database and I got the following errors:
ODBC- insert on a linked tables 'TABLE_NAME' failed.
[Microsoft][ODBC Driver Manager] SQL data type out of range (#0)
look like to me the CLOB is not working when we linked table using ODBC
Any other suggestions???
Thanks
-
Is this with Microsoft's Oracle ODBC driver or Oracle's ODBC Driver? You might try the Oracle driver specific to your version...
Jeff Hunter
-
I used Oracle driver.
I think I got this now when I tried to bring data into Oracle 9i and it works.
now I have a stupid question. How can you read the data if it's CLOB. SQL Plus ??? I tried to spool to a txt file but no luck. We have third party tools and it's not compatible with CLOB datatype.
Any suggestions on how to read the data????
thanks
-
u can try by spooling data from Acess database and then execute that script into oracle database .
i just spooled tables data from 8.1.7 and it works . any specific error message ??
Last edited by prakashs43; 10-31-2002 at 12:00 PM.
siva prakash
DBA
-
Prakashs43,
I think I got the data over to Oracle but how I can read the data???
Even I got the data to Oracle already, but just curious, how do you spool data from Access and execute scripts into Oracle???
Could you give me some instructions of how to do that ???
thanks
-
lot of ways
1) write sql insert statement to get required data
eg .. select || 'insert into table tab2 values ' || x1 || ', ' || x2 || from table tab1 ;
-- this will prepare an insert statement and u can execute this .
2) u can use 3rd part tools like toad to do this .
3) if access has exp utility .
coming to second question .
what is the problem is spooling
spool xyz.txt
select x , y from tab2 ;
spool off ;
ofcourse u can do whatever format u want .
siva prakash
DBA
-
I think you misunderstadning my question. my goal here is to bring the data from Access to Oracle which had a lot character in there.
Your suggestion is TOAD, TOAD can only view to certain limit and it will stop.
1) write sql insert statement to get required data
1) write sql insert statement to get required data
eg .. select || 'insert into table tab2 values ' || x1 || ', ' || x2 || from table tab1 ;
You mean in Oracle ??? Tab1 never exists in Oracle and that's the problem b/c If I have it along with the right data, I wouldn't have probem
Last edited by mike2000; 10-31-2002 at 12:28 PM.
-
mike2000
the insert statement is to be executed in msaccess . tab1 is the table in ms access and tab2 is a sample table that exists in oracle .
u can give exact names .
siva prakash
DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|