?
Solved

Run sql statement in Unix script

Posted on 2007-04-03
9
Medium Priority
?
2,938 Views
Last Modified: 2013-12-21
I am editing a Unix script that logs into the database and executes a sql script in a certain directory. Rather than execute this sql script I want to be able to run sql statement within the unix script. How do I do this.
This is the line in the Unix script:
su - oracle -c "export ORACLE_SID=${db}; sqlplus '/as sysdba' @${PWD}/audit_loc.sql" > ${PWD}/audit_loc.txt

The script is audit_loc.sql

I would like to execute the sql statement within Unix rather than using this: @${PWD}/audit_loc.sql

The contents of the script is the following:
set heading off;
show parameter audit_file_dest;
exit;

0
Comment
Question by:sikyala
[X]
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
  • 6
  • 3
9 Comments
 
LVL 7

Expert Comment

by:nixfreak
ID: 18845021
Not sure but try:
/bin/echo "set heading off;\nshow parameter audit_file_dest;\nexit;" | su - oracle -c "export ORACLE_SID=${db}; sqlplus '/as sysdba' @/dev/stdin" > ${PWD}/audit_loc.txt
0
 
LVL 7

Expert Comment

by:nixfreak
ID: 18845044
Small correction, please try:

/bin/echo "set heading off;\nshow parameter audit_file_dest;\nexit;\n" | su - oracle -c "export ORACLE_SID=${db}; sqlplus '/as sysdba' @/dev/stdin" > ${PWD}/audit_loc.txt
0
 

Author Comment

by:sikyala
ID: 18846363
I tried it and it didn't work. what does @/dev/stdin do?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Expert Comment

by:nixfreak
ID: 18847353
Hi sikyala,

Sorry, please try this modification:

su - oracle -c "export ORACLE_SID=${db}; /bin/echo "set heading off;\nshow parameter audit_file_dest;\nexit;\n" | sqlplus '/as sysdba' @/dev/stdin" > ${PWD}/audit_loc.txt
0
 
LVL 7

Expert Comment

by:nixfreak
ID: 18847406
/dev/stdin can be used with commands that are not designed to take standard input (stdin).

If it still doesn't work perhaps you can also try replacing the single quotes with double quotes surrounding "/as sysdba".
0
 
LVL 7

Expert Comment

by:nixfreak
ID: 18847433
Also I realized that "\n" is not required after exit.
0
 

Author Comment

by:sikyala
ID: 18848132
I tried and got the following:

./audit.bash: nshow: command not found
./audit.bash: nexit | sqlplus '/as sysdba' @/dev/stdin: No such file or directory
0
 
LVL 7

Accepted Solution

by:
nixfreak earned 2000 total points
ID: 18848730
Ok,
I think the double quotes were causing the problem. So, try:

su - oracle -c "export ORACLE_SID=${db}; /bin/echo 'set heading off;\nshow parameter audit_file_dest;\nexit;' | sqlplus '/as sysdba' @/dev/stdin" > ${PWD}/audit_loc.txt       
0
 

Author Comment

by:sikyala
ID: 18851294
It worked thanks!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

771 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