Hello All,

I got a file LCDFile.log say for example having the file contents like this...

-------------------------------------------------------
Filename : LCDN00000.11122009
*
begin
ORA-04068 package gen is invalidated at line 1
ORA-04065 inproper inputs
end
------------------------------------------------------------
Now i want to write a script file that extracts the fields like 0RA-04068
ORA-04065 only.

can anyone help me in getting the output.

thanks

The following pipes your file through grep which extracts the rows starting with the letters ORA. This is then piped through sed which removes everything from the space onwards. The command to run is the first line, the output follows:

Code:

cat LCDFile.log | grep "^ORA" | sed 's/ .*//'

ORA-04068
ORA-04065

but the file contains lots of ORA-'S like ORA-06512, ORA-04061 etc.. but i want only two of them like ORA-04068 , ORA-04065 ..

in that case we can't use grep "^ORA" it extracts all but i need only few as i mentioned.

the question is how i can give multiple search ?

thanks for your response earlier .. appreciate if you can reply to this one too..

thanks/

Still not sure what you're after but try this :

But i am getting the text attached to the string too ..

egrep '(ORA-04068|ORA-04065)' LCDFile.log like

ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure

I need only ORA-040068 and ORA-04065

Thanks for you help in advance ..

So just remove everything after the :
or if you're confident these records will exist in your file you could use:

Got it .. THANKS YOU SO MUCH.

Thanks for your time , I really appreciate it.

I am facing the small problem with the command .

I am using the command in the .ksh script like this ... in this $ERROR_MSG is a file
here the code .. searching for ORA-04068,ORA-04061,ORA-06512 in the file $ERROR_MSG
========================================
ERROR_MSG=`cat $LOGFILE.$strFileName`
ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512)' $ERROR_MSG | sed 's/:.*//'`
RETCODE=$?
if (( $RETCODE != 0 )) then
continue
else
break
fi
====================================
when i run the file ..

i am getting the following output ..

All the information it is displaying is from the $ERROR_MSG file

dmgc1:/devel/mkt/app/tpe/current/scripts>ksh temp_fa.ksh
CCDB_CASE_INFO_UPD04162008040404
TotNumInFileIcldHdr is 6
File Name: CCDB_CASE_INFO_UPD04162008040404
egrep: can't open File
egrep: can't open Name:
egrep: can't open CCDB_CASE_INFO_UPD04162008040404<br>
egrep: can't open Current
egrep: can't open SEQ
egrep: can't open NUM
egrep: can't open 890592
egrep: can't open <table
egrep: can't open cellpadding="0"
egrep: can't open cellspacing="2"
egrep: can't open width="70&#37;">
egrep: can't open <tr><td>New
egrep: can't open Enrollment
egrep: can't open Cases</td>
egrep: can't open <td>0</td></tr>
egrep: can't open <tr><td>Enrollment
egrep: can't open -
egrep: can't open Required
egrep: can't open Verification</td>
egrep: can't open <td>0</td></tr>
egrep: can't open <tr><td>Sales
egrep: can't open -
egrep: can't open Required
egrep: can't open Verification:</td><td></td></tr>
egrep: can't open <tr><td>Transfer
egrep: can't open -
egrep: can't open Required
egrep: can't open Verification:</td><td></td></tr>
egrep: can't open <tr><td>New
egrep: can't open Cancellation
egrep: can't open Cases</td>
egrep: can't open <td>1</td></tr>
egrep: can't open <tr><td>New
egrep: can't open Refund
egrep: can't open Cases</td>
egrep: can't open <td>2</td></tr></table>
egrep: can't open PL/SQL
egrep: can't open procedure
egrep: can't open successfully
egrep: can't open completed.

Any idea why its happening like this ..? It should not be the case right ... I don't understood why its throwing the output on the console like this ..

Appreciate your help in this regard.

thanks/

The first command is putting the contents of the file into the variable ERROR_MSG - let's say it contains the file contains the text "I love cheese". So now the variable contains this string.

Next you're running the egrep command using the contents of this variable. So the command it's trying to run will look like this:
The shell will think you're trying to open 3 files - one called I, another called love and yet another called cheese. Would something like the following work?

It would be well worth printing each variable out when you set it just to make sure everything is doing exactly what you are expecting. It might also be worth investing in a small book on shell programming

Hi,

==========================================
sample file
---------------------------------------------------

File Name: CCDB_CASE_INFO_UPD04162008040404<br>
Current SEQ NUM 890592
begin
*
ERROR at line 1:
ORA-20000: Duplicate File found with sequence nb 890592 for the file
CCDB_CASE_INFO_UPD04162008040404
ORA-06512: at "TPEMGR_E3.PKG_TPE_FA_LOAD", line 615
ORA-06512: at line 3




========================================


If any of the ORA'S is found in the file the ERROR_CDE should be having only that value right? that's what i want from the line
ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512)' $LOGFILE.$strFileName | sed 's/:.*//'`

If i find any of the error codes specified i need to continue else break. this is what my requirement is ..I don't want to display on the console like what it was doing right now..

The error codes are found some where in the middle of file as given example and i need that error code (ORA-20000) only ,
and as per your code

if (( "$ERROR_CDE" > "" )) then
break
fi

i think if it find any thing in the file greater than null then the if condition breaks .. and comes out. but what i want is if it find's any of the ORA'S values only then it should continue else break.

Also the condition is giving syntax error at becoz of ">" and also in the script the variable ERROR_CDE has no data for the following statment in .ksh script file

--> ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512)' $LOGFILE.$strFileName | sed 's/:.*//'`



===================================
in this following ...code

ERROR_MSG=`cat $LOGFILE.$strFileName`
ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512|ORA-20000)' $ERROR_MSG | sed 's/:.*//'`
if (( "$ERROR_CDE" = "ORA-04068" || "$ERROR_CDE" = "ORA-04061" || "$ERROR_CDE = "ORA-06512" )) then
continue
else
break
fi


can i use the above code .. I want to test whether we found the any of the ORA'S in the file ..
================================================== ======================


Sorry to bothering you so much .. I am now learning and I will buy good book if you can suggest so that simple things get clarified.







thanks for your help in advance.

The first book I bought on Unix shell was "The Unix Programming Environment by Kernighan & Pike" and I'd still recommend it - you can get it 2nd hand of amazon for a couple of $. There may be better books these days but I wouldn't know what they are.

The code I gave you did pretty much what your code was trying to do - whether that was correct or not I don't know. I think with your program it might be a good idea to just say what it is you're trying to do - ie what the whole program is trying to do otherwise we'll be fixing one little error after another.

Let me tell you the complet story..

We have schedule jobs that calls the scripts and in turn the scripts calls the oracle packages and loads the data from the incoming files.

job--> script --> oracle

What we are experiencing is that our package is getting invalidated . What actually happens is when we scripts reads the file if every thing is ok it gets processed and if it fails we are moving to bad_files directory. whats now happening is due to package getting invalidated , the file is getting moved to bad_files directory and is not loaded into oracle tables . what we are doing now is taking out the file from bad_files directory and loading in next run and it is getting successfully processed.

What my intension is . .. when the package gets invalidated , trap the ora error [ the failure is happening with ORA-04061 and ORA-04068 error codes ]and the call the package at least for three times and during these three iterations i am trying to load the data and if i can't still for three iterations then move to bad_files directory.

Now what i am doing is trap the error code after calling the package - since the package if it gets invalidated we will have error codes and then if the error codes are found i call the package again and this continues for three times . once the loop finishes for three times if the file is not processed then move to bad_files directory.

I am attaching the file for you reference ..

# Sometimes, we want to email a second group, if that is the case
# we pass the second group to the function as a parameter.
function email_out
{
typeset EMAIL_LIST
if [[ -z $1 ]] then
EMAIL_LIST="$FA_MAIL_LST"
else
EMAIL_LIST="$FA_MAIL_LST $1"
fi
email_hdlr
}

##Set the start time
STARTTIME=`date +&#37;m-%d-%y[%H:%M:%S]`
# call e-mail message template
. $TPE_KSH_DIR/process_email.ksh
# call ksh lib
. $SHARED_KSH_DIR/kshlib.ksh
# For Moving the files with permission change as part of CSA FIX
. $TPE_KSH_DIR/common_lib.ksh
FA_REJECT_LIST=${TPE_MAIL_LST}
export FA_REJECT_LIST
FA_MAIL_LST=`$SHARED_KSH_DIR/pget.awk SECTION=FA_LOAD_PROCESS PARAM=$REGIONPREFIX $TPE_KSH_DIR/TPE.ini`
FA_MAIL_LST=`eval echo $FA_MAIL_LST`
export FA_MAIL_LST
## import user defined libraries
. $TPE_KSH_DIR/chk_file_completeness.ksh
## set log file and list file.
LOGFILE=$TPE_LOG_DIR/LCD`date '+%m%d%Y%H%M%S'`.log
LISTFILE=$TPE_TMP_DIR/filename.lst
##variables for error/info message
PROCESSNAME="Load FA/CCDB file into TPE"
PROCESSDESC="Load incoming cases from First Assist into the Third Party Engine"
FailInd="N"
FileInd="N"
ZeroDetInd="Y"
ERROR_CDE=""
MAX_LOOP_COUNT=3
MIN_LOOP_COUNT=1
echo "Start of Process:$STARTTIME" >$LOGFILE

# Read the files from ccdbcen directory:
cd $TPE_CCDB_IN_DIR
for strFileName in `ls -trp CCDB* 2> /dev/null`
do

if [[ $SkipInd != "Y" ]]
then
echo "File Name: $strFileName<br>" >$LOGFILE.$strFileName
echo "File Name: $strFileName"
while [[ $MIN_LOOP_COUNT -le $MAX_LOOP_COUNT ]] ; do
$ORACLE_HOME/bin/sqlplus -s >>$LOGFILE.$strFileName<< EOF
$TPE_LOGIN_STRING
set serveroutput on;
whenever sqlerror exit 1;
whenever oserror exit 1;
begin
-- Load the file into a work table and process
PKG_TPE_FA_LOAD.LOAD_NEW_CASES('$TPE_STAGE_IN_DIR' ,'$strFileName','$FA_REJECT_LIST');
end;
/
EOF
ERROR_MSG=`cat $LOGFILE.$strFileName`
ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512|ORA-20000)' $ERROR_MSG | sed 's/:.*//'`
echo "THE ERROR CODE IS $ERROR_CDE"
# if [[ "$ERROR_CDE" = "ORA-04068" || "$ERROR_CDE" = "ORA-04061" || "$ERROR_CDE = "ORA-06512" ]] then
RETCODEX=$?
if (( $RETCODEX != 0 )) then
#if [[ "$ERROR_CDE" != "0" ]] then
# if [[ "$ERROR_CDE" = "ORA-04068" || "$ERROR_CDE" = "ORA-04061" || "$ERROR_CDE" = "ORA-06512" || "$ERROR_CDE" = "ORA-20000" ]] then
#if (( "$ERROR_CDE" > "" )) then

continue
else
break
fi
MIN_LOOP_COUNT=`expr $MIN_LOOP_COUNT + 1`
done RETCODE=$?
if (( $RETCODE != 0 )) then
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
STATUS="FAILURE"
ERROR_MSG=`cat $LOGFILE.$strFileName`
echo "End of Process:$ENDTIME" >>$LOGFILE

echo "<br> The loop count is $MIN_LOOP_COUNT and Oracle error is $ERROR_CDE<br>" >>$LOGFILE.$strFileName

email_out $CMS_FTP_MAIL_LST
FailInd="Y"
FILE_DIR=$TPE_BAD_DIR
else
nRecCount=`wc -l $TPE_CCDB_IN_DIR/$strFileName|awk '{print $1}'`
((nRecCount=$nRecCount-2))
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
if [[ $nRecCount -le 200 ]] then
if [[ $nRecCount -gt 0 ]] then
STATUS="WARNING"
echo "<br>WARNING:Load File contains only $nRecCount records<br>" >>$LOGFILE.$strFileName
PROCESS_DETAILS=`cat $LOGFILE.$strFileName|egrep -iv "Start|End|PL/SQL|ORA|ERROR|begin"`
echo "<br> The loop count is $MIN_LOOP_COUNT and Oracle error is $ERROR_CDE<br>" >>$LOGFILE.$strFileName
email_out
else
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
STATUS="WARNING"
ERROR_MSG="$strFileName: File Does Not have any Detail Records"
echo "End of Process:$ENDTIME" >>$LOGFILE.$strFileName
email_out $CMS_FTP_MAIL_LST
fi
else
STATUS="SUCCESS"
PROCESS_DETAILS=`cat $LOGFILE.$strFileName|egrep -iv "Start|End|PL/SQL|ORA|ERROR|begin"`
email_out
fi
fi
fi # SkipInd
# Move the file to wherever the process has determined that it should go ($TPE_BAD_DIR or $TPE_ARCHIVE_IN_DIR).
MoveFileswithPerm $TPE_CCDB_IN_DIR/$strFileName $FILE_DIR $LOGFILE
fi # check file existence
done

# Fail the Process if there are no files from FA to process
if [[ "$FileInd" = "N" ]] then
FailInd="Y"
ZeroDetInd="N"
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
STATUS="FAILURE"
ERROR_MSG="No CCDB Files to process from First Assist."
echo "End of Process:$ENDTIME" >>$LOGFILE
email_out $CMS_FTP_MAIL_LST
fi

# Fail the Process if all the files processed were empty(Zero Detail record Count)
if [[ "$ZeroDetInd" = "Y" ]] then
FailInd="Y"
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
STATUS="FAILURE"
ERROR_MSG="All the Processed CCDB Files from First Assist were empty. "
echo "End of Process:$ENDTIME" >>$LOGFILE
email_out $CMS_FTP_MAIL_LST
fi

if [[ "$FailInd" = "Y" ]] then
exit 1
else
exit 0
fi
echo "End of Process:$ENDTIME" >>$LOGFILE



MY CODE IS IN RED

I'm afraid that's a little too much code for me to deal with and I don't do Oracle these days but I can make the following comments:

• You should indent your code as this makes it easier to follow. If the code is indented but this isn't showing then just highlight your code and use the # icon.
• The error codes you said you wanted to trap are different to those in your code
• What do you do if there's an ORA code that's not in your list?
• The ERROR_CDE variable will contain all the error codes found and not just a single code
• I would change the structure of the program to loop until the error code is empty and then have a trap inside the loop to see if min_loop_count > 3 which will log the error - it would make the code more readable.
• You're still passing the contents of the file to the egrep command rather than the name of the file.
• Put the codes you want to trap in a single string to make it easier to maintain ie
  
  Code:

  TRAP_CODES="(ORA-04068|ORA-04065)"
  ERROR_CDE=`egrep $TRAP_CODES $LOGFILE.$strFileName | sed 's/:.*//'`

The error codes you said you wanted to trap are different to those in your code - I just gave example but the error code i want to trap are ORA-04061 , ORA-04068 and ORA-06512.


What do you do if there's an ORA code that's not in your list? - my intension is to try to load in three iterations else leave it so that i will move the file to bad_files directory, its just a try to call package atleast for three times.These are the only error codes we are expecting.

I would change the structure of the program to loop until the error code is empty and then have a trap inside the loop to see if min_loop_count > 3 which will log the error - my intension is if the error code is found then to call the package again and process the file for atleast three times and as per our experience it will load the data to tables and the package gets validated else it will move to bad files directory

CAN U PLS WALK THROUGH THE CODE AND LET ME KNOW WHY I AM GETTING INFINITE LOOP..
WITH OUTPUT AS LOOKS LIKE SOMETHING TO DO WITH WHILE LOOP INSIDE COULD NOT ABLE TO UNDERSTAND .

THE ERROR CODE IS ORA-06512
ORA-06512
ORA-06512
ORA-06512
.
.
.#!/bin/ksh

function email_out
{
typeset EMAIL_LIST
if [[ -z $1 ]] then
EMAIL_LIST="$FA_MAIL_LST"
else
EMAIL_LIST="$FA_MAIL_LST $1"
fi
email_hdlr
}

ERROR_CDE=""
MAX_LOOP_COUNT=3
MIN_LOOP_COUNT=1
TRAP_CODES="(ORA-04068|ORA-04061|ORA-06512)"
echo "Start of Process:$STARTTIME" >$LOGFILE

# Read the files from ccdbcen directory:
cd $TPE_CCDB_IN_DIR
for strFileName in `ls -trp CCDB* 2> /dev/null`
do


if [[ $SkipInd != "Y" ]]
then
echo "File Name: $strFileName<br>" >$LOGFILE.$strFileName
echo "File Name: $strFileName"
while [[ $MIN_LOOP_COUNT -le $MAX_LOOP_COUNT ]]
do
$ORACLE_HOME/bin/sqlplus -s >>$LOGFILE.$strFileName<< EOF
$TPE_LOGIN_STRING
set serveroutput on;
whenever sqlerror exit 1;
whenever oserror exit 1;
begin
-- Load the file into a work table and process
PKG_TPE_FA_LOAD.LOAD_NEW_CASES('$TPE_STAGE_IN_DIR' ,'$strFileName','$FA_REJECT_LIST');
end;
/
EOF
ERROR_MSG=`cat $LOGFILE.$strFileName`
ERROR_CDE=`egrep $TRAP_CODES $LOGFILE.$strFileName | sed 's/:.*//'`
echo "\nTHE ERROR CODE IS $ERROR_CDE"
if [[ $ERROR_CDE = "" ]]
then
break
else
continue
fi
#MIN_LOOP_COUNT=`expr $MIN_LOOP_COUNT + 1`
((MIN_LOOP_COUNT=MIN_LOOP_COUNT+1))
done
RETCODE=$?
if (( $RETCODE != 0 )) then
ENDTIME=`date +&#37;m-%d-%y[%H:%M:%S]`
STATUS="FAILURE"
ERROR_MSG=`cat $LOGFILE.$strFileName`
echo "End of Process:$ENDTIME" >>$LOGFILE

echo "<br> The loop count is $MIN_LOOP_COUNT and Oracle error is $ERROR_CDE<br>" >>$LOGFILE.$strFileName

email_out $CMS_FTP_MAIL_LST
FailInd="Y"
FILE_DIR=$TPE_BAD_DIR
else
nRecCount=`wc -l $TPE_CCDB_IN_DIR/$strFileName|awk '{print $1}'`
((nRecCount=$nRecCount-2))
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
if [[ $nRecCount -le 200 ]] then
if [[ $nRecCount -gt 0 ]] then
STATUS="WARNING"
echo "<br>WARNING:Load File contains only $nRecCount records<br>" >>$LOGFILE.$strFileName
PROCESS_DETAILS=`cat $LOGFILE.$strFileName|egrep -iv "Start|End|PL/SQL|ORA|ERROR|begin"`
echo "<br> The loop count is $MIN_LOOP_COUNT and Oracle error is $ERROR_CDE<br>" >>$LOGFILE.$strFileName
email_out
else
ENDTIME=`date +%m-%d-%y[%H:%M:%S]`
STATUS="WARNING"
ERROR_MSG="$strFileName: File Does Not have any Detail Records"
echo "End of Process:$ENDTIME" >>$LOGFILE.$strFileName
email_out $CMS_FTP_MAIL_LST
fi
else
STATUS="SUCCESS"
PROCESS_DETAILS=`cat $LOGFILE.$strFileName|egrep -iv "Start|End|PL/SQL|ORA|ERROR|begin"`
email_out
fi
fi
fi # SkipInd
# Move the file to wherever the process has determined that it should go ($TPE_BAD_DIR or $TPE_ARCHIVE_IN_DIR).
MoveFileswithPerm $TPE_CCDB_IN_DIR/$strFileName $FILE_DIR $LOGFILE
fi # check file existence
done


APPRECIATE YOUR HELP IN THIS REGARD