Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

put value of multiple sql statements into unix variables

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting put value of multiple sql statements into unix variables
# 1  
Old 11-11-2008
put value of multiple sql statements into unix variables
i want to use multple sql count statements and store these count values in unix variable but in one connection only i.e. in only 1 time database should be hit ,which is the main requirement.
# 2  
Old 11-11-2008
I have done it by having a shell script execute a sql file like this:

Shell script:
Code:
SQL1='select count(*) as City_Count from cities;'
SQL2='select count(*) as State_Count from states;'
sqlplus /nolog <<EOF

set serveroutput on size 100000
WHENEVER SQLERROR exit 1
WHENEVER OSERROR exit 1

connect $USERNAME/$PASSWORD@$DBNAME

@/opt/scripts/Executed_sql "$SQL1" "$SQL2"
exit
EOF

exit

Then for the file Executed_sql.sql I output to a text file:
Code:
def sel1='&1'
def sel2='&2'

SPOOL /opt/outgoing/output.txt
&sel1
&sel2
SPOOL OFF

This may need a few tweaks and I won't guarantee the coding, but it may get you started on what you need
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Assigning multiple column's value from Oracle query to multiple variables in UNIX

Hi All, I need to read values of 10 columns from oracle query and assign the same to 10 unix variables. The query will return only one record(row). I tried to append all these columns using a delimiter(;) in the select query and assign the same to a single variable(V) in unix. I thought I... (3 Replies)
Discussion started by: hkrishnan91
3 Replies

2. Shell Programming and Scripting

Create Multiple UNIX Files for Multiple SQL Rows output

Dear All, I am trying to write a Unix Script which fires a sql query. The output of the sql query gives multiple rows. Each row should be saved in a separate Unix File. The number of rows of sql output can be variable. I am able save all the rows in one file but in separate files. Any... (14 Replies)
Discussion started by: Rahul_Bhasin
14 Replies

3. UNIX for Dummies Questions & Answers

Getting values of 2 columns from sql query in UNIX variables

Hi, I have connected to oracle database with sqlplus -s / <<EOF select ename, age from emp where empid=1234; EOF I want to save the values of ename and age in unix shell variables. Any pointers would be welcome.. Thanks in advance!!1 Cheers :):):):) (1 Reply)
Discussion started by: gonchusirsa
1 Replies

4. Shell Programming and Scripting

How to store results of multiple sql queries in shell variables in ksh?

Hi, I have a script where I make a sqlplus connection. In the script I have multiple sql queries within that sqlplus connection. I want the result of the queries to be stored in shell variables declared earlier. I dont want to use procedures. Is there anyway else. Thanks in advance.. Cheers (6 Replies)
Discussion started by: gonchusirsa
6 Replies

5. Shell Programming and Scripting

sql output from multiple rows and columns as variables in a script

This is for an Oracle journal import. I was using a pl/sql package and oracle API's. Oracle added invoker rights to their API's and now my package won't run. I didn't want to use their API's anyway. The only reason i was using pl/sql and the API's (just a package) was to utilize a cursor. How... (2 Replies)
Discussion started by: lmu
2 Replies

6. Shell Programming and Scripting

unix variables from sql / pl/sql

How do I dynamically assign the below output to unix shell variables so I can build a menu in a shell script? Example: var1 = 1 var2= SYSTEM var3 = 2 var4= UNDOTBS1 and so on, then in the shell script I can use the variables to build a menu. set serveroutput on declare... (2 Replies)
Discussion started by: djehres
2 Replies

7. Shell Programming and Scripting

isql input file with multiple sql statements

I've got: isql -U $USERID -S $SERVER -D $DATABASE -i inputfile.sql -o outputfile.txt in inputfile I have: go sql#1 go sql#2 go sql#3 go I also tried without "go" and with";" instead which did not work SQL statements will work if I paste them directly into the script and use EOF ... (0 Replies)
Discussion started by: Cailet
0 Replies

8. Solaris

Automate SQL statements

Hello, On unix side, I have written below script for taking oracle db backup. But when I tried to execute it - i could not execute the sql statements from unix. Please suggest ------------------------ $ more bkp.sh #!/bin/ksh # make sure database is shutdown cleanly sqlplus '/ as sysdba'... (3 Replies)
Discussion started by: panchpan
3 Replies

9. Shell Programming and Scripting

How to write multiple echo statements in unix?

How to write multiple echo statements in unix? echo "************************************************************************************************************"; echo This Script do the following functions echo 1. Point 1 echo 2. Point 2 echo 3. Point 3 echo... (2 Replies)
Discussion started by: Shrutiduggal
2 Replies

10. Shell Programming and Scripting

Pass multiple variables to SQL script

I am trying to close of multiple users in an Oracle database. Each users has records in multiple tables what I need to do is use a script that call each SQL seperately passing either CLI arguments or gathered arguments from the users during run time. ## Accept variable(s) from the command line... (1 Reply)
Discussion started by: jagannatha
1 Replies
Login or Register to Ask a Question