Shell Programming and Scripting

So what's the issue?

tyler_durden

Using this way
echo "SELECT ename from emp where empno = $i;"'\n'will pass the empno as value, I was looking to pass it as sql bind variable which looks like
echo "SELECT ename from emp where empno = :SomeId;"'\n'

Why do you want to do that?

tyler_durden

...

Code:

while (x<4)
loop
exec :deptno := '${FileName[x]}'
select customer_name from customer where customer_id=:deptno;
end loop

You almost got it, just need a sub shell in the here doc:

Code:

$(for i in ${FileName[@]}; do
echo "
exec :deptno := '$i'
select customer_name from customer where customer_id = :deptno;
"
done)

You may need to change $() to `` to get around of a ksh bug.

Tyler_durden, this is requested by the DBA team, using bind Variable is preferred in continuous operation that user variable Bind Variables (I'm posting the link to share the information that might help others ).

Binlib, using your way is by passing the usage of bind SQL variable and all query that are statically using ( yyy=:xxx ) will be rejected as :xxx is a non accepted value for column yyy

At the end, please let me say thank you for all those are helping here, I'm really learning from your great experiences and new information were added to my knowledge with each single line of code you dropped here to help me.

---------- Post updated at 06:56 AM ---------- Previous update was at 06:47 AM ----------

For those where are not familiar with bin variables this article simply explain why you might use them one time.
Bind variables - The key to application performance

Shell variables can not be used as bind-variables directly. A bind variable is a variable of the accessing application passed to the database. The accessing application in this case is SQL*Plus and not the shell itself. You can use bind variables defined in SQL*Plus as shown in several places in this thread.
Your DBA team gives good advice about bind variables, but there is no way to prepare the statement and then pass the variables when the prepared statement is executed because the shell does not have a mechanism to do so.
Perl or phyton offer support for bind variables and do not need SQL*Plus to connect to the database.

Thank you Cero, I had doubts that there is a way somewhere to do this. but it's clear as there is no way for such actions.
Would like to thank all participants once again, Thank You!