Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Reading value from sqlplus in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Reading value from sqlplus in UNIX
# 1  
Old 04-25-2013
Reading value from sqlplus in UNIX
I have a unix shell script that calls two Select Queries using sqlplus as shown below.

Code:
 
more extract.sh
 
result=$(sqlplus  -s usr/swd@myhost_sid @/tmp/SELECT_QUERY.run)
 
more @/tmp/SELECT_QUERY.run
 
SELECT PROBLEM_TYPE, COUNT (*) FROM BATCH WHERE UPPER (STATUS) = 'NEW' AND PROB_TYPE = 'AN_IP' GROUP BY PROB_TYPE;
SELECT PROBLEM_TYPE, COUNT (*) FROM BATCH WHERE UPPER (STATUS) = 'NEW' AND PROB_TYPE = 'CIR_ID' GROUP BY PROB_TYPE;
exit;

How can I read the values (37 for 'AN_IP' and 14 for 'CIR_ID') in unix shell ?

current output is:

Quote:
PROB_TYPE COUNT (*)
-------------------- ----------
AN_IP 37

PROB_TYPE COUNT (*)
-------------------- ----------
CIR_ID 14
# 2  
Old 04-25-2013
Using parameter substitution in any POSIX shell:
Code:
AN_IP="${result##*AN_IP}"
AN_IP="${AN_IP%%PROB_TYPE*}"
CIR_ID="${result##*CIR_ID}"
echo $AN_IP
echo $CIR_ID

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sqlplus variable UNIX

hi guys i have a sqlplus : sqlplus -s username/password << EOF @mysql.sql EOF in mysql.sql there is a count of a table, i want to write in a variabile unix. how can i do? Thanks a lot Regards Francesco. (3 Replies)
Discussion started by: Francesco_IT
3 Replies

2. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

3. UNIX for Advanced & Expert Users

Unable to connect to sqlplus from unix

Hi, I have been trying to connect to sqlplus the same way I used to do in my earlier company but I get these error messages , please suggest way out - user name - xyzuser schema name - xyzschema $ sqlplus xyzuser@xyzschema ksh: sqlplus: not found. $ sqlplus -s xyzuser@xyzschema... (5 Replies)
Discussion started by: dhirajdsharma
5 Replies

4. Shell Programming and Scripting

SQLPLUS query in Unix script

Hi, I am using sqlplus query to get results in a csv format in unix. I am using ksh, and below is the query. echo "select r.num|| ',' || p.path ||',"' || r.issue_description ||'",' ||p.timestamp from events r, messagepath p;">> $QUERY_FILE sqlplus -s $LOGIN @ $QUERY_FILE>>$OUTFILE ... (2 Replies)
Discussion started by: Nutan
2 Replies

5. UNIX for Advanced & Expert Users

To enable sqlplus for a user in unix

Hi I added a new user using root... now i need to enable sqlplus for that particular user... when i give sqlplus schemaname/pwd it gives $ sqlplus -bash: sqlplus: command not found PFB the OS am using $ uname -a Linux gs-portal-04 2.4.21-57.ELsmp #1 SMP Wed Apr 23... (4 Replies)
Discussion started by: Whiteboard
4 Replies

6. UNIX for Dummies Questions & Answers

SQLPLUS in unix

HI Guys, i just want ask the steps when trying to create a simple SQL select statement in linux. I know how to use the select statement in SQLplus, but im having a difficult time in having a unix script connect direct to SQL plus and then retrieve the selected statement. example.... (2 Replies)
Discussion started by: mdap
2 Replies

7. Shell Programming and Scripting

Passing the unix variable to sqlplus

Hi, I am writing a script which creates an external table using a shell script. My requirement is like this. Usage: . ./r.ksh <table_name> - this should create an external table. e.g . ./r.ksh abc - this should create an external table as abc_external. How do i achieve this? Please... (5 Replies)
Discussion started by: Anaramkris
5 Replies

8. UNIX for Dummies Questions & Answers

connect sqlplus from unix

hi, I have this basic query. I have created a new user on unix. I have given home directory and permission through chmod to create directory stucture. Now need to connect sqlplus. What permissions should we give, so that this works? Any help is appreciated. Thanks, Neha (1 Reply)
Discussion started by: nehak
1 Replies

9. Shell Programming and Scripting

Need example program for sqlplus in Unix

Hi I am new to unix .I need a example program to connect oracle10g DB from shell script.The task is to connect sql from shellscript and to write a query inside to retieve some fiels.The reseult should be stored in a seperate .dat file(by using spool). Thanks Bala. (1 Reply)
Discussion started by: tobalajia
1 Replies

10. UNIX for Advanced & Expert Users

How to pass unix variable to SQLPLUS

hi fellows, can any body tell me how to pass unix variables to oracle code is... #! /bin/ksh echo ENTER DATE VALUE's read START_DATE END_DATE sqlplus xyx/abc@oracle select * from table1 where coloumn1 between $START_DATE and $END_DATE; is this is correct way........... Thanks in... (1 Reply)
Discussion started by: chiru
1 Replies
Login or Register to Ask a Question