how to get result from both of these modules?

sysmicuser · 04-28-2013, 03:35 AM

Hallo All unix Guru's

I have written a small (static) pl/sql function which is inserting data into database. It is as follows:

Code:

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.

Code:

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:

Code:

/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.

Code:

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.

chrism01 · 04-28-2013, 08:32 PM

Using bash arrays

Code:

for line in $(<t1.t)
do
    rec=$(echo $line|sed 's:/: :g')
    a=($rec)
    echo "INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, '${a[2]}', '${a[3]}', '${a[4]}', '${a[5]}');"
done

# output
INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd3', 'd4', 'waiting', 'abcd.txt');
INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd31', 'd42', 'waiting', 'pqrs.txt');
INSERT INTO stuck_files(COL_DATE,SER_NAME,TYPE_FILE,FILENAME,FILE_STATUS) VALUES (sysdate, 'd32', 'd43', 'waiting', 'xyz.txt');

Just save output to a file eg my.sql, then iirc(?)

Code:

sqlplus user/pass @my.sql

sysmicuser · 04-29-2013, 01:36 AM

I have got another way and it says success but there is no data in database !!

sysmicuser · 04-29-2013, 01:43 AM

Have a look at the source code. Doesn't work.

Code:

			if [[ ${no_of_files_stuck_input_directory} -eq 0 ]] ; then
				echo -e "No files stuck in input directory\n" >> $LOG_FILE
			else
				echo -e "\nThere are ${no_of_files_stuck_input_directory} files stuck in input directory and their names are as follows:\n"
				N=0;
				for i in $(find ${BASE_DIRECTORY} -path \*/input/* -type f -not -wholename "*/restore/EAReturn*" -mmin +60 -print) ; do
				input_files_array[$N]="$i"
			     #	echo "$i"
				#	echo $file_name
					let "N= $N + 1"
				done >> ${input_files}
				for i in ${input_files_array[@]}
					do
						file_name=$i
						echo -e file_name[$i]
						service_name=$(echo $i|cut -d'/' -f4)
						echo -e service_name [$i]
						file_type=$(echo $i|cut -d'/' -f5)
						echo -e "file_type [$i]\n"
						stuck_type=$(echo $i|cut -d'/' -f6)
						echo -e "stuck_type [$i]\n"

#       				sqlplus username/password@database <<-EOF
        				sqlplus $CONNECT <<-EOF
--        				DECLARE
--        				Counter integer :=1;
--	        			BEGIN
--	        			WHILE Counter <= ${no_of_files_stuck_input_directory} LOOP
--					INSERT INTO stuck_files(COLLECTION_DATE,SERVICE_NAME,FILE_TYPE,FILENAME,STUCK_TYPE) VALUES (sysdate,'$service_name','$file_type','$file_name','$stuck_type');
--	        			Counter := Counter + 1;
--	        			END LOOP;
--	        			END;
--	        			/
--					commit;
					exit;
					EOF
					done
			fi

Unfortunately I cannot see anything into the database !! why?

sysmicuser · 04-29-2013, 10:11 AM

@chirsm01

Excellent stuff !the only thing which I am wondering is instead of having just abcd.txt as name of file which you have indicated with
'${a[4]}' If I want to have the entire name(absolute path) what needs to be modified?

I tried with something like this 'line[$i]' but that didn't work.

sysmicuser · 04-29-2013, 10:25 AM

Looking fine now. All I did was instead of '${a[5]} I did was $line and it came through beautifully !!!