Often we have to write shell scripts that does some work (capturing daily data feed for eg) and then invoke a sql script (to persist captured data). You may or may not require to send parameters. Below provides an example to do the same with also passing parameters.
Shell script example:
#!/usr/bin/ksh # declare ORACLE_HOME, ORACLE_SID and PATH variables and export all of them # define variables to be passed to sql script. These variables can obv also be a result of some complex logic in your script employee_id=101 email@example.com # let the sql script name be insert_employee_email.sql. Invoke this file from shell script sqlplus -s /NOLOG < temp1log.txt connect oracle_schema_username/oracle_schema_password @<path-of-sql-script>/insert_employee_email.sql $employee_id $employee_email exit EOF
insert_employee_email.sql can simply be a sequence of sql statements.
delete from employee_email a where a.employee_id=&1 and a.employee_email='&2'; insert into employee_email (employee_id, employee_email) values (&1, '&2'); commit;
Note the & for retrieving values in Oracle and ‘ around 2nd parameter for persisting a String value.