Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Kron Script to connect to oracle and execute queries automatically

Posted on 2006-06-29
7
Medium Priority
?
1,876 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 1500 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

Hello fellow BSD lovers, I've created a patch process for patching openjdk6 for BSD (FreeBSD specifically), although I tried to keep all BSD versions in mind when creating my patch. Welcome to OpenJDK6 on BSD First let me start with a little …
Installing FreeBSD… FreeBSD is a darling of an operating system. The stability and usability make it a clear choice for servers and desktops (for the cunning). Savvy?  The Ports collection makes available every popular FOSS application and packag…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
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.

597 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