Invoke SQL script from Shell script with Parameters

Respected Readers,

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
employee_email=badal@somewhere.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.

Thanks,
Your’s Truly

Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Unix and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s