call oracle function inside where clause

Question

Possible Duplicate:
Is there a function to split a string in plsql?

I have following oracle function

create or replace
function FUN_CUSTID(t in varchar2)
  RETURN VARCHAR2
IS
  TYPE str_array2 IS TABLE OF VARCHAR2(100);
  v_names str_array2;
  v_condition varchar2(1000);
BEGIN
   v_condition := '(';
   select REGEXP_SUBSTR(t, '[^ ,]+', 1, rownum) 
      bulk collect into v_names
      from DUAL
      connect by level <= length (regexp_replace(t, '[^ ,]+'))  + 1;

  FOR indx in v_names.FIRST..v_names.LAST LOOP 

    if (indx = v_names.LAST) then
     v_condition := v_condition || '''' || v_names(indx) ||'''';
    else
      v_condition := v_condition || '''' || v_names(indx) ||''',';
    end if;
  v_condition := v_condition || ')';
   END LOOP;

   return v_condition;
end FUN_CUSTID;
/

Now i want to call this function from where clause like

SELECT customer_id,
       name_remark,
       wbs_id,
       service_circuit_id,
       sum(actual_minutes) TOTAL_USAGE,
       min(first_connection_time) DATE_FROM,
       max(first_connection_time) DATE_TO,
       sum(amount) AMOUNT
  FROM temp
  FROM customer_id IN (SELECT FUN_CUSTID('CUST00001,CUST00002') FROM DUAL)
  GROUP BYcustomer_id, name_remark, wbs_id, service_circuit_id
  ORDER BY customer_id;

When i run this function directly and replace value in place of function call in upper query its work perfect with some records but upper query could not work.

How to call function in where clause ?

Your function just generates a string containing lots of quote caharacters. What you want to do is turn your original string into a "table" of elements. There are many ways to do this: the most appropriate depends on your version of Oracle and otehr considerations. See the answers in the question I've linked to. — APC, Sep 10 '12 at 15:44
are you looking for a way to pass in a comma separated list to SQL ?(I'm assuming you are trying to get your function to return an array of elements from the original csv string). — tbone, Sep 10 '12 at 16:24

score 0 · Answer 1 · answered Sep 10 '12 at 16:07

Your function generates its output in v_condition as ('A','B',.....) this is one whole string for Oracle in the select query and will not work with IN. Consider using dynamic SQL for this. Oracle treats the output of the function as one singe value as ('A','B',.....) so the IN clause would be parsed as-

IN ('('A','B',.....)',...)

Which is not what you want. You want something like below

IN ('A','B',.....)

And this can be achieved by dynamic SQL.

call oracle function inside where clause

1 Answers1