topic Re: unix + sql in Operating System - Linux

unix + sql

Gyankr — Sat, 15 Dec 2007 17:20:09 GMT

I have a variable (say x) which i have to pass to a query (for eg. select col2 from table where update_date=(sysdate -x) ). Inturn i have to pass the result (col2 value) back to my unix script for further computation.
Is this possible?

Regards,
Gyan

Re: unix + sql

James R. Ferguson — Sat, 15 Dec 2007 18:49:26 GMT

Hi Gyan:

A Google for unix+sql returned these useful examples:

http://www.orafaq.com/forum/t/48103/0/

http://www.tek-tips.com/faqs.cfm?fid=2218

Regards!

...JRF...

Re: unix + sql

Hasan Atasoy — Sat, 15 Dec 2007 19:37:56 GMT

hi gyan ;

look at fallowin url ;

http://www.dbasupport.com/forums/archive/index.php/t-9326.html

#!/bin/csh -fb
set param ="3"

sqlplus system/manager@orcl << EOF | tee /tmp/$$TMP_FILE
select some_value from some_table where some_param = $param
EOF

Re: unix + sql

Arturo Galbiati — Tue, 18 Dec 2007 11:09:29 GMT

Hi,

print "\
$OraUsr/$OraPwd
set ver off feed off head off pages 0 lines 200 trim on
select col2 from table where update_date=(sysdate -$x);
exit;
"|sqlplus -s|read myvar
print myvar

This piece of code retrun teh value of col2 into var myvar using shell variable $OraUse, $OraPwd, $x

You can improve it a bit adding checks about errors.

HTH,
ART

Re: unix + sql

Sandman! — Tue, 18 Dec 2007 12:10:15 GMT

Or how about the following shell script:

#!/usr/bin/sh

VAR=$(sqlplus -s user/pass <select col2 from table
where update_date = (sysdate - x)
/
EOF)

# now VAR has a value and is available for further computation

echo $VAR

Re: unix + sql

Gyankr — Tue, 18 Dec 2007 12:13:51 GMT

Hello everybody,
Thanks for the suggestions. Can we decouple the unix and sql scripts separately.
I mean to call an sql file from unix shell and pass the result back to unix shells.

Re: unix + sql

Yogeeraj_1 — Tue, 18 Dec 2007 13:37:29 GMT

hi gyan,

You can try to put the SQL statement in a file and then use the following:

MYSQLSTMT=$(cat /home/yogeeraj/sql/sql01.sql)
the rest is a matter of running the sql and retrieving the results.

if you need further help, do let u know.

Also if you have a piece of code that is not working, you can always post it here...

kind regards
yogeeraj

Re: unix + sql

Gyankr — Tue, 18 Dec 2007 16:55:11 GMT

SQ_DB=database A
SQ_USER=
SQ_PASSWD=
EZS_DB=database B
EZS_USER=
EZS_PASSWD=
EZS_SQLFILE=/home/gppsq/temp/sample/ezs.sql
SQ_SQLFILE=/home/gppsq/temp/sample/sq.sql

#connect to first database
mysqlstm=$(cat ${SQ_SQLFILE} | sqlplus -s $SQ_USER/$SQ_PASSWD@$SQ_DB);

# here i get the result of the above sql file (SQ_SQLFILE) which is stored in the variable below (st)

st=`echo ${mysqlstm}`

echo "$st" #output is a date (eg: 24-Jun-2007 )

now the question is how do i pass the result of sq.sqlfile ($st) to EZS_SQLFILE

My EZS_SQLFILe has the below sql statements;

select value1 from table_name where col2_date > '$st';

I tried the below

sqlplus -s $EZS_USER/$EZS_PASSWD@$EZS_DB @/home/gppsq/temp/sample/ezs.sql $st

but got error as
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards,
Gyan