| Passing Multiple Parameters to Cursors? [message #587789] |
Tue, 18 June 2013 12:06  |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Hi,
How can we pass multiple parameters to cursors?
Can we do this?
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters. Any ideas?
Thanks
|
|
|
|
|
|
|
|
| Re: Passing Multiple Parameters to Cursors? [message #587796 is a reply to message #587794] |
Tue, 18 June 2013 12:34  |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Sorry my requirement has just changed.
can we pass a where clause to a cursor as parameter? i.e
something like this
ex: Cursor C1(c_whereclause number) is
select emp_name, emp_city
from employees
where emp_Id = 10 ||C_whereclause;
where c_whereclause is dynamic ie. if c_whereclause = 'and region_id = 2'
the cursor query becomes
Cursor C1(and region_id = 2)
select emp_name, emp_city
from employees
where emp_Id = 10 'and region_id = 2';
Any way to pass dynamic where clause to the cursor?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Passing Multiple Parameters to Cursors? [message #587817 is a reply to message #587806] |
Tue, 18 June 2013 15:21 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Yes that is absolutely fine.
I said when he takes the string(the where clause in this case) as a character type IN parameter and later concatenates it with the rest of the sql statement in the EXECUTE IMMEDIATE, then NUMBER as a data type for that parameter will be an incorrect datatype.
|
|
|
|
| Re: Passing Multiple Parameters to Cursors? [message #587822 is a reply to message #587817] |
Tue, 18 June 2013 15:58 |
 |
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Not entirely true, although I agree with you - yes, it would be incorrect, but Oracle would try to perform (and succeed!) implicit datatype conversion, so - after all - it *might* turn out right. Which doesn't mean that people shouldn't take care about datatype matching.
An example:SQL> create table test
2 (char_col varchar2(20));
Table created.
SQL> insert into test (char_col) values ('ABC123');
1 row created.
SQL> insert into test (char_col) values ('DEF123');
1 row created.
SQL>
A procedure which would, in EXECUTE IMMEDIATE, accept NUMBER and return the result correctly:
SQL> create or replace procedure prc_test (par_in in number) is
2 l_str varchar2(500);
3 l_cnt number;
4 begin
5 l_str := 'select count(*) from test ';
6 l_str := l_str || 'where char_col = ''ABC' || par_in ||'''';
7
8 dbms_output.put_line(l_str);
9
10 execute immediate l_str into l_cnt;
11 dbms_output.put_line('Found ' || l_cnt || ' records');
12 end;
13 /
Procedure created.
Test:
SQL> exec prc_test (123);
select count(*) from test where char_col = 'ABC123'
Found 1 records
PL/SQL procedure successfully completed.
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
