LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 04-28-2013, 03:35 AM   #1
sysmicuser
Member
 
Registered: Mar 2010
Posts: 353

Rep: Reputation: 0
Question how to get result from both of these modules?


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.
 
Old 04-28-2013, 08:32 PM   #2
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.9, Centos 7.3
Posts: 17,396

Rep: Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395Reputation: 2395
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
 
1 members found this post helpful.
Old 04-29-2013, 01:36 AM   #3
sysmicuser
Member
 
Registered: Mar 2010
Posts: 353

Original Poster
Rep: Reputation: 0
I have got another way and it says success but there is no data in database !!
 
Old 04-29-2013, 01:43 AM   #4
sysmicuser
Member
 
Registered: Mar 2010
Posts: 353

Original Poster
Rep: Reputation: 0
Question

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?
 
Old 04-29-2013, 10:11 AM   #5
sysmicuser
Member
 
Registered: Mar 2010
Posts: 353

Original Poster
Rep: Reputation: 0
@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.
 
Old 04-29-2013, 10:25 AM   #6
sysmicuser
Member
 
Registered: Mar 2010
Posts: 353

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


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
am not getting any result when am using 'if', without getting result for below script alavala53 Linux - General 3 10-25-2012 06:00 PM
[SOLVED] Grep for the result of a command within the result of another command jasonws Programming 6 11-18-2010 02:39 PM
Slack9 - no PPP (can't open dependencies file /lib/modules/2.4.18/modules.dep) bluehz Slackware 1 05-04-2003 02:32 PM
Re: modprobe: Note: /etc/modules.conf is more recent than lib/modules/2.4.9/modules.d Andy.M Linux - General 1 01-24-2002 01:50 AM
Re: modprobe: Note: /etc/modules.conf is more recent than lib/modules/2.4.9/modules.d Andy.M Linux - Newbie 2 01-24-2002 01:40 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 10:49 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration