How can you pass parameters to a sql script....

select * from dept where deptno in (&1);

Hi amankhan,

Do you want to pass multiple parameters? is the number of parameters fixed or varying?

The above solution will work but the user will have to enter the string as 10,20,40,40.  If you want the user to enter 4 values with some prompt being shown to them , you can use the accept command as

accept dept1 prompt 'Enter Unit 1 : '
accept dept2 prompt 'Enter Unit 2 : '
accept dept3 prompt 'Enter Unit 3 : '
accept dept4 prompt 'Enter Unit 4 : '
accept dept5 prompt 'Enter Unit 5 : '

select *
from dept
where deptno = '&dept1'
UNION ALL
select *
from dept
where deptno  = '&dept2'
UNION ALL
select *
from dept
where deptno  = '&dept3'
UNION ALL
select *
from dept
where deptno  = '&dept4'
UNION ALL
select *
from dept
where deptno  = '&dept5';

Multiple parameters can be passed separately to the script with a single line of code :

select * from dept where deptno in (&1, &2, &3, &4);

Following covers a comprehensive discussion on the topic of passing parameters

http://www.oracle.com/technology/support/tech/sql_plus/htdocs/sub_var9.html

Rgds,
HR

Hi Guys,

Thanks a lot for the assistace. My parameters are varying riaz. They are not fixed. In that case, How can we pass them.

Thanks

IF you know the max number of parameters, you can provide for that many. User can enter the required ones and leave the rest blank. I tested that with the SQL I gave

Instead of passing paramteres, the following script will do clearer and better job for you:


    set echo off
    set verify off

    accept values char prompt "Type in values as 10,20,30 : "
    select * from dept where deptno in (&values)
    /
   
    set verify on    

I am sure there are plenty of ways of passing parameters, apart from many that have already been discussed here.

If you want to use the parameters as input to 'IN' clause, then the solution given by actonwang would work very well.

It would be useful for all of us, if you can share how exactly you are using the parameters inside the script.

Rgds,
HR

Hi Guys,


Thanks for the help provided by all of you. For ex:- the parameter values are stored in a file. How can we pass the values of that file as parameters to the script.

My parameter values change and they are very huge in number. So am storing them in a file and want to pass the values from the file one by one.

Is there a way it be done. Help Appreciated

for ex:--

deptid.txt --- File name

10
20
30
40
50
60
70
80
90
100
110 --- are the values in the file that should be passed to the query. This list can vary from time to time.

Which OS ?

hey harish,

thanks for the idea u have given. Its good to implement using utl_file i think.

Guys, i need some more help.

I am selecting one value using select by passing a parameter to that select statement which are stored in a file using utl_file package.

Now, I want to use the output of that query and use that value to update a column in the same table.

for ex:-

select comm from emp where empno = &empno1;

update emp
set comm = comm + 10
where empno = &empno1

Help Appreciated

Thanks

Your example is to simplistic, but I got your point.
You can capture the value in a variable and then use that variable to update the column.

Eg


--Store the value in a variable.

declare
v_comm varchar2(2000)

begin
Select comm into v_comm from emp where empno = &empno1;

--Now you can update the column in the table
update emp
set comm = v_comm
where emp_no = &emp_no;

End;

Rgds,
HR