unix + sql

[Gyankr]

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

[James R. Ferguson]

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

[Hasan Atasoy · Accepted solution]

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

[Arturo Galbiati]

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

[Sandman!]

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

[Gyankr]

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.

[Yogeeraj_1]

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

[Gyankr]

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