Home » Infrastructure » Unix » shell scripts to read data from a text file and to load it into a table
shell scripts to read data from a text file and to load it into a table [message #356317] Thu, 30 October 2008 05:15 Go to next message
pallavishetty
Messages: 3
Registered: October 2008
Junior Member
Hi All,
I have a text file consisting of rows and columns as follows,

GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001
GEF001 000093625 MKL005675 000001 000000 000000 000000 000000 000000 000001

My requirement is that, i should read the first 3 columns of this file using a shell script and then i have to insert the data into a table consisting of 3 rows in oracle
Re: shell scripts to read data from a text file and to load it into a table [message #356325 is a reply to message #356317] Thu, 30 October 2008 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why not just calling SQL*Loader to fill your table?

Regards
Michel
Re: shell scripts to read data from a text file and to load it into a table [message #356326 is a reply to message #356317] Thu, 30 October 2008 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, as I don't speak Unix, I can only suggest you to check SQL*Loader utility. This is a link to 10g version (as you obviously didn't pay much attention to OraFAQ Forum Guide; if you did, you'd provide some more information, such as Oracle database version).

Shortly: write a control file which would tell SQL*Loader how to interpret your text file and load information you need into corresponding table columns.

Or, if your database version supports it, you might also research use of external tables feature.
Re: shell scripts to read data from a text file and to load it into a table [message #356329 is a reply to message #356325] Thu, 30 October 2008 06:03 Go to previous messageGo to next message
pallavishetty
Messages: 3
Registered: October 2008
Junior Member
am asked to write shell script.
Re: shell scripts to read data from a text file and to load it into a table [message #356332 is a reply to message #356329] Thu, 30 October 2008 06:17 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
anacedent
While a shovel is a great tool for making a hole in the ground, it is suboptimal for screwing in a screw.

Tell them that shell script is used for making a hole in the ground; SQL*Loader or external tables feature is a tool for loading data from text files into Oracle tables.
Re: shell scripts to read data from a text file and to load it into a table [message #356333 is a reply to message #356332] Thu, 30 October 2008 06:39 Go to previous messageGo to next message
pallavishetty
Messages: 3
Registered: October 2008
Junior Member
the whole application is deployed in unix and that text file comes from mainframe. am working in the unix side of the application and i cant access the data directly from the mainframe. so am required to write a script which reads the data from text file which is placed in certain location and i have to load it to oracle database.

Please help me something with this...

Thanks in advance.
Re: shell scripts to read data from a text file and to load it into a table [message #356351 is a reply to message #356333] Thu, 30 October 2008 08:32 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It can't be done in a "pure" shell script. Period.

You need some sort of executable or database library to interact with the database.

So use SQL*Loader or external tables, that's what they are there for.
Re: shell scripts to read data from a text file and to load it into a table [message #356355 is a reply to message #356351] Thu, 30 October 2008 08:39 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No use, Thomas.

is asked to write shell script.
Re: shell scripts to read data from a text file and to load it into a table [message #356358 is a reply to message #356355] Thu, 30 October 2008 08:46 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, we can't let people think we don't give people the best possible solutions that fit their requirements, so here is a shell script that does as much as is possible for this case:

#!/usr/bin/bash
echo "Error - This will not work. Replace programmer"
exit 1


Just ship that to the customer, problem solved. Very Happy

[Updated on: Thu, 30 October 2008 08:47]

Report message to a moderator

Previous Topic: shell script
Next Topic: ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
Goto Forum:
  


Current Time: Tue Dec 10 01:43:00 CST 2024