Exporting from Access 2000 to Oracle 9i

Hi Guys,

I am trying to export 10-71 tables(imported into access from oracle to make some manipulations) from Access to Oracle. I read couple of the postings in this forum and researched Sql*loader. I have a some questions regarding sql loader and about the control file

1. Is sql*loader the best tool that can be used for such exports?

2. During the process of exporting, do we have to export each table in Access into a delimited text file and then use loader to load it into Oracle?If there is an alternative , please explain. Can we do the exporting into oracle directly from access without the intermediate transformations into text files and also without the process of creating a table structure in oracle for the loader to later load the data from the delimited text file ?

3. What does Position in a control file mean.Is it compulsory to use it. I did read couple of references regarding it online, but I am still not clear on the concepts of its usage.

4. I tried the sql*loader, but I am facing some problems.Can someone please take a look and let me know where I need to make changesand what i should to solve my problem

The control file is:

part I:
_____

--This is a sample control file to load the tblcustomers table into oracle
LOAD DATA
INFILE 'd:\Database Tables\tblcustomers.txt'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
--REPLACE
--APPEND
INSERT
INTO TABLE tblcustomers
fields terminated by ',' optioanlly enclosed by '"'
(CustomerID, CustomerName, Address, DicountRate, Term)

--------------------------------------
Tried the above code once, when by running the sqlldr at the command prompt with the oracle username/password and control file name with extension,

I am getting a message that
" commit point reached -- logical record count 4 "

My table tblcustomers in Access has only 4 records in it . So untill here everything is fine,however all my records are sent to the bad file rather than to my oracle table. What should I do to rectify this.
_____________________________________________
I tried using an other set of code for my control file with positions and stuff, which is given below
Part II
----------

--This is a sample control file to load the tblcustomers table into oracle
LOAD DATA
INFILE 'd:\Database Tables\tblcustomers.txt'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'

INSERT
INTO TABLE tblcustomers

(CustomerID POSITION(36) INTEGER,
CustomerName VARCHAR(38:90),
Address VARCHAR(100:200),
DiscountRate POSITION(*) FLOAT EXTERNAL,
Term POSITION(*) INTEGER EXTERNAL)

--------------------------------------------------------
I think that the above code will have to be changed as the position specified might be wrong, however, I am really not able to figure out what I should specify in the control file for say if my CUSTOMERNAME field is of type varchar(50) or (200) what ever and if my column CUSTOMERID is of type number(20) or say float(30)
___________________________________________
The table structure that I created
Name Null? Type
----------------------------------------- -------- ------------
CUSTOMERID NUMBER(20)
CUSTOMERNAME VARCHAR2(50)
ADDRESS VARCHAR2(100
TELEPHONE VARCHAR2(12)
DISCOUNTRATE NUMBER
TERM NUMBER



Some one please help me..

-Thanks for your help in advance

Hello,

SQL Loader is the best very fast , but it is necessary to know.

Try to import data with others tools :

DreamCoder for Oracle
www.mentattech.com

Oracle Migration Workbench
http://www.oracle.com/technology/tech/migration/workbench/index.html

SwisSQL
http://swissql.com/oracle-migration.html

Regards,

1. There is a tool called Data Junction that is also very good. It has scripting capabilities and a runtime engine that can be used to batch process migrations. It also supports many file formats.

2. An alternative would be to use PERL scripts. You can open Access and Oracle simultaneously and perform a read / write function.

3. Position is used to select columns within a line. The first name of a user may be in columns 1-5 and the social security number may be in columns 12-20.

4. You need to examine the log file created. Also, by default, the loader will stop after 50 errors are encountered. Without knowing what the source file looks like and what ther errors are, it is impossible to surmise exactly what went wrong. Common errors include data type mismatch errors, field width errors and improper handling of NULLs.