Programming

date_gen VARCHAR2(10) := '$DATEGEN';

DATE_FORMAT="YYYYMMDD"
DATE_FORMAT=`echo "'"$DATE_FORMAT"'"`

EMPNAME=`echo "'"$EMPNAME"'"`
EMPID=`echo "'"$EMPID"'"`
DATEGEN=`echo "'"$DATEGEN"'"`

INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES($EMPNAME,$EMPID,TO_DATE($DATEGEN,$DATEFORMAT));

$
$
$ # show the content of the shell script
$
$ cat -n tst.sh
     1  #!/usr/bin/bash
     2  EMPNAME=$EMP_NAME;
     3  EMPID=$EMP_ID;
     4  DATEGEN=$DATE_GEN;
     5  DATEFORMAT=$DATE_FORMAT;
     6  sqlplus -s test/test <<EOF
     7  whenever sqlerror exit 1
     8  whenever oserror exit 2
     9  INSERT INTO TBL1(EMPNAME,EMPID,EMPBDATE) VALUES('$EMPNAME',$EMPID,TO_DATE('$DATEGEN','$DATEFORMAT'));
    10  prompt One row inserted into table TBL1...
    11  EOF
    12  echo "Return value = $?"
$
$ # set the values of the shell variables and export them
$
$ EMP_NAME="ABC XYZ"     ; export EMP_NAME
$ EMP_ID=1001            ; export EMP_ID
$ DATE_GEN="01012010"    ; export DATE_GEN
$ DATE_FORMAT="MMDDYYYY" ; export DATE_FORMAT
$
$ # now run the shell script
$
$ ./tst.sh
 
1 row created.
 
One row inserted into table TBL1...
Return value = 0
$
$ # verify that the record was inserted
$
$ echo "select * from tbl1;" | sqlplus -s test/test
 
EMPNAME                             EMPID EMPBDATE
------------------------------ ---------- ---------
ABC XYZ                              1001 01-JAN-10
 
1 row selected.
 
$
$
$