How to execute a query stored in a variable in SQL*Plus

Question

Is there any way to pass a query to SQL*Plus through a variable?

I'm aware SQL*Plus has the capability to execute a file like:

sqlplus user/pass@db @filename

Within a kornshell script I'm trying to do:

query="select * from dual;"
sqlplus user/pass@db $query

atokpas · Accepted Answer · 2017-04-26 08:01:55Z

2

There might have solution to do that BUT I can achieve the same goal using the following method.

[oracle@myserver Desktop]$ $ORACLE_HOME/bin/sqlplus -s jay/passsword@db <<! 
select * from dual; 
exit 
!


D
-
X

Update, you can store the returned result in a variable as shown below.

query="select * from dual;"
var=$($ORACLE_HOME/bin/sqlplus -s jay/pass@db <<!
set pages 0
set head off
set feed off
$query
!
);

edited Apr 26, 2017 at 8:01

answered Apr 26, 2017 at 7:27

atokpas

3,3571 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tom Nulty Over a year ago

This is definitely a good alternative! Thank you. Strangely though, I tried encasing the variable within the exclamation marks and it doesn't work.

Tom Nulty Over a year ago

Although this isn't exactly what I'm looking for. This allows me to execute an SQL query from within the same script which is what I needed. If no more relevant answers come along in 30mins I will accept this answer. Thanks again :)

atokpas Over a year ago

As I have updated, you can store your query in a variable and call inside the SQL*PLUS command section.

Tom Nulty Over a year ago

It works! Thanks a lot. I think it didn't work for me the first time because I was putting ; exit within the variable also. That was the only difference.

Collectives™ on Stack Overflow

How to execute a query stored in a variable in SQL*Plus

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related