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.
END;
/
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-lReceived on Thu Feb 12 2009 - 13:29:13 CST