String Search And Data Load
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: String Search And Data Load

  1. #1
    Join Date
    Nov 2000
    Posts
    34

    Arrow String Search And Data Load

    Friends,

    I have prepared following unix shell scripts to search "ORACLE" word from header and trailor row, if string found in both header and trailor only then process should go ahead to load the data in the oracle table other wise it should exit.

    I am facing problem in search of a string from header and trailor row.

    #!/usr/bin/ksh

    PATH=/usr/local/opt/oracle/product/ostl550/bin:/usr/bin:/usr/sbin:/usr/ucb:/usr/lbin:/usr/bin/X11:local/bin:/CC/bin:/local/cob31bin:/local/spf

    ORACLE_HOME=/usr/local/opt/oracle/product/ostl550

    ORACLE_SID=ostl550

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

    export ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

    CUR_TIME=`date +%m%d%y_%R`

    DATA_DIR=/appl/focsoc/jp3979/sotsofe/data
    CTL_DIR=/appl/focsoc/jp3979/sotsofe/scripts
    LOG_DIR=/appl/focsoc/jp3979/sotsofe/log
    BAD_DIR=/appl/focsoc/jp3979/sotsofe/bad
    DCD_DIR=/appl/focsoc/jp3979/sotsofe/dcd
    ARCHIVE_DIR=/appl/focsoc/jp3979/sotsofe/archive_data

    LOG_FILE=$LOG_DIR/$CUR_TIME.log
    >$LOG_FILE
    chmod 755 $LOG_FILE

    bad_file=$BAD_DIR/$CUR_TIME.bad
    discard_file=$DCD_DIR/$CUR_TIME.dcd
    data_file=$DATA_DIR/sotsofe.txt

    DB_USER=west271dly
    DB_PASSWORD=west

    # If data file is not received exit.

    DATA_RECVD=0

    if [ -f $data_file ]
    then
    DATA_RECVD=1
    fi

    if [ DATA_RECVD -eq 0 ]
    then
    exit
    fi

    # If the file is present continue loading into the table.


    if [ -f $data_file ]

    then

    header_record=`head -1 $data_file | grep "ORACLE"`

    mail -s "sotsofe.txt data file has header record " jp3979@sbc.com

    else

    exit

    fi

    if [ -f $data_file ]

    then

    trailor_record=`tail -1 $data_file | grep "ORACLE"`

    mail -s "sotsofe.txt data file has trailer records" jp3979@sbc.com

    else

    exit

    fi

    done

    if [ -f $data_file ]

    then
    echo "Loading " $data_file >> $LOG_FILE
    sqlldr userid=$DB_USER/$DB_PASSWORD@$ORACLE_SID control=$CTL_DIR/sotsofe.ctl log=$LOG_FILE bad=$bad_file discard=$discard_file discardmax=2 errors=2
    echo "Successfully Loaded " $data_file >> $LOG_FILE
    mv $data_file $ARCHIVE_DIR/sotsofe.txt$CUR_TIME
    echo "sotsofe.txt moved to archive directory " >> $LOG_FILE

    fi

    echo "Ending Process At " $CUR_TIME >> $LOG_FILE

    mail -s "sotsofe.txt Data Load Job Finish" jp3979@sbc.com < $LOG_FILE



    Pl Guide.
    Jayesh

  2. #2
    Join Date
    Jan 2002
    Posts
    148
    guess some case sensitive problem ?

  3. #3
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    # If the file is present continue loading into the table.


    if [ -f $data_file ]

    then

    header_record=`head -1 $data_file | grep "ORACLE"`

    mail -s "sotsofe.txt data file has header record " jp3979@sbc.com

    else

    exit

    fi
    In this code fragment you capture the first line of your file and you put the line into the variable header_record if it contains the character string ORACLE at any point. But nowhere else in your code do you look at ${header_record} to see if it actually contains anything.

    Similarly with the last line in the file - you capture it if it contains the text ORACLE but you never test the variable afterwards to see if it has anything in it.

    I am an awk enthusiast and I think it would be hard to test the first and last records directly in the shell. My Solaris system disagrees with my standard UNIX book on the awk index function. I have shown it as it works in Solaris. I would suggest...

    header_test=`head -1 ${data_file}|awk '{t1=index($0,"ORACLE");if(t1>0){print "OK"} else {print "FAIL"}}'`
    trailer_test=`tail -1 ${data_file}|awk '{t1=index($0,"ORACLE");if(t1>0){print "OK"} else {print "FAIL"}}'`
    if [ "${header_test}" = "FAIL" -o "${trailer_test}" = "FAIL" ]
    then
    echo header or trailer line does not contain ORACLE.
    exit
    fi

    If you want to make your comments more helpful to the user you can separate the tests for the header and trailer and report which failed.

    I have another problem with the style of this script. It is not the source of your problem but it shows an uncertainty and vagueness about what you are doing.

    # If data file is not received exit.

    DATA_RECVD=0

    if [ -f $data_file ]
    then
    DATA_RECVD=1
    fi

    if [ DATA_RECVD -eq 0 ]
    then
    exit
    fi
    As far as this code fragment is concerned what is the point of the DATA_RECVD? It is never referenced again and is unnecessary for this task. I would suggest:

    1) reverse the condition and use it to exit directly
    2) add a message to the user explaining the premature exit
    3) test if the file is readable rather than whether it exists.

    So, for example,

    if [ ! -r ${data_file} ]
    then
    echo File ${data_file} does not exist or is unreadable.
    exit
    fi

    There is no further need after this point to test if the data file is there or not (which you do THREE more times in your script). If the data file is not there (or in my case, is not readable) then you will have already exited the script. If you are still in the script you have a data file. No need to keep checking.

  4. #4
    Join Date
    Nov 2000
    Posts
    34
    Thanks For Your Guidance. I found from you for what I was looking for.

Posting Permissions

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


Click Here to Expand Forum to Full Width