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 Go to next message
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
/forum/fa/10711/0/


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 2832 times)

[Updated on: Thu, 28 March 2013 19:00]

Report message to a moderator

Re: SQL LOADER [message #580870 is a reply to message #580867] Thu, 28 March 2013 19:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
why are you purposefully violating Third Normal Form by storing same columns in two different tables?
Other than by decree, what exactly differentiates rows in table eloader from rows in table eldr?
Re: SQL LOADER [message #580871 is a reply to message #580870] Thu, 28 March 2013 19:45 Go to previous messageGo to next message
sri3
Messages: 7
Registered: March 2013
Location: Hyderabad
Junior Member
I am just learning how to use sql loader for storing same data into two different tables.
Re: SQL LOADER [message #580873 is a reply to message #580871] Thu, 28 March 2013 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sri3 wrote on Thu, 28 March 2013 17:45
I am just learning how to use sql loader for storing same data into two different tables.


What led you to think that sqlldr was designed to load a single file into more than 1 table?

why stop at only two? why not two hundred different tables?
Re: SQL LOADER [message #580876 is a reply to message #580867] Thu, 28 March 2013 22:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #580897 is a reply to message #580876] Fri, 29 March 2013 10:34 Go to previous messageGo to next message
sri3
Messages: 7
Registered: March 2013
Location: Hyderabad
Junior Member
Thanks Barbara Boehmer
Re: SQL LOADER [message #580898 is a reply to message #580873] Fri, 29 March 2013 10:42 Go to previous messageGo to next message
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 #580903 is a reply to message #580898] Fri, 29 March 2013 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also have a look at external table.

Regards
Michel
Re: SQL LOADER [message #596959 is a reply to message #580873] Fri, 27 September 2013 15:17 Go to previous messageGo to next message
sri3
Messages: 7
Registered: March 2013
Location: Hyderabad
Junior Member
Hi Blackswann, If you don't understand the requirement.Please don't reply to any post with your negative atitude.
Re: SQL LOADER [message #596960 is a reply to message #580873] Fri, 27 September 2013 15:18 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
How can you say that sqlldr is only for 1 table.Is there any Oracle document says that.If Yes,Could you please share us that document,so that we will also understand.....
Re: SQL LOADER [message #596964 is a reply to message #596959] Fri, 27 September 2013 17:03 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
sri3 wrote on Fri, 27 September 2013 13:17
Hi 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
Previous Topic: How to get parent table column reference and put in child table while loding data using sql loader
Next Topic: EXCLUDING DATA OF SOME TABLES in EXPDP
Goto Forum:
  


Current Time: Thu Mar 28 08:34:21 CDT 2024