shell script to find filename & line count of each file, now insert this record into Oracle table.

Question

Hi,

I have to find the filename available in a folder with each file line count. And then, i will have kind of two column data.

Now i have to insert this record into a oracle table having two column(col1, col2).

Can i write a shell script which will do both.

I found here itself of writing the first part. i.e

wc -l *| egrep -v " total$" | awk '{print $2 " " $1}' > output.txt

Now, how will insert data of output.txt into oracle table.

Thanks, Neel.

Answer 1

You can load text data into Oracle with its command-line tool SQL*Loader. There is too much to describe here about how to use SQL*Loader, start by reading this web page:

http://www.orafaq.com/wiki/SQL*Loader_FAQ

Answer 2

I do not know Oracle, but it appears that the SQL syntax is quite similar to MySQL.

In essence you, you would do what you have done here with one minor change.

wc -l *| egrep -v " total$" | awk '{print $2 "|" $1}' > output.txt

You would write an SQL script called thesql.sql that would look like the following:

LOAD DATA
INFILE output.txt
INTO TABLE yourtable
FIELDS TERMINATED BY '|'
(col1, col2)

Then, in your script (this is where I am hazy), have a line like this:

sqlplus <user>/<pw>@<db> @thesql.sql

I found some help from a couple of different sources - here and here.

Hope this helps!

Answer 3

In version 9i Oracle gave us external tables. These objects allow us to query data in OS files through SELECT statements. This is pretty cool. Even cooler, in 11.0.1.7 we can associate a shell script with an external table to generate its OS file. Check out Adrian Billington's article on listing files with the external table preprocessor in 11g. Your shell script is an ideal candidate for the preprocessor functionality.

If you need to know the contents of the directory now for whatever purpose you can simply SELECT from the external table. If you want to keep a permanent record of the file names you can issue an INSERT INTO ... SELECT * FROM external_table;. This statement could be run autonomically using a database job.

Answer 4

You have two choices. Both I tested with this table structure:

SQL> create table tbl_files(fileName varchar(20), lineCount number);

First choice is generate sql script with generate insert SQL commands, and run sqlplus command line utility. I little bit modified your shell script:

wc -l *| egrep -v " total$" | awk '{q=sprintf("%c", 39); print "INSERT INTO TBL_FILES(fileName, lineCount) VALUES (" q$2q ", " $1 ");";}' > sqlplusfile.sql

After run this script file "sqlplusfile.sql" have this content:

INSERT INTO TBL_FILES(fileName, lineCount) VALUES ('File1.txt', 10);
INSERT INTO TBL_FILES(fileName, lineCount) VALUES ('File2.txt', 20);
INSERT INTO TBL_FILES(fileName, lineCount) VALUES ('File3.txt', 30); 

Now you can run directly sqlplus command with this file in parametr:

sqlplus username/password@oracle_database @sqlplusfile.sql

After run this script, table look like this:

SQL> select * from tbl_files;

FILENAME              LINECOUNT
-------------------- ----------
File1.txt                    10
File2.txt                    20
File3.txt                    30

Note: At the end of file "sqlplusfile.sql" must be present "commit;" otherwise, you will not see data in the table.

Second choice is to use sqlldr command line tool (this tool is part of the installation of Oracle Client)

Again, little bit modified your script:

wc -l *| egrep -v " total$" | awk '{print "\""$2"\"" "," $1}' > data.txt

Content of file "data.txt" look like this:

"File1.txt",10
"File2.txt",20
"File3.txt",30

In the same directory I created the file "settings.ctl" with this content:

LOAD DATA
INFILE data.txt
INSERT
INTO TABLE TBL_FILES
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(fileName, lineCount)

Now you can run this command, which loads data into the database:

sqlldr userid=user/passwd@oracle_database control=settings.ctl

sqlldr utility is better choice, but in some Oracle Client installation is not present.