Solved

Kron Script to connect to oracle and execute queries automatically

Posted on 2006-06-29
7
1,828 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Author Comment

by:anumalas
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sed -e '1,10d' $FILENAME > new-$FFILENAME
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
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 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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now