Re: Pass multiple numeric values to a function

    I have a lot of things to say about your procedure.

1. What do you want to do with the string you return? Feed it to another function that does something similar? I'm very suspicious of functions in general (from experience)
2. DISTINCT and a 7 way join are for me a big red flag. I don't see the reason why you join with acc_procedure and procedure, for instance. Is it to check that there are matching rows? Have you foreign keys? I'm not sure that the design is really, really clean.
3. The best way to concatenate a string is, IMHO, sys_connect_by_path(). Example here http://www.gennick.com/magic.html, in which I had a hand.
4. The solution to your particular problem is to proceed as follows:

SQL> select distinct to_number(substr(nums, 1, instr(nums, ',') - 1)) num   2 from (select n, substr(val, 1 + instr(val, ',', 1, n)) nums

  3         from (select rownum as n,
  4              list.val
  5           from  (select ',1,2,3,65,98,23,76,' val
  6              from dual) list
  7           connect by level < length(list.val)
  8                   - length(replace(list.val, ',', ''))))

       NUM

     1
     2
     3

7 rows selected.

Use the preceding in either an IN (query) or a join.  Note that there are separators both at the beginning and at the end of the string that contains the values. It's a trick to simplify processing.

HTH S Faroult

Ravi Gaur wrote:
> One of our developers is trying to write a function that accepts
> multiple values (all numbers) that he can use inside the IN clause of
> the query.
>
> The function code is as shown below --
>
> CREATE OR REPLACE FUNCTION test_123 (pBB_ID_LIST VARCHAR2)
> RETURN VARCHAR2
> IS
> vspcm_typ_nm_uc VARCHAR2 (2000);
>
> CURSOR stname
> IS
> SELECT distinct st.spcm_typ_nm_uc
> FROM
> acc_blackbar abb,
> acc_specimen_type ast, acc_procedure apr,
> acc_specimen_part asp,
> specimen_type st, procedure pr,
> proc_proc_family ppf
> WHERE abb.ACC_BLKBR_ID = apr.ACC_BLKBR_ID
> AND apr.ACC_SPCM_PART_ID = asp.ACC_SPCM_PART_ID
> AND asp.ACC_SPCM_TYP_ID = ast.ACC_SPCM_TYP_ID
> AND ast.ACC_SPCM_TYP_SPCM_TYP_ID = st.SPCM_TYP_ID
> AND apr.procdr_id = pr.PROCDR_ID
> AND pr.PROCDR_ID = ppf.PROCDR_ID
> AND abb.acc_blkbr_id in (pBB_ID_LIST);
>
> BEGIN
> DBMS_OUTPUT.PUT_LINE(' BB LIST : ' || pBB_ID_LIST);
> FOR st IN stname
> LOOP
> vspcm_typ_nm_uc := vspcm_typ_nm_uc || ',' ||
> st.spcm_typ_nm_uc;
> END LOOP;
> vspcm_typ_nm_uc := SUBSTR (vspcm_typ_nm_uc, 2);
> RETURN vspcm_typ_nm_uc;
> END;
> /
>
> It works ok for a single value but returns the following error while
> passing 2 values:
> select test_123('199307885008,199307885006') FROM DUAL;
> select test_123('199307885008,199307885006') FROM DUAL
> *
> ERROR at line 1:
> ORA-01722: invalid number
> ORA-06512: at "PATHWAY_RELEASE.TEST_123", line 8
> ORA-06512: at "PATHWAY_RELEASE.TEST_123", line 25
>
>
> He has also tried table types without success. Any help is deeply
> appreciated.
>
> Thanks,
> - Ravi Gaur
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l