Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting In a csh script, can I set a variable to the result of an SQLPLUS select query?
# 1  
Old 04-14-2009
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 [unmatched `] 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;
exit;
END`
#
if [$count < 1] then
echo "ERROR: EOD message for PC not found"
endif
#######

If I remove the "set count =" portion and just have the SQLPLUS run, it does retreieve a count. why can't I set that number to my count variable?

thanks!!
# 2  
Old 04-14-2009
Or can I only do this type of variable setting using bash or some other shell?
# 3  
Old 04-14-2009
Quote:
Originally Posted by gregrobinsonhd
Can someone tell me why I'm getting [unmatched `] 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;
exit;
END`
#
if [$count < 1] then
echo "ERROR: EOD message for PC not found"
endif
#######

If I remove the "set count =" portion and just have the SQLPLUS run, it does retreieve a count. why can't I set that number to my count variable?

thanks!!
hmmm thats because you are entering more than one line inside "`"
Code:
 
set count=`$SQLPLUS -s ${DB_LOGIN} << END\
set head off;\
select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;\
exit;\
END`

# 4  
Old 04-14-2009
Quote:
Originally Posted by vidyadhar85
hmmm thats because you are entering more than one line inside "`"
Code:
 
set count=`$SQLPLUS -s ${DB_LOGIN} << END\
set head off;\
select count(1) from put_groups where group_name='PC' and description='EOD_EVENT' and serial_number=1;\
exit;\
END`

So you're saying if I run it all together into one really long line, it should work? I tried that just now and got a "badly placed ()'s error" - something different though! Would you mind posting it the way you feel it should appear in my .csh ? Thank you!!
# 5  
Old 04-14-2009
shouldn't count(1) be count(*) ?
i get a syntax error with count(1) within SQL.
# 6  
Old 04-14-2009
Quote:
Originally Posted by gregrobinsonhd
So you're saying if I run it all together into one really long line, it should work? I tried that just now and got a "badly placed ()'s error" - something different though! Would you mind posting it the way you feel it should appear in my .csh ? Thank you!!
just type the way i written.. above
at the end of line place "\" to continue with the command
# 7  
Old 04-14-2009
Quote:
Originally Posted by quirkasaurus
shouldn't count(1) be count(*) ?
i get a syntax error with count(1) within SQL.
count(1) ,count(*) and count(ROWNUM) mean same but count(1) runs faster than count(*)

Previous Thread | Next Thread
Test Your Knowledge in Computers #996
Difficulty: Medium
Major Unix vendors, including Sun Microsystems, HP, IBM, and SGI, have been selling virtualized hardware since before 1990.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to set variable permanent in csh?

We are using csh on our AIX platform, if we have to export/set a specific environment variable we use setenv command but its only valid till session. How do we set that variable permanent in our csh AIX? Do we put it in userprofile file or something else? (1 Reply)
Discussion started by: aixusrsys
1 Replies

2. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

3. 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

4. Shell Programming and Scripting

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.. ./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... (6 Replies)
Discussion started by: bongo
6 Replies

5. UNIX for Advanced & Expert Users

Set shell variables from SQLPLUS query results

Hi All, I needed to get the result of two sqlplus queris into shell variables. After days of looking for the ultimate solution to this problem.. i found this... sqlplus -s USER/PASS@DB <<EOF | awk '{if(NR==1) printf("%s ", $1); if(NR==2) printf("%s ", $1);}' | read VAR1 VAR2 set head off... (2 Replies)
Discussion started by: pranavagarwal
2 Replies

6. 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

7. 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

8. Shell Programming and Scripting

how to convert the result of the select query to comma seperated data - urgent pls

how to convert the result of the select query to comma seperated data and put in a .csv file using korn shell. Pls help me as its very urgent. Thanks, Hema. (1 Reply)
Discussion started by: Hemamalini
1 Replies

9. UNIX for Dummies Questions & Answers

select count(*) in sqlplus into variable unix shell

Need to select count(*) from table to check for zero result in unix script (2 Replies)
Discussion started by: struggle
2 Replies

10. Shell Programming and Scripting

How to store query multiple result in shell script variable(Array)

:) Suppose,I have one table A. Table A have one column. Table A have 10 rows. I want this 10 rows store into shell script variable. like #!/bin/ksh v_shell_var=Hi here in call oracle , through loop How can I store table A's 10 rows into v_shell_var (Shell Script Array). Regards, Div (4 Replies)
Discussion started by: div_Neev
4 Replies

Featured Tech Videos