[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1064
  • Last Modified:

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.
0
sanpradeep
Asked:
sanpradeep
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
greischCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
choukssaCommented:
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now