?
Solved

shell variable in sqlplus

Posted on 2011-03-17
11
Medium Priority
?
1,049 Views
Last Modified: 2012-05-11
My shell script accepts one argument that is the file name.
$sh myscript.sh /usr/tmp/abc.sql

I need to execute the sql file that i am passing to the shell script.

I have the script in a shell variable ..
src=$1
sqlplus username/paswd <<EOF > ${src%.*}.log
@src
EOF

It is erroring at "@src"
ORA-00933: SQL command not properly ended..
 How can I execute the sql script.
0
Comment
Question by:sanpradeep
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Accepted Solution

by:
greisch earned 252 total points
ID: 35155173
Try this :

sqlplus username/paswd @$src > ${src%.*}.log
0
 

Author Comment

by:sanpradeep
ID: 35156184
yes.. that worked for me..

But I have a different issue..
I need to enable the 10053 trace and then execute the input script...
The script will be like..
alter session set events='10053 trace name context forever, level 1';
@src
alter session set events='10053 trace name context off';

One way is to include these alter statements in the script itself and run the script as you mentioned..
but is there a way where I can execute the script in between the statements..

0
 
LVL 2

Expert Comment

by:choukssa
ID: 35156292
try this

sqlplus username/password <<+ENDOFSQL+
alter session set events='10053 trace name context forever, level 1';
@src;
alter session set events='10053 trace name context off';
exit
+ENDOFSQL+

--choukssa
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:sanpradeep
ID: 35156595
Choukssa..

The shell doesnt recognise @src...
src is a shell variable. I am using it inside the sqlplus invoked from shell script..
so @src  and @$src is failing..

However, greisch's solution works..
I wanted to know if i can execute the script in between statements ..
Note: The sqlplus script is invoked from shell script...
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35157205
I would think that @$src would work.  What is the error you get when you run it that way?
0
 
LVL 3

Expert Comment

by:greisch
ID: 35157225
You can create a temporary sql script in your shell script :

echo "alter session set events='10053 trace name context forever, level 1';" > /tmp/tmpscript
cat $src >> /tmp/tmpscript
echo "alter session set events='10053 trace name context off';" >> /tmp/tmpscript

sqlplus username/paswd @/tmp/tmpscript > ${src%.*}.log

rm /tmp/tmpscript

Open in new window

0
 

Author Comment

by:sanpradeep
ID: 35157342
johnsone,

It is erroring with ORA-00933: SQL command not properly ended..

greisch,

Yes, that is one of the solution. But I wanted to execute the script in between statments... by using @src
I wanted to know if it is possible to execute it in between statements...
0
 
LVL 2

Assisted Solution

by:choukssa
choukssa earned 248 total points
ID: 35160328
Here you go.  Complete Shell script that take SID and SRC as input.


#!/usr/bin/ksh

#-- Get the SID
SID=$1

#-- Get src
src=$2


#-- Exit if the SID has not been supplied
if [ -z "${SID}" ]; then
  echo "ERROR: SID not supplied"
  exit 10
fi
echo "SID: ${SID}"

### Set the oracle environment
ORAENV_ASK=NO
export ORAENV_ASK

ORACLE_SID=$1
export ORACLE_SID

. oraenv

sqlplus scott/tiger <<+ENDOFSQL+
alter session set events='10053 trace name context forever, level 1';
@${src};
alter session set events='10053 trace name context off';
exit
+ENDOFSQL+

exit 0

Open in new window


--choukssa
0
 

Author Comment

by:sanpradeep
ID: 35162745
choukssa,
Thanks for the reply...
0
 
LVL 3

Expert Comment

by:greisch
ID: 35163165
I am not very happy. I answered your initial question then you change it and don't even receive an assist.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question