how to load data from text file into database

[izza_azhar]

hi all,
i'm newbie in the database actually.the data are inside the .DAT file and separated by ','..
so, how i can do to load the data into the Oracle database using SPL plus.. someone please give any suggestion please...

example data:

39, State-gov, 77516, Bachelors, 13, Never-married, Adm-clerical, Not-in-family, White, Male, 2174, 0, 40, United-States, <=50K

50, Self-emp-not-inc, 83311, Bachelors, 13, Married-civ-spouse, Exec-managerial, Husband, White, Male, 0, 0, 13, United-States, <=50K

thanks

[stecal]

SQL*LDR
http://download-west.oracle.com/docs...rt2.htm#436160


create table dbj_test
(
age number,
jobtype varchar2(30),
id number,
degree varchar2(30),
grade number,
marital varchar2(30),
jobclass varchar2(30),
noidea varchar2(30),
race varchar2(30),
gender varchar2(30),
dunno number,
still_no_idea number,
whatever number,
citizenship varchar2(30),
no_of_braincells varchar2(30)
);


-- Control file for dbj_demo_test
--
-- Start of a new data load
LOAD DATA
--

-- Enter the name of the datafile here
INFILE 'c:\oracle\admin\dbj_demo.dat'
-- INFILE *
--
-- Records rejected due to database constraints
BADFILE 'c:\oracle\admin\demo_bad.txt'
--
-- Records rejected due to not being in the right format
DISCARDFILE 'c:\oracle\admin\demo_discard.txt'
--
-- Start of the data load
REPLACE
INTO TABLE dbj_test
fields terminated by ","
trailing nullcols
(
age,
jobtype,
id,
degree,
grade,
marital,
jobclass,
noidea,
race,
gender,
dunno,
still_no_idea,
whatever,
citizenship,
no_of_braincells
)

C:\oracle\admin>sqlldr scott/tiger control=dbj.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jan 29 09:59:55 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

C:\oracle\admin>

SQL> select count(*) from dbj_test;

COUNT(*)
----------
2

Contents of log file:


SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jan 29 09:59:55 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: dbj.ctl
Data File: c:\oracle\admin\dbj_demo.dat
Bad File: c:\oracle\admin\demo_bad.txt
Discard File: c:\oracle\admin\demo_discard.txt
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DBJ_TEST, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AGE FIRST * , CHARACTER
JOBTYPE NEXT * , CHARACTER
ID NEXT * , CHARACTER
DEGREE NEXT * , CHARACTER
GRADE NEXT * , CHARACTER
MARITAL NEXT * , CHARACTER
JOBCLASS NEXT * , CHARACTER
NOIDEA NEXT * , CHARACTER
RACE NEXT * , CHARACTER
GENDER NEXT * , CHARACTER
DUNNO NEXT * , CHARACTER
STILL_NO_IDEA NEXT * , CHARACTER
WHATEVER NEXT * , CHARACTER
CITIZENSHIP NEXT * , CHARACTER
NO_OF_BRAINCELLS NEXT * , CHARACTER


Table DBJ_TEST:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 247680 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Jan 29 09:59:55 2006
Run ended on Sun Jan 29 09:59:55 2006

Elapsed time was: 00:00:00.48
CPU time was: 00:00:00.10

[izza_azhar]

SQL*LDR
http://download-west.oracle.com/docs...rt2.htm#436160


create table dbj_test
(
age number,
jobtype varchar2(30),
id number,
degree varchar2(30),
grade number,
marital varchar2(30),
jobclass varchar2(30),
noidea varchar2(30),
race varchar2(30),
gender varchar2(30),
dunno number,
still_no_idea number,
whatever number,
citizenship varchar2(30),
no_of_braincells varchar2(30)
);


-- Control file for dbj_demo_test
--
-- Start of a new data load
LOAD DATA
--

-- Enter the name of the datafile here
INFILE 'c:\oracle\admin\dbj_demo.dat'
-- INFILE *
--
-- Records rejected due to database constraints
BADFILE 'c:\oracle\admin\demo_bad.txt'
--
-- Records rejected due to not being in the right format
DISCARDFILE 'c:\oracle\admin\demo_discard.txt'
--
-- Start of the data load
REPLACE
INTO TABLE dbj_test
fields terminated by ","
trailing nullcols
(
age,
jobtype,
id,
degree,
grade,
marital,
jobclass,
noidea,
race,
gender,
dunno,
still_no_idea,
whatever,
citizenship,
no_of_braincells
)

C:\oracle\admin>sqlldr scott/tiger control=dbj.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jan 29 09:59:55 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2

C:\oracle\admin>

SQL> select count(*) from dbj_test;

COUNT(*)
----------
2

Contents of log file:


SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jan 29 09:59:55 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: dbj.ctl
Data File: c:\oracle\admin\dbj_demo.dat
Bad File: c:\oracle\admin\demo_bad.txt
Discard File: c:\oracle\admin\demo_discard.txt
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DBJ_TEST, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AGE FIRST * , CHARACTER
JOBTYPE NEXT * , CHARACTER
ID NEXT * , CHARACTER
DEGREE NEXT * , CHARACTER
GRADE NEXT * , CHARACTER
MARITAL NEXT * , CHARACTER
JOBCLASS NEXT * , CHARACTER
NOIDEA NEXT * , CHARACTER
RACE NEXT * , CHARACTER
GENDER NEXT * , CHARACTER
DUNNO NEXT * , CHARACTER
STILL_NO_IDEA NEXT * , CHARACTER
WHATEVER NEXT * , CHARACTER
CITIZENSHIP NEXT * , CHARACTER
NO_OF_BRAINCELLS NEXT * , CHARACTER


Table DBJ_TEST:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 247680 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Sun Jan 29 09:59:55 2006
Run ended on Sun Jan 29 09:59:55 2006

Elapsed time was: 00:00:00.48
CPU time was: 00:00:00.10

QL*Loader: Release 9.2.0.1.0 - Production on Mon Jan 30 22:43:41 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: adult.ctl
Data File: C:\oracle\admin\test.dat
Bad File: c:\oracle\admin\demo_bad.txt
Discard File: c:\oracle\admin\demo_discard.txt
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DBJ_TEST, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
AGE FIRST * , CHARACTER
JOBTYPE NEXT * , CHARACTER
ID NEXT * , CHARACTER
DEGREE NEXT * , CHARACTER
GRADE NEXT * , CHARACTER
MARITAL NEXT * , CHARACTER
JOBCLASS NEXT * , CHARACTER
STATUS NEXT * , CHARACTER
RACE NEXT * , CHARACTER
GENDER NEXT * , CHARACTER
COL_11 NEXT * , CHARACTER
COL_12 NEXT * , CHARACTER
COL_13 NEXT * , CHARACTER
CITIZENSHIP NEXT * , CHARACTER

SQL*Loader-500: Unable to open file (C:\oracle\admin\test.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Table DBJ_TEST:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 231168 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 0
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Mon Jan 30 22:43:41 2006
Run ended on Mon Jan 30 22:43:47 2006

Elapsed time was: 00:00:06.22
CPU time was: 00:00:00.02

---->why is that do?
please help~~

[rigatoni]

Double check that the test.dat file is in c:\oracle\admin directory.
The errors are indicating that the file wasn't found. I suppose it might be possible that you don't have permission to access the file but I kind of doubt that's the issue