Search This Blog

Saturday, March 29, 2008

Calling SqlPlus From Unix Shell Script

In this post we will discuss how we can call sqlplus from within a unix shell script. Here is a post on how to read concurrent program parameters from shell script and this is a continuation to that.
The syntax to call a SQLPLUS from shell script is


sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
-- variable declarations
....
....
begin
--sql statements..
....
....
end;
/
exit
EOF

In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign. Any dbms_output.put_line statements are printed in the log file if script is run through concurrent program.

12 Comments:

Ash said...

Suresh,

When I pass a value through the concurrent manager to my shell script I capture using the following statement

`echo $parameters|cut -d" " -f9|cut -d"=" -f2.

But the value I capture is always surrounded by double quotes. How can I capture without double quotes?

Thanks

Suresh Vaishya said...

If you want to read parameters from concurrent manager then $1 is the variable that stores all the information.
Refer to my post
http://sureshvaishya.blogspot.com/2008/03/read-concurrent-parameters-in-unix.html

Let me know if you still have any questions.

swati said...

Why do we use this '-s' option with sqlplus?..

Suresh Vaishya said...

-s is silent, basically it suppreses the oracle banner and runs it in silent mode.

gsrabinandan said...

Hello Suresh, thanks for your variable tips. How can i store the output of the sqlplus query into a variable in the Shell script?

I need to do something like countdownload='sqlplus .....$login...

and use $countdownload later in the shell script.

Suresh Vaishya said...

Please refer to my post
Store SQLPLUS output to a variable in Unix Shell Script

Anonymous said...

Hi Suresh...is it possible to access a shell array variable inside sqlplus body?

Suresh Vaishya said...

Check http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:150612348067
May be its useful

The other option would be to use Java wrapper which I am not aware of much.

Thanks,
Suresh

Suresh Vaishya said...

Another post from AskTom using Java

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

Regards,
Suresh

Sajesh P said...

Hello Suresh ,

Bolg is gr8 , good info ...

I was trying to invoke sqlplus session from Shell .

Code
-------------------
login=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )`

V_START=`sqlplus -s $login <<!
set echo off
set feedback off
set heading off
set pagesize 0
select 'START' from dual;
!`
echo $V_START

# Execute the query
sqlplus -s $login <<!
set feedback off
set serveroutput on
declare
begin
dbms_output.put_line('test');
exception when others then
dbms_output.put_line('test1');
end;
exit
!
echo "COMPLETE"
===========

The log prints "Start" but the dbms statement and echo "complete" does not print on the log file

I am not sure what I am missing .

Thanx
Sajes

siva said...

I am calling procedure from one Unix with different set of parameters (like threads - once thread for each set of parameters). How to know, how many threads are running each time - I want to limit the no of threads ?

sap testing said...

This is a very popular site among the beginners as well as professional technicals.The best part is it is being updated very regularly to give latest data.

Copyright (c) All rights reserved. Presented by Suresh Vaishya