Link to home
Start Free TrialLog in
Avatar of oracle_rookie

asked on

Sql loader through shell scripting

Hi All Experts,

I have never worked on oracle and i have just got new work on oracle.I need to read a csv file into oracle table using sql loader and i need to use unix shell scripting also for scheduling this occurence ever 24 hours interval,because after every 24 hours new csv file will be generated and new data should be appended in oracle table automatically.

Please guide me from basics.I urgently need anybody's help.Please help!!!!!!!!!!!!!!!

An oracle rookie
Avatar of rockiroads
Flag of United States of America image

From the basics, hmm, well a fair bit involved, mostly understanding how to use sql loader.

Basically you create a shell script which runs the sql loader command
Secondly you use cron to schedule the task

So 2 things to read up on

With sql loader it takes various arguments. You will need to create a control file also which is a text file containing the ddl. Since there is a fair bit try reading up on oracle refs eg

The command line arguments for sql loader take in the following
control filename
log filename
rejected records (bad) filename
name of file to import

see this for more info

I think the first thing to do is to understand what you need to import, create the control file and play about with it on your shell.

Once you feel you have mastered it, add that (along with other checks you need to do) in a shell script then update the crontab and schedule the task to run that script. eg or
The EE lot here will help you as you go along but my first post was to try make you understand what is involved. There might be other suggestions but I am just telling you what I have done in the past.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

If you can provide a table definition and sample data I can provide a SQL*Loader script to load it.

Then it's a simple matter to create the shell script and cron it as described above.
Avatar of oracle_rookie


Hi slightwv,

below is the table structure which i am using to upload.As of now i am using external table but now i need to use sql loader in shell scriting because there is two reasons in sql loader we can handle bad data well and i also need scheduling of this script.So i want your help in this process.below is the code which i have been using so far:
CREATE TABLE cash_desk_ext
  Branch   varchar2(20),
  Cash_desk   number,
  Cash_deskTXT   varchar2(20))
       (records delimited BY newline
            terminated BY ','
            optionally enclosed BY '"'
            missing field VALUES are NULL
     LOCATION ('Cash Desk Locations.csv')

create table cash_desk as select * from Cash_Desk_ext;

Thanks to all for giving so early and good response!!!!!
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for such a nice solution.

Just wanted to confirm that i need to put the given code in control file.and i need to run the below syntax in unix comman window:

sqlldr username/password control=myControl.ctl

And one more thing .ctl file is different or i can save code in simple note pad and named it with .ctl extension.

Once again thanks a lot.
Also where i need to write the below command:

  Branch   varchar2(20),
  Cash_desk   number,
  Cash_deskTXT   varchar2(20)

in the control file itself or anywhere else.
have u heard of sqlplus? it should be installed as part of your oracle install. Unless you want to run the test sql you just need to run your original sql.
Change the control file to reflect your tablename and columns.

run that, login using your userid and password and run the sql created for you by slightwv

the first link I gave you describes the control file and what each command does. it would be advisable you read that so you gain an understanding. Look for Example 5-1 Sample Control File. The whole page describes in detail the usage.

your line sqlldr username/password control=myControl.ctl should be okay

and regarding your question And one more thing .ctl file is different or i can save code in simple note pad and named it with .ctl extension.
yes its just a plain text file

>>Also where i need to write the below command:

I just provided that as the setup I used in my example.  The table you need to load should already exist in the database.  You do not need to keep creating it.

>>need to run the below syntax in unix comman window:

As long as the Oracle environment is set up properly that should be all you need.  If you type sqlldr with no parameters and the 'help' displays, you should be good.
Hi Slightwv/rockieroads,

Thanks for such a nice reply.I know you people got little bit irritated by me.I really apologize for that.But you can understand that i have downloaded putty and seen unix command window just two days back.So i have asked stupid questions.I will try all these steps tomorrow in my office and if any problem occurs then i will let you ppl know.

You people are really excellent in explaining things.

so was my posts actually helpful to you then?
yes dear ,you told me about sqlplus .i was not thinking about that.Now i will try both things and let's see what will work for me.But both of you have given good replies.
as well as what you need for scheduling lol.

I thought for a minute that my posts were not helpful considering nothing I posted was recognized in terms of acceptance.

Oh well. Good luck with the rest of your oracle task though. Suggest you go on a basic unix course for shellscripting if your new to it
Hi All,
Please see the attached image.I am getting the problem in running sqlplus command.

This should probably be a seperate question.
Your oracle environment isn't set up properly.

In your profile do the following:

#replace ORCL with your database name
. oraenv

Hi slightwv,

Thanks for your reply.
I could not get what you mean by doing in your profile?please explain.Actually i am on client site so what i need to say client unix server administrator.He has created one unix server username and password for me and one connection name,username and password for oracle database.
Please let me know if i need to ask administrator for something in this case or i need to do myself.

oracle_rookie, maybe you should understand the first thing that slightwv says

This should probably be a seperate question.

you have closed this question by accepting the post that you felt helped you the most. the way ee works is once its closed if you need further help on ANOTHER issue then you ask a new question. Surely it cannot be that difficult? Asking a new question will probably gain you more responses.

I agree with rockiroad.  You really need to open another question on this.
Hi Experts,

I have asked new question related to similar thing and also i have attached a script.Please explain what script is doing.below isthe link for new question:

thanks a lot.
A new question should get you more responses.

Remember one thing though when you have got help from multiple people, and their posts helped you in solving your problem then remember to split points

I know you are new to EE but you need to look at the posts given. Like this for example, my first post gave you info you needed for the question you asked about sql*loader and scheduling and slightwv was very helpful in giving you a practical solution. So take this as an example  and from now on reward those who you feel contributed in solving your question.

Good luck!

Hi rockiroads,

I really apologise for not giving your comments any point.Actually i was new to EE and was not aware of it.I will take care of it in future and will divide points accordingly.
No problems, I am aware that you are new to EE as I said before. I am just ensuring you remember the rules going forward.