Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    26

    Question Unanswered: How to pass Character parameter to a function

    hi,
    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

    return(M_load_MZEC_Peak_Min_Time(:r_date, eak_time, 'BRKP9L5'||','||'BRKPRUMN' ));

    I also tried with this but same problem

    return(M_load_MZEC_Peak_Min_Time(:r_date, eak_time, ''''||'BRKPRUMN'||''''||','||''''||'BRKP9L5'||'''' ));


    *****************Function IS**********

    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;
    begin
    select nvl(sum(load_mw),0) as load_Mwatt
    into M_load
    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;

    return(M_load);
    end ;

    Some one clear me what is wrong.
    Thanks and Regards.
    Khawar

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Have a look at this link

    .

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.
    Code:
    CREATE OR REPLACE FUNCTION Fun_Dept 
      (par_d1 IN CHAR DEFAULT '0', 
       par_d2 IN CHAR DEFAULT '0'
      )
    RETURN NUMBER
    IS
      l_num_employees NUMBER;
    BEGIN
      SELECT COUNT(*) INTO l_num_employees
        FROM EMP e
        WHERE (e.deptno = par_d1
               OR
               e.deptno = par_d2
              );
      RETURN (l_num_employees);
    END;
    /
    Now let's test it:
    Code:
    SQL> select fun_dept('10', '20') no_employees from dual;
    
    NO_EMPLOYEES
    ------------
               8
    
    SQL> select fun_dept('10') no_employees from dual;
    
    NO_EMPLOYEES
    ------------
               3
    
    SQL> select fun_dept no_employees from dual;
    
    NO_EMPLOYEES
    ------------
               0
    
    SQL>

  4. #4
    Join Date
    Aug 2004
    Posts
    26

    Thanking you to figure out

    Hi,
    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.
    Regards. Khawar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •