Monday, October 14, 2013

How to Assign the Output of an SQL Query to a Unix Variable

www.unixbabuforum.inestablish an oracle connection from unix shell scripting and assign the output of the following sql query to the corresponding variables 

SELECT TO_CHAR (START_DATE + 1, 'YYYYMMDD') AS LOAD_DATE, START_DATE- 6 as DATA_START_DATE, END_DATE - 8 as DATA_END_DATE 
FROM WEEK_TO_445CALENDAR 
WHERE START_DATE < SYSDATE AND SYSDATE < END_DATE 

variables are LOAD_DATE, DATA_START_DATE, DATA_END_DATE 

www.unixbabuforum.inexecute below commands: 

1. var=$(date) 
2. echo $var 

In first command you assign output of date command in "var" variable! $() or `` means assign the output of command. 
And in the second command you print value of the "var" variable. 

Now for your SQL query, Think that you have SQL command like: 
SELECT * FROM TBL; 
EXIT; 

NOTE: Don't remember to write semicolons and the EXIT command at last. 

var=$(sqlplus username/password << EOF 
SELECT * FROM TBL; 
EXIT; 
EOF 


In above example, We called sqlmap and passed it username and password as argument. 

Now I use "<< EOF" : << is called HERE-DOCUMENT and it's good to pass some commands to another command. like here we passed SELECT * FROM TBL and EXIT to "sqlplus" command. 

EOF : means END OF FILE, You can choose another string! But when you passed all of SQL commands, you have to specify the END of them, here we do that with EOF string. 

Now your sql query output's has stored in "var" variable. 

www.unixbabuforum.inusing INFORMIX and "ksh", i will do it like that (in a rush) - You'll have to tune for ORACLE: 

root@devx:/tmp/nm # set $( echo "select min(dt_deb) as _min, max(dt_deb) as _max from stat_run_traces" | dbaccess db_olf ) 


To check: 
root@devx:/tmp/nm # echo $@ 
_min _max 07/05/11 26/08/13 
root@devx:/tmp/nm # 

To assign: 
varmin=$3 
varmax=$4 



Limitations : 
Be careful when data returned contains spaces !! 

root@devx:/tmp/nm # set $( echo "select \"happy new year\" as _col1, min(dt_deb) as _min, max(dt_deb) as _max from stat_run_traces" | dbaccess db_olf ) 

root@devx:/tmp/nm # echo $@ 
_col1 _min _max happy new year 07/05/11 26/08/13 
root@devx:/tmp/nm # 


So in some situations, you'd better do 2 database accesses to set shell vars, and shift the first 1, which is the label. 

root@devx:/tmp/nm # set $( echo "select first 1 \"happy new year\" as _col1 from stat_run_traces" | dbaccess db_olf ) 
root@devx:/tmp/nm # echo $@ 
_col1 happy new year 
root@devx:/tmp/nm # shift 
root@devx:/tmp/nm # echo $@ 
happy new year 
root@devx:/tmp/nm # some_news=$@ 
root@devx:/tmp/nm # echo $some_news 
happy new year 
root@devx:/tmp/nm #

www.unixbabuforum.inYou can use this sample 

OUTPUT=$(sqlplus scott/tiger << EOF \ 
select * from emp;\ 
EOF) 

set -$OUTPUT 

check $1 $2 .

www.unixbabuforum.invar=$(date) or var=`date` 

Better example: 

VAR=`sqlplus <<EOF 
SELECT * FROM DB; 
EXIT; 
EOF` 

www.unixbabuforum.inSeveral people tried to help you. Don't flag them. This forum is for learning, it is not an exchange for people to provide tested cookbook solutions for you. 

The issue is you have a very confused idea of what you are doing here. 

First, any SQL query can produce multiple rows of output. So you could get many sets of the fields you are selecting. A simple assignment of variables only deals with one row. So how do you expect to deal with several results sets? And incidentally ignoring any header and formatting stuff injected by SQL. 

Also, you have to realize that sql variables and shell variables have nothing to do with each other. What people are telling you above is some tricks to transfer values between those two environments. 

I would treat this as a data processing issue. This is too hard for a novice to deal with in one bite. 

First, wrap your query in a function. 

Then run the function, redirect output to a file, read the file into an editor, and figure what you have to deal with. 

Probably, you will want to use awk to deformat the query output (in another function to be separately tested), then have a read loop that iterates over the rows and processes one set of fields at once. 

I don't understand your sql. I have used Oracle and Ingres for 20 years, but I would have to work through it, and I would like to see the schema and data samples to fully understand it. You maybe have a bunch of formal training in SQL. So why do you assume that bash can be learned without effort and solved with a quick free one-liner?



0 comments:

Post a Comment

 
Design by BABU | Dedicated to grandfather | welcome to BABU-UNIX-FORUM