passing the variables in UNIX

Hi,
Thanks in advance.
Please help me I am passing the variables from the web browser to the UNIX by clicking the button my browser. But question is the variables which I am passing are not passing to the UNIX. The following are the variables I am declared p_new_proj_id and p_upd_proj_id.

Please help me how do I pass the variables to the UNIX script.


UPDATE rem_temp
SET proj_id = '$P_new_proj_id'
WHERE proj_id = '$p_upd_proj_id'


admala

Hi,

You can't give $ inside the sql statement. You have to have a shell script which has the $ arguments and the sql should have '&' inside. Here is an example :

Sql Script: a.sql

select to_date('&dt1') + 1,to_date('&dt2') + 1
from dual;

Shell script: a.sh

date1=$1
date2=$2
cr="\n"

echo $date1 $cr $date2 | sqlplus user/pwd @a.sql

##End SH script.

At the unix prompt:

$ a.sh 01-nov-2000 02-nov-2000

Hope this helps.

Thanks
Padma

Thanks for the info but my problem is now I am passing the values from the WEB. Means the user is entering the values on the browser and I am capturing the values in to these two variables p_new_proj_id and P_upd_proj_id and I am passing the variables directly to sqlrun stream which is calling my procedure and it is supposed to take the values but some how its not taking the variables which I am passing from the web browser.

As per ur answer I have to create the values on my procedure but its not possible because user enters the values and those values has to go directly to the variables which I declared on the procedure like the following update statement.

UPDATE rem_acquisition_screening
SET proj_id = '$P_new_proj_id'
WHERE proj_id = '$p_upd_proj_id'

Thanks

I thought you had mentioned

'Please help me how do I pass the variables to the UNIX script'

Let me know how exactly you are calling the proc??? Through unix script or third party tool?

I am capturing the following variables from the Browser

USER ID: REMIS
PASSWORD:
MODIFIED PROJECT/INSTALL CODE:

We are using Oracle Web Application Server and I wanted to send these variablles to the UNIX machine.
Now I am connecting to the oracle successfully with out any problem the following code i am using now

HTP.print('frmLOV = open("/cgi-bin/run_sql?userid=" +');
HTP.print(' escape( document.forms[0].P_USERID.value + "/" +');
HTP.print(' document.forms[0].P_PASSWORD.value + ');
HTP.print(' "@" + sidDNS) + ');
HTP.print(' "&sqlproc=" + proc ');


Proc: is the procedure name which I am passing.

Now I wanted to pass the two more variables and these variables are going to change the project id.

Thanks in advance.

The quesion is how do I pass the variables from Out side to the UNIX because now its upadating with the set variable but its not giving the value of the variable. like as I mentioned I am passing the user id and password and its connecting to the sql with out any problem

passing parameters to unix takes the following form :

1. call the unix script with the paramters

unix_script p_uid p_pwd

the p_uid and p_pwd will appear in Unix script as $1 $2



2. Now unix script in turn will do following to make use of
two paramters

1. make a x.sql script with two DEFINE paramters.

echo 'DEFINE p_uid='$1 >> x.sql
echo 'DEFINE p_pwd='$2 >> x.sql

2. run another .sql script which will first run x.sql and
call the procedure or select statement while using
&p_uid and &p_pwd.


I hope this helps.

Regards

gtm

You just need to add the parameters to the query string you're using to run the script. A typical url with a query string looks something like

http://yourhost/path/yourscript?var1...e2&var3=value3

etc

The code you have now is using open to fetch run_sql and adding a query string to it, but it looks like it might not be working correctly. Does it cause problems? It shouldn't hae spaces between the variables, or quotes around the variable names either. In any case, you just want to add the values to your query string, with something like

htp.print('&p_new_proj_id=' + p_new_proj_id);
htp.print('&p_upd_proj_id=' + p_upd_proj_id);

under the sample you already posted.

You also have to make sure your unix script is prepared to handle those parameters, they will be coming in the environment variable QUERY_STRING.