?
Solved

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

Posted on 2006-04-03
16
Medium Priority
?
35,142 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:amankhan
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 16367637
select * from dept where deptno in (&1);
0
 
LVL 13

Expert Comment

by:riazpk
ID: 16368366
Hi amankhan,

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

Expert Comment

by:sapnam
ID: 16368398
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 16368665
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
 
LVL 4

Assisted Solution

by:Harish_Rajani
Harish_Rajani earned 1200 total points
ID: 16369947
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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16370016
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
 

Author Comment

by:amankhan
ID: 16370893
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
 
LVL 8

Expert Comment

by:sapnam
ID: 16371047
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
 
LVL 8

Assisted Solution

by:Pierrick LOUBIER
Pierrick LOUBIER earned 300 total points
ID: 16371066
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
 
LVL 19

Expert Comment

by:actonwang
ID: 16372203
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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16372315
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
 

Author Comment

by:amankhan
ID: 16384742
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
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 16389182
Which OS ?
0
 
LVL 4

Accepted Solution

by:
Harish_Rajani earned 1200 total points
ID: 16390300
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
 

Author Comment

by:amankhan
ID: 16396912
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
 
LVL 4

Expert Comment

by:Harish_Rajani
ID: 16443400
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question