OraFAQ Forum: Unix » Passing a unix variable to sql query

Home » Infrastructure » Unix » Passing a unix variable to sql query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing a unix variable to sql query [message #140037]

Fri, 30 September 2005 09:42

Naveen Verma
Messages: 60
Registered: August 2004

Member

Hi,
I am writing a function in unix which takes two parameters, say "param1" and "param2". This function consists of a simple sql query, say

function ABC(){
# assuming that connection to oracle went fine
# mentioning only the sql
SELECT * FROM TABLE123
WHERE C_DATE > SYSDATE - $param1
AND C_DATE < SYSDATE - $param2.
}
Can anyone help me out how to get it?

Thanks in advance....

Regards,
N.V Smile

Report message to a moderator

Re: Passing a unix variable to sql query [message #140040 is a reply to message #140037]

Fri, 30 September 2005 09:59

somnath1974
Messages: 15
Registered: July 2005

Junior Member

Hi Naveen,
Place the sql in a file and invoke the sql file with command line parameters.

If you are using Oracle then you could easily use the command line sequential parameter feature like the &1, &2 etc.

An example:

----------------
sql file : test.sql
SELECT
A.ENAME,
A.ENO,
FROM EMP A,
WHERE E.ENO='&1';
quit;
-----------------
In the shell script invoke this sql file after logging into SQL*Plus client and redirect the output to a file.

sqlplus -S user/pass@sid @get_camps.sql $param1 > my_log.log

Hope this helps !

Best Regards,
Somnath

Report message to a moderator

Re: Passing a unix variable to sql query [message #140053 is a reply to message #140037]

Fri, 30 September 2005 10:45

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

oracle@mutation#func 1000 2000
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
 
6 rows selected.
 
oracle@mutation#cat func
#!/usr/bin/bash
function foo {
sqlplus -s scott/tiger <<EOF
select * from emp where sal between $1 and $2;
exit;
EOF
}
foo $1 $2

Report message to a moderator

Re: Passing a unix variable to sql query [message #140241 is a reply to message #140053]

Mon, 03 October 2005 02:19

Naveen Verma
Messages: 60
Registered: August 2004

Member

Thank you very much guys.

Its your goodness, am gr8ly appreciated your support on behalf of my team.

Thanks
Naveen Cool

Report message to a moderator

Previous Topic:	Different Question
Next Topic:	how to know file system from iostat output..

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 19:38:40 CST 2024