Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Running a select script through UNIX and sending output to file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Running a select script through UNIX and sending output to file
# 1  
Old 04-13-2011
Running a select script through UNIX and sending output to file
Hi,

(Oracle, AIX)

I have googled this and searched this forum, however I haven't had much luck with an answer and have tried several different things.

Basically I have a SQL select statement which generates a whole load of UPDATE statements, I want to run the select statement via sqlplus in UNIX and to output the results (i.e. the update statements) to a file. I would then like the script to do some other stuff, but not worried about that atm.

What I have so far...

sqlplus user/password <<selectupdate
@<scriptname.sql>;
selectupdate

Any ideas?

Thanks in advance Smilie


# 2  
Old 04-13-2011
You can use spool.
Code:
sqlplus user/password <<selectupdate
spool /your/filename
@<scriptname.sql>;
spool off
selectupdate

# 3  
Old 04-13-2011
Quote:
Originally Posted by pravin27
You can use spool.
Quote:
Originally Posted by pravin27
Code:
sqlplus user/password <<selectupdate

Code:
 spool /your/filename
 @<scriptname.sql>;
 spool off
 selectupdate

Thanks for the suggestion. Here's an update of my code:
Code:
sqlplus user/password@SID <<selectupdate >  selectupdate.txt
  set heading  off
  spool  selectupdateX.txt
   @<select_script>.sql;
spool  off
 selectupdate

With this, I don't get the update statements from the select script, just some useless information (well, useless to me). I was hoping that either selectupdate.txt or selectupdateX.txt would produce a file like...
update table set column = something
udpate table2 set column = something
The select statement I created does this when running via sqlDeveloper. Hope that makes sense? Smilie
Any help is appreciated! In the meantime I'll keep playing with the script.
Cheers,
Gary

---------- Post updated at 06:05 AM ---------- Previous update was at 06:03 AM ----------

Ach, sorry for the bad formatting there.

---------- Post updated at 10:02 AM ---------- Previous update was at 06:05 AM ----------

Update -

So my latest version...

Code:
X= 'sqlplus -r u/p@sid  <<selectupdate
   spool  selectupdateX.txt
    @<script>.sql;
 selectupdate'
 
  echo $X >  selectupdateY.txt

And this produces the following error...

Code:
-bash: sqlplus -r u/p@sid  <<selectupdate
 spool  selectupdateX.txt
    @scrambler_SELECT_UPDATE_V0.4.sql;
 selectupdate: No such file or  directory

Any idea why this error is caused? Smilie

Cheers
# 4  
Old 04-13-2011
I suspect it means what it says; no such file or directory.
# 5  
Old 04-13-2011
Quote:
Originally Posted by Corona688
I suspect it means what it says; no such file or directory.
Good point, I didn't think of that.................

I don't understand why it's gerating that error; I don't believe that I'm ever setting "selectupdate" as a file or directory.
# 6  
Old 04-13-2011
Try the following :
Code:
sqlplus /nolog <<EOF
conn user/pass@$ORACLE_SID
spool result.txt
@sqltorun.sql
disconnect
EOF

Cannot confirm since i'm @ home, but that's how i do it.
# 7  
Old 04-13-2011
Something you're doing but didn't post is trying to run that variable literally.

There's no point putting it in a variable, to put it in a file, to run the file. Just put the code into the script as it was given to you and it should work.

Code:
sqlplus -r u/p@sid  <<selectupdate
spool selectupdateX.txt
@<script>.sql;
# Note selectupdate must be at the BEGINNING of the line
selectupdate
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Select command going to infinite loop after running the script

cd /opt/et/WAS/apps/8.0 find . -name "HostIntegration.properties" -o -name "HostSocket.properties" -o -name "environment.properties" 2> /dev/null | awk -F '' '{print $4}'|awk '!x++' | cat>/home/cbadmin/file1.txt cd /home/cbadmin/ PS3='Please enter a number from list of applications==>:' select... (3 Replies)
Discussion started by: bhas85
3 Replies

2. Shell Programming and Scripting

How to print the output of a select query using shell script?

HI, I want to connect to database and fetch the count from a table. The sql query is as below : select count(*) from table_test where test_column='read'; How can I print the output of this statement using shell script. Thanks in advance. (4 Replies)
Discussion started by: confused_info
4 Replies

3. Shell Programming and Scripting

How to process select list of files and output to the same file?

Hi, I've a list of files ac_info.tps, subscription_array.tps, .......and many other files one of the file, bin_range_list.tps has the following content CREATE OR REPLACE TYPE "BIN_RANGE_LIST" AS TABLE OF BIN_RANGE_ELEM; / grant execute on... (4 Replies)
Discussion started by: jediwannabe
4 Replies

4. Shell Programming and Scripting

sql select command output formatting in shell script

Hi, I need to connect to the database and retrieve two variables from the database and store them in a variable,out of these two variables I need to get lastdigit appended to the variable 1 retrieved and variable 2 with out any modification in short select var,data from usage; o/p=... (1 Reply)
Discussion started by: rkrish
1 Replies

5. AIX

Sending script output as email

Hi i have a script which executes daily through cron. The output of the script is appended to a log file everyday It also emails me the output of the logfile as we have the mailx command in the script The below is my requirement : Normally When I get the email it sends the entire content... (3 Replies)
Discussion started by: newtoaixos
3 Replies

6. Shell Programming and Scripting

output file of the shell script running through crontab is deleting automatical daily.

Dear Friends, I am working on IBM AIX. I have written one script and kept in the crontab as to run daily at 11:38 AM. and the output of the script to be appended to the file generated with the month name. but my file deleting daily and the new file is creating with the output of the shell... (2 Replies)
Discussion started by: innamuri.ravi
2 Replies

7. Shell Programming and Scripting

Running a shell script in cron...email not sending - help??

I am pretty new to Unix shell scripting, but wondered if anyone could help (in layman's terms if possible!!) :) I have a shell script which ultimately sends an alert to an email address if part of a batch of programs fails. Here's the script that sends the email: Script: 6check.csh... (8 Replies)
Discussion started by: tjhorwood
8 Replies

8. Shell Programming and Scripting

FTP script for sending a file from one unix directory to another unix server director

Hi, My local server is :/usr/abcd/ Remote server is :/Usr/host/test/ I want to send files from local unix directory(All files starting with O_999) to remote host unix directory. Can any body give me the Unix Shell script to do this. One more doubt: Shall we need to change the file... (1 Reply)
Discussion started by: raja_1234
1 Replies

9. Windows & DOS: Issues & Discussions

Want to use the output of Select statement in Unix script

Hi, I have a UNIX script which calls SQL Select statement: Now i want to use the output of that select statement within my UNIX script so as to call different shell script depending upon the output of the select statement. Can anyone help me in this regard. TIA Akhil Goel (4 Replies)
Discussion started by: akhilgoel9
4 Replies

10. Shell Programming and Scripting

Sending mail from Unix - For html output

I have automated some checks - and I want to send a email when there is an issue. This is fine and I can send the email. However - I want to generate the email in html format so I can highlight any issues to a reader.. ie. If there is a disk space issue - then the email will highlight the... (2 Replies)
Discussion started by: frustrated1
2 Replies
Login or Register to Ask a Question