Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    54

    Unanswered: Convert Access tables through SQL Loader..

    Dear all

    I'm newbi in oracle. I have created an application in MS-Access. Now I want to convert in ORACLE. I have come to know that SQL loader is used for this purpose. Please anyone can tell me the steps required for SQL loader to convert my Access tables with data into ORACLE tables.

    Thanks.

    Wasim

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Tables must be already created in an Oracle schema. They will be empty; SQL*Loader will only insert data (i.e. can't create tables' structure).

    Access has to prepare records; the most popular way is to create a comma separated values (CSV) file. If it was, for example, departments table, 'dept.csv' file might look like this:
    Code:
            10,ACCOUNTING    ,NY
            20,RESEARCH      ,DALLAS
            30,SALES         ,CHICAGO
            40,OPERATIONS    ,BOSTON
    Now, you'll have to create a control file which will tell SQL*Loader what to do with these values. It has its syntax - follow it and - unless there are special requirements - it will be a simple one. For example, 'dept.ctl' file might look like this:
    Code:
    LOAD DATA 
    INFILE 'dept.csv'
    REPLACE
    INTO TABLE dept
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS
    ( 
    deptno, dname, loc
    )
    Now, all you have to do is to, actually, load records into a table. It is done at the operating system prompt. This is MS Windows example, so - here's a snippet of the execution and verification:
    Code:
    C:\TEMP>sqlldr mike/lion control=dept.ctl log=dept.log
    
    Commit point reached - logical record count 3
    Commit point reached - logical record count 4
    
    C:\TEMP>sqlplus mike/lion
    
    SQL> select * from dept;
    
        DEPTNO DNAME                LOC
    ---------- -------------------- --------------------
            10 ACCOUNTING           NY
            20 RESEARCH             DALLAS
            30 SALES                CHICAGO
            40 OPERATIONS           BOSTON
    
    SQL>
    Oracle 10g SQL*Loader documentation is available here, or - if you use another database version - search for it at http://tahiti.oracle.com.

    Also, if your database version supports it (10g and above), you might try to use "external tables" feature. It will allow you to read CSV files as if they were "ordinary" Oracle tables. Quite handy.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Or....
    1) Create the table in Oracle db.
    2) Link that table to MS Access: File> Get External Data> Link Tables> Files of Type> ODBC database
    3) Select the table and create Query to insert your Access data into the Oracle table.

    PS: You need to define ODBC connection to the Oracle database.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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