Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

redirecting oracle sqlplus select query into file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting redirecting oracle sqlplus select query into file
# 1  
Old 05-18-2010
redirecting oracle sqlplus select query into file
So, I would like to run differen select queries on multiple databases..

I made a script wich I thought to be called something like..
Code:
./script.sh sql_file_name out.log

or to enter select statement in a command line..
(aix)

and I did created some shell script wich is not working..
it looks like:
Code:
#!/bin/bash
sql=$1
out_file=$2
if [ -f $out_file ]
then
        rm $out_file
fi
while read line
do
        if [[ ! "$line" =~ ^# ]]; then
                tns="$(echo $line | cut -d: -f2)"
                pwd="$(echo $line | cut -d: -f1)"
                echo SYS@ $tns >> $out_file
                export ORACLE_SID=$tns
                #sqlplus -S -L sys/$pwd@$tns  AS SYSDBA < $sql >> $out_file
                #echo +++++++++++++++++++ >> $out_file
                echo $sql
                sqlplus -s -l sys/$pwd@$tns AS SYSDBA < $sql >> $out_file
        fi
done < "connections_sys"


so, what's wrong?

I would like to make something like 'gimme all instance names from all databases into my file'
Last edited by Franklin52; 05-18-2010 at 08:36 AM.. Reason: Please use code tags!
# 2  
Old 05-18-2010
can you post the content of connections_sys?
also, Are you getting some error? whats that?
# 3  
Old 05-18-2010
connection_sys looks something like:

Code:
dbpwd1:DATABASE1
dbpwd2:DATABASE2

error is like:

./run_sys.sh: line 20: $sql: ambiguous redirect
SELECT NUM, NAME, VALUE FROM V$PARAMETER WHERE NUM=1715
# 4  
Old 05-18-2010
If I understand correctly and you are trying to redirect the query results to a text file then you can try something like this:

Code:
 
sqlplus -s user/pass << EOF
SPOOL /Myoutputdir/myfile.txt
select sysdate from dual;
SPOOL OFF

You might also want to check sqlplus set commands like:

set linesize
set pagesize
set echo
set term

and such
# 5  
Old 05-18-2010
yes, but sql statement should be different each time
# 6  
Old 05-18-2010
Hi
Try this way:

Code:
sqlplus -s -l sys/$pwd@$tns AS SYSDBA  @$sql >> $out_file

Guru.
# 7  
Old 05-18-2010
Quote:
Originally Posted by bongo
yes, but sql statement should be different each time
you can have two files.

one is "connection_sys" and the other one is the sql queries for the respective databases. and read the files simultaneously.

Code:
exec 4<connection_sys
while read query
do
 IFS=":" read user pass sid <&4
 $ORACLE_HOME/bin/sqlplus -S $user/$pass@$sid  << EOF
 $query
EOF
done < sql_queries
4<&-

you can change the sqlplus syntax as you need for sysdba.

remember to change

Code:
IFS=":" read user pass sid <&4

to

Code:
IFS=":" read pass sid <&4

when you remove the username part from the "connection_sys" file.

the current format is:
Code:
user1:pass1:sid1
user2:pass2:sid2

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

2. Shell Programming and Scripting

How to run a SQL select query in Oracle database through shell script?

I need to run a SQL select query in Oracle database and have to capture the list of retrieved records in shell script. Also i would like to modify the query for certain condition and need to fetch it again. How can i do this? Is there a way to have a persistent connection to oracle database... (9 Replies)
Discussion started by: vel4ever
9 Replies

3. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

4. Shell Programming and Scripting

Shell Script to execute Oracle query taking input from a file to form query

Hi, I need to query Oracle database for 100 users. I have these 100 users in a file. I need a shell script which would read this User file (one user at a time) & query database. For instance: USER CITY --------- ---------- A CITY_A B CITY_B C ... (2 Replies)
Discussion started by: DevendraG
2 Replies

5. UNIX and Linux Applications

linux sqlplus select results writes into file twice

Hello, This is my first post and its because I could not find solution for myself I decided to ask help here. What I want to do; I want to get some data from a table 1 on server 1 and insert those datas into a table 2 on server 2. ( lets say schema names are server1 and server 2 also ).... (10 Replies)
Discussion started by: azuahaha
10 Replies

6. Shell Programming and Scripting

redirecting sql query output to a file

Hi, I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning. success records to success.log file failure record to failure.log file. As of now i am doing like... (1 Reply)
Discussion started by: sailaja_80
1 Replies

7. Shell Programming and Scripting

In a csh script, can I set a variable to the result of an SQLPLUS select query?

Can someone tell me why I'm getting error when I try to run this? #!/bin/csh -f source ~/.cshrc # set SQLPLUS = ${ORACLE_HOME}/bin/sqlplus # set count=`$SQLPLUS -s ${DB_LOGIN} << END select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;... (7 Replies)
Discussion started by: gregrobinsonhd
7 Replies

8. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi Yogesh, Lucky that i caught you online. Yeah i read about DBI and the WriteExcel module. But the server is not supporting these modules. It said..."Cannot locate DBI"..."Cannot locate Spreadsheet::WriteExcel" I tried creating a simple text file to get the query output, but the... (1 Reply)
Discussion started by: dolphin123
1 Replies

9. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi , I just found you while surfing for the string 'Redirecting sql select query output from within a shell script to txt file/excel file' Could you find time sending me the code for the above question? It'll be great help for me. I have a perl file that calls the sql file... (1 Reply)
Discussion started by: dolphin123
1 Replies

10. Shell Programming and Scripting

Select a portion of file based on query

Hi friends :) I am having a small problem and ur help is needed... I have a long file from which i want to select only some portions after filtering (grep). My file looks like : header xxyy lmno xxyy wxyz footer header abcd xy pqrs footer . . (14 Replies)
Discussion started by: vanand420
14 Replies
Login or Register to Ask a Question