HomeОбразованиеRelated VideosMore From: Abbasi Asif

How to use Oracle SQL Loader sqlldr to load data into table from text file

82 ratings | 45748 views
How to use Oracle SQL Loader sqlldr to load data into table from text file create table emp(empno number(10),ename varchar(30)); insert into emp values(10,'asif'); set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on spool d:\oradata\sqlloader\data.txt select empno || ',' || ename from emp; spool off delete emp; update file named data.txt create a file named control.txt load data infile 'd:\oradata\sqlloader\data.txt' into table emp fields terminated by "," optionally enclosed by '"' ( empno, ename ) sqlldr asif/asif control=d:\oradata\sqlloader\control.txt
Html code for embedding videos on your blog
Text Comments (17)
snmn77 (1 year ago)
Getting error: LRM-00112: multiple values not allowed for parameter 'control'
pardeep kumar (1 year ago)
you never committed after deleting data from table
Sathya Narayanan (2 years ago)
Excellent piece of work
Nitin Sinha (2 years ago)
Please keep up the good work. Danke !
upasna dhruv (2 years ago)
I GOT THIS ERROR WHAT TO DO NEXT SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 10 22:19:43 2016 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL*Loader-522: lfiopn failed for file (LOADER_CONTROL.log)
upasna dhruv (2 years ago)
really proved helpful plz keep uploading such videos i found it easy and simple  thanks
chetan agrawal (2 years ago)
Thanks a lot for video
RIYAS RAHMAN A (2 years ago)
thank you very much
Sashidhar Reddy (4 years ago)
Hi Asif, i have implemented this in my academic project where it has 5 tables. Here it has circular references (foreign key), so what actually happening is 1.i have created the tables without any constraints. 2. then i have added the constraints using ALTER statement. 3. Now using the control files i have created, i tried to insert into the tables. But nothing is getting inserted into the tables. Can you help me with this.  Thanks in advance -sashi
Robert Thomas (3 years ago)
I know this is 6 months old....but I'd load the data with the constraints disabled, then enable them after the load.
jasimnov05 (4 years ago)
very helpful,thanks for sharing
Vinesh Nair (5 years ago)
Thanks!
Sudhakar Amineni (5 years ago)
Thanks Asif Nice Explanation...........
MJ (5 years ago)
Thanks Asif
madhumathi karanth (5 years ago)
simple and apt !
Gina Polych (5 years ago)
I have oracle 9i on my computer can i use the same commands? Do the commands -- fields terminated by "," optionally enclosed by '"' work on oracle 9i too? please answer me thanks ================== load data infile 'd:\oradata\sqlloader\data.txt' into table emp fields terminated by "," optionally enclosed by '"' ( empno, ename )
SupermanKelly™ (5 years ago)
Many steps. Does Oracle not use a UNLOAD and LOAD command? Ie. Informix. UNLOAD TO "file.unl" SELECT * FROM employees; DELETE FROM employees; LOAD FROM "file.unl" INTO employees;

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.