By bluemind2005
I have written a small (static) pl/sql function which is inserting data into database. It is as follows:
sqlplus $CONNECT <<-EOF
DECLARE
Counter integer :=1;
BEGIN
WHILE Counter <= ${no_of_files} LOOP
INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate,'pqrs','pqrs','pqrs','pqrs');
Counter := Counter + 1;
END LOOP;
END;
/
commit;
exit;
EOF
However the above “insert” needs to be more of dynamic in nature, as such you can see that we want to insert data depending upon the variable value ${no_of_files}.
Now let me show you another module which is giving me the file(s)names which I want to insert.
N=0
for i in $(find . -path */waiting/* -type f -not -name "SS*" -mmin +120 -print) ; do
testarray[$N]="$i"
echo "$i"
let "N= $N + 1"
done
Sample Output from above command is as follows:
/d1/d2/d3/d4/waiting/abcd.txt
/d1/d2/d31/d42/waiting/pqrs.txt
/d1/d2/d32/d43/waiting/xyz.txt
Now the data insert which is given in the above pl/sql block has directly to do with this output. It going to be dynamic in nature.
Let us come back to mapping of how we need to insert data based on this output. I will go through the first insert.2 insert and 3rd insert so you would get an idea of design.
But these are very dynamic, meaning the insert depends on the output produced but yes the directory structure would remain same.
COL_DATE,SER_NAME,TYPE_FILE,FILEFILENAME,FILE_STATUS
sysdate,d3,d4,abcd.txt,waiting
sysdate,d31,d42,pqrs.txt,waiting
sysdate,d32,d3,xyz.txt,waiting
I believe this can definitely be implemented with help of magic like awk or anything but not able to implement it.
Any ideas/help would be greatly appreciated.
Add to favorites