Solved

shell variable in sqlplus

Posted on 2011-03-17
11
1,023 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
 

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

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 91
Need help converting some sql server sql to Oracle sql 9 70
SQL Query 34 79
ITERATE THROUGH DATES 11 9
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now