Programming

#!/bin/sh
usage="`basename $0` <file_name>"

Insert_Record()
{
set -x
INS_REC=$(sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
declare
v_cnt NUMBER:=0;
begin
select count (*) into v_cnt from TBL1;
if v_cnt >= 0 then

INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES('$EMPNAME','$EMPID',TO_DATE('$EMPBDATE','YYYY-MM-DD'));
end if;
end;
/
EOF)

TBL_ERR=$(echo "$INS_REC" | grep -e "SP2-" -e "ORA-")
echo $TBL_ERR
}

process_input_record ()
{
echo "Input file path is $INPUT_FILE_FOLDER"
if [ "$(ls -A $INPUT_FILE_FOLDER)" ]; then
#echo "Take action $INPUT_FILE_FOLDER is not Empty"
for file in "$INPUT_FILE_FOLDER/"ABC_MN_XYZ*.CSV
do	
# Did lots of things for input file data

# Check each line 
line=`head -1 $file`
nrfields=$(IFS=\;; set -- $line; echo $#)
if [ $nrfields -ne 4 ]; then
echo "Incorrect first record fields"
exit
else			
DATE=`echo $line | cut -d ";" -f 2`						
DATEGEN==`echo $DATE | cut -c1-8`			
while read line
do
fields=$(IFS=\;; set -- $line ; echo $#)
Status=`echo $line | cut -d ";" -f 7`
if [ $fields -eq 12 ]; then
if [ "$Status" -eq 1 ]; then
EMPNAME=`echo $line | cut -d ";" -f 2`
EMPID=`echo $line | cut -d ";" -f 4`
Insert_Record

sed "/$line/d" $file > tmp
mv tmp $file
fi
else
echo "Incorrect DETAIL record fields"
fi
done < $file
fi
else
echo "$INPUT_FILE_FOLDER is Empty"
fi
done
}

#----------------------------------------------------------------------------
# Main
#----------------------------------------------------------------------------

if [ $# -eq 1 ]; then

if [ -f $1 ]; then
. $1  
process_input_record

else 
echo "$ERROR_CODE : file $1 is not available"
exit 1   
fi 
else
echo "usage : $usage"
fi

select count (*) into v_cnt from TBL1;
if v_cnt >= 0 then

DATE=`echo $line | cut -d ";" -f 2`
echo "Date is : $DATE"			
DATEGEN==`echo $DATE | cut -c1-8`
echo "Generated Date is : $DATEGEN"

Date is : 20100913115432
Generated Date is : =20100913

Date is : 20100913115432
Migration Date is : 20100913
Header record is correct
Incorrect DETAIL record fields
Incorrect DETAIL record fields
+ + sqlplus bios/bios@BIOS
+ 0<<
set head off
set feed off
set serveroutput on

INSERT INTO TBL1 ( EMPNAME,EMPID,EMPBDATE)
VALUES($EMPNAME,$EMPID,TO_DATE($EMPBDATE,'YYYY-MM-DD'));
/
INS_REC=
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 20 08:33:00 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9                                 TO_DATE (20100913,"YYYY-MM-DD")
                                                    *
ERROR at line 8:
ORA-00984: column not allowed here


SQL>                            TO_DATE (20100913,"YYYY-MM-DD")
                                                    *
ERROR at line 8:
ORA-00984: column not allowed here


SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
+ + grep -e SP2- -e ORA-
+ echo
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Oct 20 08:33:00 2010

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

$(...)

`...`

tee /tmp/mySQL <<! | sqlplus . . .

INS_REC=$(tee /tmp/mySQL <<! |sqlplus $USERID/$PASSWORD@$DATABASE << EOF 
set head off 
set feed off 
set serveroutput on
INSERT INTO....
/
EOF)