Shell Programming and Scripting

Hi

My question needs two answers

how to write scripts to update a table in oracle db based on the result of the number of record counts

for example
i need to execute the following script every hour

Code:

awk '{sum[$4]++;}END{for(i in sum) {print i, sum[i]}}' filename

here everyhour the filename will be changing...like 200903042106 at 22:05 hrs and 200903042206 at 23:05hrs

(year month day hr min format)

the output will be varying for each execution
for e.g output for 200903042106 will be

Code:

RTMS 100
BSNLSERVICE1 324

and output for 200903042206 will be

Code:

RTMS 50
BSNLSERVICE1 264
CAE 25
PFS 98
DATING 49

I WILL BE HAVING A TABLE WITH ALL POSSIBLE COULMNS (WHICH MAY COME IN THE OUTPUT OF THE SCRIPT LIKE...RTMS)

the think is i should be able to read what is first value (RTMS or BSNLSERVICE1 or CAE...etc) and then insert in to the db, the count corresponding to that column leaving other columns with null values or blank...along with the timestamp in timestamp column of the time table which i can make use for querying purpose...like between dates or interval...etc


DB should reflect like the following:

Code:

TIMESTAMP | RTMS | BSNLSERVICE1 | CAE | PFS | DATING |
200903042106 | 100 | 324 | 0 | 0 | 0 |
200903042206 | 50 | 264 | 25 | 98 | 49 |

how it can be achieved?

then 2nd question
about creating the cronjob dynamically with varing file names as told already

with thanks
Aemunathan

this would give you current date and time:

Code:

date '+%G%m%d%H%M'

similarly, you can prepare the required timestamp

to know about cron and how to setup a cron job see this

Hi

Am trying to connect to oracle db like ....

created one script file like connect.sh which contains

Code:

#!/bin/bash
cd /u01/app/oracle/product/9.2.0/bin/
sqlplus -l xxx/xxxx@yyy <<EOF
select distinct application_code from event_service;
exit
eof

if i execute this by ./connect.sh it works fine

but when i added this in to the crontab...it throws some error

crontab file entry

Code:

15 * * * * /aemu/connect.sh > /aemu/logs.txt 2>&1

it is not storing the output to the file name logs.txt as well

under /var/mail am seeing some mails related to this....

Code:

Date: Sat, 7 Mar 2009 22:45:00 +0530 (IST)
From: Super-User <root@ss.com>
Message-Id: <200903071715.n27HF0Yv002256@ss.com>
To: root@ss.com
Subject: Output from "cron" command
Content-Type: text
Content-Length: 141

Your "cron" job on rtmssrv1
/aemu/connect.sh > /aemu/logs.txt

produced the following output:

/aemu/connect.sh: sqlplus: command not found

can anyone help me out to find the errors

hi

i have tested connecting to db through script...i was missing the path from where i can run sqlplus command ....

now connection to db is ok...

how to update the table with the two column values i may receive from the command

Code:

awk '{sum[$4]++;}END{for(i in sum) {print i, sum[i]}}' filename

how to fetch those values and add in table along with date info.


Please help me out!!!