Solved

Kron Script to connect to oracle and execute queries automatically

Posted on 2006-06-29
7
1,860 Views
Last Modified: 2010-10-05
Hi

I have couple of questions.

1. How can we connect to Oracle Sql directly form KRON script?
2. I have some log information in one table, how to fire a select query to get data from that table, after getting results i want to store those results into one file.
3. File name should be current system date, how can we create that text file in a specified location and store/append sql results into that file.
4. After creating and appending results,
                I have one more log file called "trace.log". in that file i have some oracle log information.
                How to grep oracle related errors from that file and append results to the newly created textfile.for ex todays date is 29062006, somy filename should be like "29-06-2006.txt"

Finally my text file must contatin sql query results + greped trace.log file results.

I need all these in only one script. Once i run that script, that is able to connect to oracle, fire a select query, get the results, create a new text file with present system date, get grepped results from trace.log file and append results to "$Sysdate.txt" file.

Thanks in advance
 and its very very urjent i have to complete this task by tomarrow.  I need quick responces.

0
Comment
Question by:anumalas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 12

Expert Comment

by:stefan73
ID: 17008370
Hi anumalas,

1/2. A cron script is like any other script, except it has no screen output. So you could do:

sqlplus SCOTT/TIGER <<EOL > output
select count(1) from dual;
EOL

(or you could use the comprehensive output mechanisms of sqlplus)

3/4. Like
FILENAME=$(date +'%d-%m-%Y.txt')
command >>! $FILENAME


Enjoy,

Stefan
0
 
LVL 3

Author Comment

by:anumalas
ID: 17008396
Hi Stefan73

sorry for the spel mistake that is not KRON/cron.   Im using korn shell. so i need complete steps in .sh file can u give me completed script except database connection bocz i have some environmental varialbles to connect to database.

thanks in advance
0
 
LVL 12

Expert Comment

by:stefan73
ID: 17008475
anumalas,

Sure:
FILENAME=$(date +'%d-%m-%Y.txt')
sqlplus SCOTT/TIGER <<EOL >> $FILENAME
select count(1) from dual;
EOL

...Be aware that anyone on your machine can see your sqlplus call, including your password parameter.

Stefan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:anumalas
ID: 17008799
Hi


After appending sql results i want to add some data to that txt file that data is available in other log file i will use grep to filtet that data as per my requirement after that i want to re open my text file and i want to add results which are came form grep command to this file so how can i do that??

menas how to reopen that text file and append results to that?

Thanks once again....
0
 
LVL 4

Accepted Solution

by:
JulieBouillon earned 500 total points
ID: 17008917
Hi,

Simply add a grep after EOL, like this:

FILENAME=$(date +'%d-%m-%Y.txt')
sqlplus SCOTT/TIGER <<EOL >> $FILENAME
select count(1) from dual;
EOL
grep yourpatern /path/to/your/logfile >> $FILENAME
0
 
LVL 3

Author Comment

by:anumalas
ID: 17041144
Hi

after executing above script it is also adding sql query to my generated text file. But i dont want to put query in that text file how to remove those lines from the generated file before adding grep results to that file.

I need reply asap. I have tried with sed but its showing output to console. i have tried like bellow

FILENAME=$(date +'%d-%m-%Y.txt')
sqlplus SCOTT/TIGER <<EOL >> $FILENAME
select count(1) from dual;
EOL
sed -e '1,10d' $FILENAME > $FFILENAME
grep 'mypattern'  /path/to/your/logfile >> $FILENAME


But it is showing empty results+grepped results.

I think im doing little mistake while overwriting to same text file.

Hope i will get reply soon....
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 17041206
sed -e '1,10d' $FILENAME > new-$FFILENAME
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
When you do backups in the Solaris Operating System, the file system must be inactive. Otherwise, the output may be inconsistent. A file system is inactive when it's unmounted or it's write-locked by the operating system. Although the fssnap utility…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question