Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> how to pass values from bash shell script to sqlplus
ram123
post May 3 2012, 11:46 AM
Post #1


Newbie
*

Group: Members
Posts: 9
Joined: 3-May 12
Member No.: 47,162



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.
Go to the top of the page
 
+Quote Post
burleson
post May 3 2012, 03:57 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,634
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 31st October 2014 - 09:11 AM