Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

bash script & sql query

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers bash script & sql query
# 1  
Old 09-07-2010
bash script & sql query
Hi Guys,

I would like with this script

Code:
"DATA=`ora123 su -c 'echo "SET HEADING OFF;\n SET FEED OFF;\n select USER NAME, ACCOUNT_STATUS from dba_users;\n exit" | sqlplus / as sysdba -s /' `
echo $DATA >> $DAT"

make a sql query, but the statement

Code:
"select USER NAME, ACCOUNT_STATUS from dba_users;"

does not work. :-(

What am I doing wrong?

Thanks for your help
ixibits
Last edited by Scott; 09-07-2010 at 04:09 PM.. Reason: Please use code tags
# 2  
Old 09-07-2010
Hi.

I'm not really sure what you are trying to do in the first part of your statement:
Code:
DATA=`ora123 su -c

If I run the SQL (replacing echo with the more universally-format-friendly printf (and removing the exit;, which you don't need in this case)), I get the result I would expect:

Code:
$ printf "SET PAGES 0\nSET HEADING OFF\nSET FEED OFF\n select USER NAME, ACCOUNT_STATUS from dba_users;" | sqlplus -s / as sysdba
SYS			       OPEN
SYS			       OPEN
SYS			       OPEN
SYS			       EXPIRED & LOCKED
SYS			       EXPIRED & LOCKED
...

# 3  
Old 09-07-2010
i must switch the user for this action.
# 4  
Old 09-07-2010
Code:
$ DATE=$(su oracle -c 'printf "SET PAGES 0\nset HEADING OFF\n SET FEED OFF\n select USER NAME, ACCOUNT_STATUS from dba_users;" | sqlplus -s / as sysdba')

$ echo "$DATE"
SYS			       OPEN
SYS			       OPEN
SYS			       OPEN
SYS			       EXPIRED & LOCKED
SYS			       EXPIRED & LOCKED
SYS			       EXPIRED & LOCKED
SYS			       EXPIRED & LOCKED
SYS			       EXPIRED & LOCKED

You need to enter the Oracle password (have sudo, ssh, or (yuk!) rsh set up to avoid that), and make sure you set your Oracle environment before running it.
# 5  
Old 09-07-2010
the test with printf is the same....


.......web server (URL) or the local file system (filename.ext) with specified parameters that will be assigned to substitution variables in the script. When SQL*Plus starts, and after CONNECT commands, the site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile (e.g. login.sql in the working directory) are run. The files may contain SQL*Plus commands. Refer to the SQL*Plus User's Guide and Reference for more information.
# 6  
Old 09-07-2010
I get much the same "junk" with the original command you posted (between the ` ... `).

But I didn't use the original command you posted.

Please copy and paste (in [code][/code] tags) the exact command you used.
# 7  
Old 09-07-2010
ok, that is it.

many thanks, scottn

---------- Post updated at 09:29 PM ---------- Previous update was at 09:23 PM ----------

echo "SET HEADING OFF;\n SET FEED OFF;\n exit\n"|Sqlplus -s /
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

SQL query output convert to HTML & send as email body

Hi , I have a sql query in the unix script ,whose output is shown below.I want to convert this output to HTML table format & send email from unix with this table as email body. p_id src_system amount 1 A 100 2 B 200 3 C ... (3 Replies)
Discussion started by: jagadeeshn04
3 Replies

2. UNIX for Dummies Questions & Answers

Script to run sql query.

Please read How To Ask Questions The Smart Way (1 Reply)
Discussion started by: balu_279013
1 Replies

3. Shell Programming and Scripting

SQL query within an awk script !!

Hello Experts, This sounds crazy, but one of my requirements is that if ID in 5th column in my input file (: separated) is not present in the oracle database table (say t_id) then this record should be skipped. I am using awk for all other requirements. So I was thinking if this requirement... (4 Replies)
Discussion started by: juzz4fun
4 Replies

4. Red Hat

Sql query through shell script

hey , i am using this code to store value of a sql query and and then use it in other query but after some time , but it is not working. please help #!/bin/bash val_1=$( sqlplus -s rte/rted2@rel76d2 << EOF setting heading off select max(stat_id) from cvt_stats; exit EOF ) nohup... (5 Replies)
Discussion started by: ramsavi
5 Replies

5. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

6. Shell Programming and Scripting

Oracle SQL Query & connect?

Hi I'm looking to query a table on a database and then iterate over the results in a loop. I believe this is the last part of my script that I need (after finding out threads for passing variables to other scripts and calling functions in other scripts). I've searched the forums but the best... (8 Replies)
Discussion started by: Dird
8 Replies

7. Shell Programming and Scripting

query sql using shell script

query sql using shell script, is it possible? my friend told me to do a file.sql and link to my shell script, but can i query sql using shell script? thanks in advance! (2 Replies)
Discussion started by: kingpeejay
2 Replies

8. Shell Programming and Scripting

Script executing sql query

Hello, I have a sh script excuting a sql query through sqlplus. I am having trouble making my date equal to the date of the server time in the sql script. How can i call the server date from my query? Thanks (2 Replies)
Discussion started by: kingluke
2 Replies

9. Shell Programming and Scripting

script variable within a sql query

I created a script to read a csv file with four columns. The script also saved values of each col in a arry. In the script, i connected to db try to run a query to pull out data baisc on the values from the csv file. select Num from tableName where Sec_Num in ('${isin}') /*isin is an arry... (1 Reply)
Discussion started by: Sherry_Run
1 Replies

10. Shell Programming and Scripting

& in SQL query

I have a script that looks for all jobs that contain a particular calendar. Some of the calendars have '&' in them and sql freaks out when it encounters that.. is there a way around this? I have tried: select job_name from job where run_calendar='1&15dom' select job_name from job... (3 Replies)
Discussion started by: Lindarella
3 Replies
Login or Register to Ask a Question