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

Hi Guys,


I am writing a sql script for which i want to pass parameters and run that sql script. For ex:

select * from dept where deptno in (10,20,30,40);

i want to pass 10,20,30,40 as parameters to the sql script that is written. How can i write the query and pass the parameters to the above query.

Help Appreciated.

Thanks
amankhanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pierrick LOUBIERIS Operational Excellence ManagerCommented:
select * from dept where deptno in (&1);
0
riazpkCommented:
Hi amankhan,

Do you want to pass multiple parameters? is the number of parameters fixed or varying?
0
sapnamCommented:
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';

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Pierrick LOUBIERIS Operational Excellence ManagerCommented:
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);
0
Harish_RajaniCommented:
Actually its very simple..
and you dont have to interactively pass the values.


Just say,

@test.sql parameter1, parameter2, parameter3.

You have to refer parameter1, parameter2, parameter3 as &1, &2, &3.

For example write the sql as following:

test.sql
select * from emp
where emp_id  between &1 AND &3
and emp_id < &2;

Now to pass the parameters just do following

SQLPLUS> @test.sql 12345  1000  12500

Good luck !!

Rgds,
HR
0
Harish_RajaniCommented:
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
0
amankhanAuthor Commented:
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
0
sapnamCommented:
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
0
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
Accept one parameter, and pass the full string :

SQL> select * from dept where deptno in (&1);
Enter value for 1: 10,20,30,40
old   1: select * from dept where deptno in (&1)
new   1: select * from dept where deptno in (10,20,30,40)

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>
0
actonwangCommented:
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    
0
Harish_RajaniCommented:
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
0
amankhanAuthor Commented:
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.
0
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
Which OS ?
0
Harish_RajaniCommented:
You can use UTL_FILE.get_line function to read the data from file and pass it to the sqlscript

Prerequisite:
1)You must place your input file into one of the directories listed againset parameter UTL_FILE_DIR. You can see this parameter by issuing following command on sqlplus
select value from v$parameter where name = 'utl_file_dir';


declare
file_handle   utl_file.file_type;
inputparameter varchar2(1000);
begin
file_hande := utl_file.fopen (path, filename, 'R');   -- Open the file
--here path is directory e.g /home/user, and filename is the name of
--file that contains parameters

WHILE TRUE
LOOP
utl_file.get_line (file_hande, inputparameter); -- read the first line
@test.sql inputparameter;

-- here instead of calling test.sql, you can write the select statement directly. e.g select * from my_table where id = inputparameter;
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND
UTL_FILE.fclose(file_handle);
WHEN OTHERS

UTL_FILE.fclose(file_handle);

END;






Rgds,
HR

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amankhanAuthor Commented:
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
0
Harish_RajaniCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.