Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2003
    Posts
    57

    Unanswered: transferring data from Access to Oracle DB

    hey folks,

    what's the best way to transfer data from Access to Oracle DB? the DB designs for the databases are not identical, meaning that i can't just copy the data from a table in the Access DB to one in the Oracle DB. first i have to figure out what fields from what tables in the Access DB go in what fields/tables in the Oracle DB and then do the transfer.

    i am thinking Perl script, but that'd take a long time. any suggestions?

    thanks all.

  2. #2
    Join Date
    Oct 2003
    Posts
    12

    Re: transferring data from Access to Oracle DB

    If I were you, I'll go with Oracle SQL*Loader.

    - Rohit.

    Originally posted by my_lou
    hey folks,

    what's the best way to transfer data from Access to Oracle DB? the DB designs for the databases are not identical, meaning that i can't just copy the data from a table in the Access DB to one in the Oracle DB. first i have to figure out what fields from what tables in the Access DB go in what fields/tables in the Oracle DB and then do the transfer.

    i am thinking Perl script, but that'd take a long time. any suggestions?

    thanks all.

  3. #3
    Join Date
    Oct 2003
    Posts
    57
    never heard of it. what is it and where can i get it from?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    ??

    use an ODBC connection

    totally easy, painless, and worry free
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Since this is MS Access you are talking about, it probably is not a whole heck of a lot of data, so The Duck is right. You will have to link the Oracle tables in the Access Database, then create Insert queries to populate the data just so. To warn you, I tried this once. It will take you several tries to get all the queries right. The only alternatives to that are buying some expensive ETL tool. Good luck.

  6. #6
    Join Date
    Oct 2003
    Location
    Montreal
    Posts
    10
    The easiest way would be to dump your Access data in a csv formatted file.
    Transfer the file to your oracle server, and finally use SQL*Loader as krohit said.

    It may take you few hours to get this thing to work, but with the help of Oracle Documentation you should be able to figure out out to load your data!

  7. #7
    Join Date
    Aug 2003
    Posts
    328
    I would use SQL Loader too. Its in your oracle binn file. You need to get the syntax right, but it is a good way to do it. The syntax is pretty to sort out, and its readily available.

  8. #8
    Join Date
    Sep 2003
    Posts
    4
    I will definitely use SL Loader to do this.

    First save the Access tables as text files and then create a .CTL using a text editor. The .CTL file specifies the location of the table saved as a textfile and the tabel in oracle to which you want the data exported. Meaning you must have the table already created in Oracle with the same number of fields as you have it coming from access.

    The syntax for .CTL file should be thus

    Load Data
    Infile "Mytextfile.txt" -- file containing table data -- specify paths correctly
    Append or Truncate (-- I guess you should be using append) into oracle tablename
    Separated by "," (or whatever) optionally enclosed by
    (Field1, field2, field3 etc)

    Hope this helps

  9. #9
    Join Date
    Sep 2003
    Posts
    4
    You use SQL loader from your command prompt thus

    (sqlldr) (username/password) (.CTL filename)

    Note the spaces in between. Brackets are for clarity. Not required

  10. #10
    Join Date
    Oct 2003
    Posts
    57
    well, i could do that if the tables in my Oracle DB corresponded 1:1 to the tables in the Access DB, but alas, they do not. different fields from tables in the Access DB will go to who knows what fields in the Oracle DB, it's a big mess i tell ya.

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    In that case use staging tables which are an exact copy of your access tables. Use linked tables in Access to copy the data over.

    And then use sql or plsql to transform the data from your staging tables to your final tables.

    Alan

  12. #12
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I would think the easiest thing to do would be to create a linked table in Access that points to the Target Oracle table(s). Then write update queries in Access that will update/insert the link table with the necessary transformations in Access.

    ie.

    Access table: invoices
    Access table: inv_day
    Oracle table linked to Access: invoice

    Update query

    Insert into invoice
    Select i.invoice_id, i.customer_id, id.invoice_date, id.ship_date
    From invoices i, inv_day id
    Where id.invoice_id = i.invoice_id;

    Another way would be Microsofts DTS package if you have that.

  13. #13
    Join Date
    Oct 2003
    Posts
    57
    hmm, linking the Oracle tables to the Access ones sounds like a good idea. how exactly do i do that though? i have the Oracle client installed on my machine, but i just installed the developer tools. would i need to reinstall with all the management tools and everything in order to be able to do table linking?

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    no no no no.


    You can load ALL tables from Access into Oracle, then manipulate the data anyway you want.

    TOTALLY simplistic.

    Get the data into Oracle, then writing scripts to load your real Oracle tables is a snap.

    USE ODBC!!

    Takes two seconds to connect to your test-schema, then load all access tables into there. Write simple scripts to load the access tables you just moved into oracle into your real DB-tables and PRESTO! All set.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Oct 2003
    Posts
    57
    The_Duck,
    your solution sounds good, but you are being too vague for my level of Access/Oracle knowledge. assume i am a novice in the DB field and give me step by step instructions please.
    thanks much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •