Unanswered: How to pass Character parameter to a function
I have one function which calculate amount return number, but the problem is this if I sent parameter for IN clause with one parameter it works but if I sent two parameter like this IN('ab','cd') doest not work.
I am sending parameter like this
M_Load_MZEC_Peak_Min_time( p_r_date date, p_r_time number, p_feeder_code varchar2) RETURN number IS
M_load number := 0;
select nvl(sum(load_mw),0) as load_Mwatt
from feeder_load f
where feeder_code in ( p_feeder_code )
and to_date(to_char(f.r_date,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date(to_char(p_r_date,'dd/mm/yyyy'),'dd/mm/yyyy')
and f.r_time = p_r_time;
Some one clear me what is wrong.
Thanks and Regards.
I admit, I didn't read what Tom Kyte said about it, but here it is: if you always pass not more than two parameter values, you could actually CREATE two parameters and use them in the WHERE clause separately. As it is the IN operator, it could be substituted with OR. Check this example: I used default values (0) so that you could call the function with one/two/without parameters.
CREATE OR REPLACE FUNCTION Fun_Dept
(par_d1 IN CHAR DEFAULT '0',
par_d2 IN CHAR DEFAULT '0'
SELECT COUNT(*) INTO l_num_employees
FROM EMP e
WHERE (e.deptno = par_d1
e.deptno = par_d2
Now let's test it:
SQL> select fun_dept('10', '20') no_employees from dual;
SQL> select fun_dept('10') no_employees from dual;
SQL> select fun_dept no_employees from dual;
Thanks for your reply guys, what I have decided is to go with passing two parameter(because in any case I don't have more than two) separately because Tom's Solution is Complex.
Thanks to all of you for helping me figure out this problem.