• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1572
  • Last Modified:

Execute Oracle Query via unix shell sql plus, query is in file

Hello experts
I want to execute a sql query from a shell script. I have the commands in my .sh script:
sqlplus user/pw@db
connnect user/ow@db
spool myres
->This all works well, now I want to execute a sql command which is in a file:
@myquery.sql
->I get SP2-0734: unknown command beginning "@myquery ..." rest of the line ignored

How can I in my script exectute a SQL query which is in a file?
0
mruff
Asked:
mruff
3 Solutions
 
Wasim Akram ShaikCommented:
You need to show us little bit more to assist you further.. may be the code which you are trying to execute in myquery.sql has blank lines.. this is what i could think of with the error associated with SP2-0734

try adding this line in the very beginning of the file myquery.sql

set sqlblanklines on

Open in new window

and try to re-execute the code in similar fashion you have posted here...
if you still get an error, post the code of the file myquery.sql
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Just put the query as a parameter on the sql prompt (add paths if necessary):
sqlplus -s ${user}/${pw}@${db} @myquery.sql >> ${output}

Open in new window

The above will run the query and send it's output to ${output}, just define a few variables for user, pw, db and output.

You don't need the connect statement in your sql file, you are already connecting using sqlplus.
0
 
slightwv (䄆 Netminder) Commented:
I prefer to NOT place the username and password on the command line.  It is a huge security issue.

Try a HERE doc:

sqlplus << EOF
connnect user/ow@db
spool myres
@myquery.sql
exit
spool off
EOF
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now