Home » RDBMS Server » Server Utilities » SQL LOADER (Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product)
| SQL LOADER [message #580867] |
Thu, 28 March 2013 18:44  |
 |
sri3
Messages: 7
Registered: March 2013
Location: Hyderabad
|
Junior Member |
|
|
I AM TRYING TO INSERT DATA FROM SINGLE FLAT FILE INTO TWO DIFFERENT TABLES USING SINGLE CTL (CONTROL) FILE.BUT AM ABLE TO INSERT INTO SINGLE TABLE ONLY.
I HAVE THE FOLLOWING DATA IN CSV FILE
I AM TRYING TO INSERT A SINGLE CSV FILE INTO TABLES
CODE FOR CTL FILE:
options (skip=1)
load data
infile 'C:\Users\Ak\Desktop\Sql loader\emploader.csv'
append into table eloader
fields terminated by","
trailing nullcols
(ename,
empno,
sal,
comm,
phonenum,
mgr,
deptno,
location,
job,
qualification)
into table eldr
fields terminated by","
trailing nullcols
(ename,
empno,
sal,
comm,
phonenum,
mgr,
deptno,
location,
job,
qualification)
I AM ABLE TO INSERT DATA INTO SINGLE TABLE ONLY.
FOLLOWING IS THE LOG FILE
SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 29 03:54:22 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: C:\Users\Ak\Desktop\Sql loader\fortwotab.ctl
Data File: C:\Users\Ak\Desktop\Sql loader\emploader.csv
Bad File: C:\Users\Ak\Desktop\Sql loader\emploader.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table ELOADER, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENAME FIRST * , CHARACTER
EMPNO NEXT * , CHARACTER
SAL NEXT * , CHARACTER
COMM NEXT * , CHARACTER
PHONENUM NEXT * , CHARACTER
MGR NEXT * , CHARACTER
DEPTNO NEXT * , CHARACTER
LOCATION NEXT * , CHARACTER
JOB NEXT * , CHARACTER
QUALIFICATION NEXT * , CHARACTER
Table ELDR, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENAME NEXT * , CHARACTER
EMPNO NEXT * , CHARACTER
SAL NEXT * , CHARACTER
COMM NEXT * , CHARACTER
PHONENUM NEXT * , CHARACTER
MGR NEXT * , CHARACTER
DEPTNO NEXT * , CHARACTER
LOCATION NEXT * , CHARACTER
JOB NEXT * , CHARACTER
QUALIFICATION NEXT * , CHARACTER
value used for ROWS parameter changed from 64 to 49
Table ELOADER:
21 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.
Table ELDR:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
21 Rows not loaded because all fields were null.
Space allocated for bind array: 252840 bytes(49 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 21
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Mar 29 03:54:22 2013
Run ended on Fri Mar 29 03:54:45 2013
Elapsed time was: 00:00:23.54
CPU time was: 00:00:00.11
-
Attachment: Capture.JPG
(Size: 93.04KB, Downloaded 3803 times)
[Updated on: Thu, 28 March 2013 19:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: SQL LOADER [message #580876 is a reply to message #580867] |
Thu, 28 March 2013 22:00  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When loading into more than one table, the position has to be reset for each table after the first one, using POSITION(1) with the first field, as shown below.
options (skip=1)
load data
infile 'C:\Users\Ak\Desktop\Sql loader\emploader.csv'
append into table eloader
fields terminated by","
trailing nullcols
(ename,
empno,
sal,
comm,
phonenum,
mgr,
deptno,
location,
job,
qualification)
into table eldr
fields terminated by","
trailing nullcols
(ename POSITION(1),
empno,
sal,
comm,
phonenum,
mgr,
deptno,
location,
job,
qualification)
|
|
|
|
|
|
| Re: SQL LOADER [message #580898 is a reply to message #580873] |
Fri, 29 March 2013 10:42 |
|
sri3
Messages: 7
Registered: March 2013
Location: Hyderabad
|
Junior Member |
|
|
As am a beginner, the only thing i know about sql loader. It is one of the Oracle Tool will be used to transfer the data from Flat files into Oracle Base tables.Also it is a bulk loader utility.Anyways thanks for your reply blackswan.
[Updated on: Fri, 29 March 2013 10:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: SQL LOADER [message #596964 is a reply to message #596959] |
Fri, 27 September 2013 17:03 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
sri3 wrote on Fri, 27 September 2013 13:17Hi Blackswann, If you don't understand the requirement.Please don't reply to any post with your negative atitude.
with free advice, you sometimes get what you paid for it.
If you are ever dissatisfied with the tone, tenor, completeness, correctness, or timeliness of any response,
please submit your request for a prompt & full refund to /dev/null
|
|
|
|
Goto Forum:
Current Time: Wed Jan 01 04:56:08 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
