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
Solved

shell variable in sqlplus

Posted on 2011-03-17
11
1,031 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 3

Accepted Solution

by:
greisch earned 63 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 34

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 62 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 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