Re: Pass multiple numeric values to a function

Thanks a bunch, Stephane!
That was quite helpful. I've no idea what the developer intends to do with
the output but the connect by query did help him.

- Ravi Gaur


On Thu, Feb 12, 2009 at 1:51 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:

> Ravi,
>
>    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, ',', ''))))
>  9  /
>
>       NUM
> ----------
>     1
>     2
>     3
>    65
>    98
>    23
>    76
>
> 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
> >
> >
> >
> >
> >
>
>
>

Other related posts: