Solved

Sql loader through shell scripting

Posted on 2010-09-08
23
2,523 Views
Last Modified: 2013-12-21
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
0
Comment
Question by:oracle_rookie
  • 9
  • 9
  • 5
23 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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 http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm

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


see this for more info http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm

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 http://www.unixgeeks.org/security/newbie/unix/cron-1.html or http://kb.iu.edu/data/afiz.html
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 

Author Comment

by:oracle_rookie
Comment Utility
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,
  OFTXT   VARCHAR2(20),
  Cash_deskTXT   varchar2(20))
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY SOME_DIR
     ACCESS PARAMETERS
       (records delimited BY newline
        fields
            terminated BY ','
            optionally enclosed BY '"'
            lrtrim
            missing field VALUES are NULL
      )
     LOCATION ('Cash Desk Locations.csv')
  )
PARALLEL
     REJECT LIMIT UNLIMITED;

create table cash_desk as select * from Cash_Desk_ext;


Thanks to all for giving so early and good response!!!!!
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
sql loader control files look very similar to external table syntax.

Given the table( I changed the name for my test database):
drop table tab1 purge;
CREATE TABLE tab1
(              
  Branch   varchar2(20),
  Cash_desk   number,
  OFTXT   VARCHAR2(20),
  Cash_deskTXT   varchar2(20)
);


The sql loader control file is in the code window below.

The command line to execute it is:
sqlldr username/password control=myControl.ctl
load data

   infile 'Cash Desk Locations.csv'

   replace

   into table tab1

	fields terminated BY ','

	optionally enclosed BY '"'

   TRAILING NULLCOLS

(

	Branch,

	Cash_desk,

	OFTXT,

	Cash_deskTXT

)

Open in new window

0
 

Author Comment

by:oracle_rookie
Comment Utility
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.
0
 

Author Comment

by:oracle_rookie
Comment Utility
Also where i need to write the below command:

CREATE TABLE tab1
(              
  Branch   varchar2(20),
  Cash_desk   number,
  OFTXT   VARCHAR2(20),
  Cash_deskTXT   varchar2(20)
);

in the control file itself or anywhere else.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 

Author Comment

by:oracle_rookie
Comment Utility
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.

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
so was my posts actually helpful to you then?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:oracle_rookie
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
0
 

Author Comment

by:oracle_rookie
Comment Utility
Hi All,
Please see the attached image.I am getting the problem in running sqlplus command.
Regards,
Shyam
 

Unix-command-image-for-EE.JPG
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
This should probably be a seperate question.
Your oracle environment isn't set up properly.

In your profile do the following:

ORACLE_ASK=NO
#replace ORCL with your database name
export ORACLE_SID=ORCL
. oraenv

0
 

Author Comment

by:oracle_rookie
Comment Utility
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.

Shyam
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I agree with rockiroad.  You really need to open another question on this.
0
 

Author Comment

by:oracle_rookie
Comment Utility
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:

http://www.experts-exchange.com/Programming/Languages/Scripting/Shell/Q_26482259.html

thanks a lot.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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  http://www.experts-exchange.com/help.jsp#hs=8&hi=100

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!

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
0
 

Author Comment

by:oracle_rookie
Comment Utility
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.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now