Hi! In this post, I will show you how to pass multiple values separated by comma in concurrent program parameter in Oracle apps.
To achieve the same requirement in the database, we can use lexical parameter like below. It retrieves the department names based on the department numbers that we pass in the p_deptno parameter.
SELECT dname "Department" FROM dept WHERE deptno IN (&p_deptno);
However, the above format won't work in concurrent program parameter but it can be achieved using REGEXP. The below code shows a sample procedure that uses a cursor with SELECT query to achieve the same requirement that can be used in concurrent programs.
To pass multiple values separated by commas in Concurrent program parameter:
CREATE OR REPLACE PROCEDURE getDept (p_deptno IN VARCHAR2)
AS
CURSOR get_dept_cur IS SELECT dname FROM dept WHERE deptno IN
(SELECT REGEXP_SUBSTR(UPPER(p_deptno), '[^,]+', 1, level) FROM dual
CONNECT BY REGEXP_SUBSTR(p_deptno, '[^,]+', 1, level) IS NOT NULL);
BEGIN
FOR i IN get_dept_cur LOOP
dbms_output.put_line(i.dname);
END LOOP;
END;
/
EXEC getDept('10,20,30,40');
Hope this helps. Happy learning!
