 log file creation [message #323435] |
Wed, 28 May 2008 08:29  |
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
|
Senior Member |
|
|
I have a .sh file which contains
. $SVEN_HOME/config/configurator.cfg
sqlplus $leninlogin @$general_weekly/ACE2_HISTORY/ACE2_History.sql
I want to add a log file to track the log of job completion?
This .sql file simply truncates a table and it will be automated in control M tool.
But please advice how to write the script for log creation and with proper message.
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: log file creation [message #323788 is a reply to message #323786] |
Thu, 29 May 2008 08:01  |
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
|
Senior Member |
|
|
there is only one statement
truncate table ACE2_DELETED_LIST;
PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'
col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;
PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'
Begin
spool &filename
truncate table ACE2_DELETED_LIST;
spool off
exit;
This is the current code where the table truncated message is comming to the log file in case of success and in case of failure the error message
hence it should have the user defined message in case of success and error message in case of error
[Updated on: Thu, 29 May 2008 08:05] Report message to a moderator |
|
|
|
| Re: log file creation [message #323800 is a reply to message #323788] |
Thu, 29 May 2008 09:17 |
 |
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
First remove "Begin" line, then:
PROMPT '**************************************************'
PROMPT 'Generating Log File
PROMPT '**************************************************'
col filename new_value filename
SELECT 'ACE2_DELETED_LIST_'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.log' filename
FROM DUAL;
whenever sqlerror exit 1
set feedback off
PROMPT '**************************************************'
PROMPT 'cleaning the deleted lists from ACE2_DELETED_LIST '
PROMPT '**************************************************'
spool &filename
truncate table ACE2_DELETED_LIST;
prompt Success!
spool off
exit; [Updated on: Thu, 29 May 2008 09:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: log file creation [message #323920 is a reply to message #323914] |
Fri, 30 May 2008 01:06 |
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
|
Senior Member |
|
|
Yes this was what i wanted,Thanks
but if you can see my previous mail carefully,i wanted the additional thing which actually makes better redeability of the code
| Quote: | secondly when i execute the .sh file,if it is success then it shows the success prompt and the file creation as well but when it is a failure then
this error message should only reflect in the log file(the user executing the .sh script should get just a message that it failed and please check the log file for details,and as it is happening now the error is there in the log file but the same thing is also appearing in the shell while executing the .sh file)
|
[Updated on: Fri, 30 May 2008 01:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: log file creation [message #324218 is a reply to message #324131] |
Sat, 31 May 2008 12:45 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
There are two "EOF" tags (you can use just about any string you want). They form a "here document" structure in shell scripting. Take care not to indent the second one in KSH it must be in the first colum. Everything between the tags is basically what you'd normally have in a separate sql script.
The commented out line (#sqlplus...) falls withing the here document, so SQLplus gets it and doesn't know what to do with it. You can comment something in sql using "--" rather than "#".
/dev/null is a null sink. You redirect output that you aren't intrested in seeing to it.
No need to touch the file first - that just creates an empty file if it doesn't already exist.
If you really want a SQL log, then use all the propmts, etc that you're interested in and redirect to your log, then just cat the log when error is detected.
rv=return value. $? always contains the return code of the last statement you ran in KSH. You capture it into a variable to ensure that you don't accidentally look at $? for the wrong statement, like say an echo (that you put in for debugging)before checking the return code.
Try something like this (untested)
. $SVEN_HOME/config/configurator.cfg
LOG_FILE=$general_weekly/ACE2_HISTORY/ACE2_DELETED_LIST_`date '+%Y%m%d'`.log
touch $LOG_FILE
sqlplus -s /nolog<<EOF >$LOG_FILE
whenever sqlerror exit 1
connect $leninlogin
set echo off
prompt Trying to truncate ACE2_DELETED_LIST now...
truncate table ACE2_DELETED_LIST;
EOF
rv=$?
#echo Retval is $rv
if [ $rv -eq 0 ]; then
echo "Successfully Truncated"
else
echo "ERROR. Failed to complete the process"
cat $LOG_FILE
fi
# exit with return code, so that your scheduler knows
# if this script failed (1-255) or succeeded (0)
exit $rv
Unix return code is limited to 1 byte
Oracle return codes are almost always negative numbers (e.g. ORA-01012 value is -1012, so don't try returning "whenever sqlerror exit SQL.SQLCODE"
http://www.toadworld.com/Education/StevenFeuersteinsPLSQLExperience/TrainingandPresentations/MiscellaneousPLSQLTraining/tabid/173/Def ault.aspx#Wierd
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
