Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2009
    Posts
    56

    Post Unanswered: extracting a pattern in unix script

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by MIKELALA
    can anyone help me in getting the output.
    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

  3. #3
    Join Date
    Nov 2009
    Posts
    56
    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/

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Still not sure what you're after but try this :
    Code:
    egrep '(ORA-04068|ORA-04065)' LCDFile.log

  5. #5
    Join Date
    Nov 2009
    Posts
    56
    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 ..

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    So just remove everything after the :
    Code:
    egrep '(ORA-04068|ORA-04065)' LCDFile.log | sed 's/:.*//'
    or if you're confident these records will exist in your file you could use:
    Code:
    echo "ORA-04068"
    echo "ORA-04065"

  7. #7
    Join Date
    Nov 2009
    Posts
    56
    Got it .. THANKS YOU SO MUCH.

    Thanks for your time , I really appreciate it.

  8. #8
    Join Date
    Nov 2009
    Posts
    56
    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/

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    ERROR_MSG=`cat $LOGFILE.$strFileName`
    ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512)' $ERROR_MSG | sed 's/:.*//'`
    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:
    Code:
    egrep '(ORA-04068|ORA-04061|ORA-06512)' I love cheese
    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?

    Code:
    ERROR_CDE=`egrep '(ORA-04068|ORA-04061|ORA-06512)' $LOGFILE.$strFileName | sed 's/:.*//'`
    if (( "$ERROR_CDE" > "" )) then
       break
    fi
    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

  10. #10
    Join Date
    Nov 2009
    Posts
    56
    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.
    Last edited by MIKELALA; 11-14-09 at 16:52.

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  12. #12
    Join Date
    Nov 2009
    Posts
    56
    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

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by MIKELALA
    Let me tell you the complet story..
    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/:.*//'`

  14. #14
    Join Date
    Nov 2009
    Posts
    56
    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
    Last edited by MIKELALA; 11-14-09 at 19:47.

  15. #15
    Join Date
    Nov 2009
    Posts
    56
    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
    Last edited by MIKELALA; 11-15-09 at 00:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •