Solved

Kron Script to connect to oracle and execute queries automatically

Posted on 2006-06-29
7
1,855 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
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My previous tech tip, Installing the Solaris OS From the Flash Archive On a Tape (http://www.experts-exchange.com/articles/OS/Unix/Solaris/Installing-the-Solaris-OS-From-the-Flash-Archive-on-a-Tape.html), discussed installing the Solaris Operating S…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

831 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