Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


SQL LOADER versus UTL_FILE to read several files at one time.what to use?

Posted on 2003-11-24
Medium Priority
Last Modified: 2013-12-12
Hi experts,

I have an important task and i need fast help.
The  operating system is UNIX.From a defined directory i will read any number of files and i need to load every row from every files exists in the directory.Now i know to do it with utl_file ,but if the users want sql loader as the tool to load data ,how do i write the program to load from serveral files at one time.i only know to load 1 file into a table.IS it possible to write a program that will read all files from the direcotory?
IF so what in general the program is? If u know to write that program please explain to me what u recommand to use sql loader or utl_file methood to load the data.what is better in controlling bad rows and ,what is more efficient and why.If its not possible to write the program i asked and u explain me why, i will regard it as an answer also.The sqlloader will run from Oracle applications.
THANKS in advance
Question by:yeitan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 11

Accepted Solution

pennnn earned 750 total points
ID: 9812563
I would also go for the sql loader solution - it should be much faster than utl_file.
If you want to load all files from a directory and don't know the files names and the number of files, then I think the easiest way would be to concatenate all the files into a single file and load it. This way the name of the file to be loaded will be always the same and you'll have a simple and fixed control file. To concatenate all the files you just need to run one command:
cat /<directory_path>/* > file_name
cat /u80/home/input_files/* > /u80/home/input_files/input_file.dat

Then in the control file you'll have INFILE /u80/home/input_files/input_file.dat. You just need to run the unix command before running sql loader.
If the files have different format and/or headers, then you'll need to write a script to concatenate them manually.
Hope that helps!

Author Comment

ID: 9820142
hi pennnn

Your idea is nice and i will consider it,but there are problems with it.
If the files are big .Is there any problem to integrate them to one file?
Its sounds risky.What if this command doesnt succeed to combine all the files but only few of them? how can i know about it?maybe it cant be...i just ask...
When sql loader works in case it didnt succeed to load some rows,it creates a file with extension .bad ,i will have to try load the bad rows again-In the next loading process .Its difficult to have control .How can i only load the rows that were with a problem in the next loading process with out the rows that were ok?
When i work with utl_file in case there was an exception i can stop loading then i can mark or single out  the file with the problem and i will not remove it from the directory so that for the next loading process that file(after i correct it) can be loaded again (i will think how to deal with the rows from that file which were inserted with no problem at the 1st time-cause i dont want duplicate data).
all the rest files(with no exception whats so ever) will be removed from the directory so that it will not be loaded again.Do u understand me?With your idea i dont have all the control.This program is loading tables foe an interface,its a process that might happen even few time a day.So do u still think your idea is the best to our needs?

LVL 11

Expert Comment

ID: 9853575
I'm sorry I didn't get back to you earlier, but I was away for a while.
First, I have to say that the best solution is the one that suits YOU best. If you feel more comfortable with utl_file, then you can try creating a high-level prototype using utl_file and see if it shows acceptable performance.
Now if you want to try the SQL Loader/unix scripts solution there are several things that can probably help you.
You can always check in a shell script if the last command was successfull using the $? variable. Example:
if test $? -ne 0
   echo "ERROR"

Then you can use another approach to avoid the problem with the big files. You can process each file separately by using a control file template that can be updated for each files that needs to be loaded.
Here's a high-level algorithm (might have syntax errors):
1) Create a control file that has the following (or similar) line for the input file (the string INPUT_FILE_PLACEHOLDER is just an exmaple, you just have to use the same string later).:

The rest of the controlfile should be whatever you want it to be, just the INFILE will have to be as shown above.
2) The shell script should have the following algorithm (example):
for file in /your_path/*
   $ORACLE_HOME/bin/sqlldr userid=username/password control=$CONTROLFILE;
   if test $? -ne 0
      echo "ERROR in file "$file >> $yourlogfile

Let me know if it's not clear enough...
Hope that helps!

Author Comment

ID: 10051693
pennn hi...the soulution i picked was like the 1st comment of yours and i used sql loader and inside the shell script i used the cat cammand u gave me which works very good(united 70 files togethere with no problem)!!!


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question