Data Conversion Problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Data Conversion Problem

Hybrid View

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    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 ???

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You would do better with a CLOB data type if it is supported on your version -- LONG is very difficult to handle.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2002
    Posts
    411
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Sep 2002
    Posts
    411
    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

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 11:00 AM.
    siva prakash
    DBA

  7. #7
    Join Date
    Sep 2002
    Posts
    411
    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

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  9. #9
    Join Date
    Sep 2002
    Posts
    411
    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 11:28 AM.

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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
  •  



Click Here to Expand Forum to Full Width