Re: Pass multiple numeric values to a function
- From: Ravi Gaur <ravigaur1@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Thu, 12 Feb 2009 14:38:36 -0600
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:
