Pass multiple numeric values to a function

From: Ravi Gaur <ravigaur1_at_gmail.com>
Date: Thu, 12 Feb 2009 13:29:13 -0600
Message-ID: <289232290902121129n58403d33r6a2ad1b091e150b6_at_mail.gmail.com>

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

Received on Thu Feb 12 2009 - 13:29:13 CST