Sunday, May 18, 2008

List and search all the files in the HDD on a windows box using simple OS commands and SQL loader (Oracle)

List and search all the files in the HDD on a windows box using simple OS commands and SQL loader (Oracle).

1) Goto command prompt and goto the root drive (c: or d:).

2) Type "dir /s /b >> d:\sample.txt" without the quotes

3) All the files in your drive (c: or d:) are listed in a raw format.

4) Get to your oracle database and create a table with just one column, say name.

create table filelist

(name varchar(2000))

I give 2000 as some path locations in sys vol info can sometimes be very very long.

5) At the OS level create a control file for the SQL loader.

--------------------Code starts-----------------

LOAD DATA

INFILE 'D:\sample.txt'

APPEND

INTO TABLE fileList

fields terminated by ''

(name char(2000))

--------------------Code ends-------------------

The

i) INFILE 'D:\sample.txt' should be the same file as the one in step 2 (Type "dir /s /b >> d:\sample.txt")

ii) (name char(2000)) /* if you dont specify the char(2000) you are bound to get a error

"sql loader Field in data file exceeds maximum length" */

6) Space occupied on the database table is around 8MB for around 99k odd records.

7) Run the SQL Loader command at the OS level.

sqlldr temp1/temp$1 control='D:\sample.ctl' log='D:\samplelog.txt'

where,

temp1/temp$1 are the username and password of the user/schema under which the table 'fileList' resides

sample.ctl is the file where the information about the table and source datafile is written and,

sample.log.txt is the log file which stores the logs of the SQL loader run

8) A successful run should typically have something like this.

=================log file================

Table FILELIST:

99479 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 96128 bytes(64rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 99479

Total logical records rejected: 0

Total logical records discarded: 0

=================log file ends================

9) After the successful run you can search for a particular in the drive by a simple SELECT statement

select name from filelist where name like '%file_name%';

And the output will be the files with the entire path location of the files in the drive.

The purpose of this snippet is just to show how SQL loader can be used.

This code can be modified further to accommodate a Java front end and be used as a file search utility for windows.

This process can also be automated by running all this in a batch file.

DISCLAIMER: Use the above code at your own risk and I will not be responsible for any damage caused to your machine.

No comments: