Solved

Kron Script to connect to oracle and execute queries automatically

Posted on 2006-06-29
7
1,868 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

This tech tip describes how to install the Solaris Operating System from a tape backup that was created using the Solaris flash archive utility. I have used this procedure on the Solaris 8 and 9 OS, and it shoudl also work well on the Solaris 10 rel…
I promised to write further about my project, and here I am.  First, I needed to setup the Primary Server.  You can read how in this article: Setup FreeBSD Server with full HDD encryption (http://www.experts-exchange.com/OS/Unix/BSD/FreeBSD/A_3660-S…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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