Sample example to use Ref cursor in Oracle Apps for beginners

Hi, This post is to show you a sample example on how to use ref cursor in Oracle apps R12 which will be helpful for beginners. Below code shows an example of using Ref cursor.

CREATE OR REPLACE PROCEDURE RETURNING_REFCURSOR(

                   P_EMPNO EMP05.EMPNO%TYPE,

                  L_REFCURSOR OUT SYS_REFCURSOR) IS

L_ENAME EMP05.ENAME%TYPE;

L_DNAME DEPT05.DNAME%TYPE;

BEGIN

OPEN L_REFCURSOR FOR SELECT E.ENAME,D.DNAME FROM EMP05 E,DEPT05 D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = P_EMPNO;

LOOP

FETCH L_REFCURSOR INTO L_ENAME,L_DNAME;

EXIT WHEN L_REFCURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(L_ENAME||' '||L_DNAME);

END LOOP;

CLOSE L_REFCURSOR;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END RETURNING_REFCURSOR;

/

SET SERVEROUTPUT ON

VARIABLE G_REF REFCURSOR

VARIABLE G_EMPNO NUMBER

EXECUTE :G_EMPNO := 7839

EXECUTE RETURNING_REFCURSOR(:G_EMPNO,:G_REF)

Happy learning!!

How to pass multiple values to a parameter in concurrent program in Oracle apps

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!