how to pass values from bash shell script to sqlplus Options

[ram123]

Hi Experts,

i am new to UNIX shell scripting,i want to run parallel queries of 16 sqlplus jobs through shell script.i don't know to pass values dynamically from shell script to sqlplus.

here i am hard coded the values but i want to pass the values dynamically. and how i will know each job is running successfully.if any job is fail the it will exit from script.

my shell script is :


sqlplus -s username/password @script_file.sql 1001 1004 &

sqlplus -s username/password @script_file.sql 1006 1010 &

sqlplus -s username/password @script_file.sql 1011 1015 &

sqlplus -s username/password @script_file.sql 1016 1025 &


my sql file is:


CLEAR BUFFER
SET SQLBLANKLINES ON
SET ECHO OFF
SET FEEDBACK OFF
SET FLUSH OFF
SET HEADING OFF
SET LINESIZE 32767
SET NEWPAGE NONE
SET PAUSE OFF
SET NULL NULL
SET TRIMSPOOL ON
SET RECSEP OFF
SET CONCAT '+'
SET WRAP OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDDHH24MISS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYYMMDDHH24MISS';

SPOOL service_ids_&&1+_&&2.dat

SELECT ACCOUNT_ID ||chr(35)||
SUBSCRIPTION_SERVICE_ID ||chr(35)||
RATE_PLAN_ID ||chr(35)||
EFFECTIVE_FROM_DATE ||chr(35)||
EFFECTIVE_TILL_DATE ||chr(35)||
CREATED_DATE ||chr(35)||
CREATED_USER_ID ||chr(35)||
MODIFIED_DATE ||chr(35)||
MODIFIED_USER_ID ||chr(35)||
DELETION_STATUS ||chr(35)||
RATEPLAN_SUBSCRIPTION_ID ||chr(35)||
BILLING_REGION_ID ||chr(35)||
PACKAGE_SUBSCRIPTION_ID ||chr(35)||
NO_OF_INSTANCES ||chr(35)||
ACTIVATION_DATE ||chr(35)||
CESSATION_DATE ||chr(35)||
SERVICE_ID ||chr(35)||
EXIT_PLCY_FLAG ||chr(35)||
NULL ||chr(35)||
NULL ||chr(35)||
NULL ||chr(35)||
NULL ||chr(35)||
NULL ||chr(35)||
NULL

FROM V_BIL_SUBSCRIPTION_RATEPLN_DTL DTL WHERE DTL.SERVICE_ID BETWEEN &&1 AND &&2;

SPOOL OFF

EXIT

Any help would be highly appreciated.

[burleson]

Hi Ram,

>> how to pass values from bash shell script to sqlplus?

Please read:

http://www.dba-oracle.com/t_script_unix_sql_plus.htm

http://www.dba-oracle.com/t_passing_sqlplu...hell_script.htm

There is a great book "Oracle Shell Scripting" with lots of working examples in the code download:

http://www.rampant-books.com/book_0701_shell_scripting.htm