shell variable in sqlplus

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.
sanpradeepAsked:
Who is Participating?
 
greischConnect With a Mentor Commented:
Try this :

sqlplus username/paswd @$src > ${src%.*}.log
0
 
sanpradeepAuthor Commented:
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
 
choukssaCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sanpradeepAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
I would think that @$src would work.  What is the error you get when you run it that way?
0
 
greischCommented:
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
 
sanpradeepAuthor Commented:
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
 
choukssaConnect With a Mentor Commented:
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
 
sanpradeepAuthor Commented:
choukssa,
Thanks for the reply...
0
 
greischCommented:
I am not very happy. I answered your initial question then you change it and don't even receive an assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.