PL/SQL Stored Procedure registration with Parameters

Step1) Create Procedure with parameters

Package Specification:
CREATE OR REPLACE PACKAGE APPS.H_EMP_PKG
AS
   PROCEDURE H_EMP_DATA_PARAM (ERRBUF             OUT VARCHAR2,
                               RETCODE            OUT VARCHAR2,
                               P_FROM_DEPTNO   IN     NUMBER,
                               P_TO_DEPTNO     IN     NUMBER);
END H_EMP_PKG;

Package Body:
CREATE OR REPLACE PACKAGE BODY APPS.H_EMP_PKG
AS
   PROCEDURE H_EMP_DATA_PARAM (ERRBUF             OUT VARCHAR2,
                               RETCODE            OUT VARCHAR2,
                               P_FROM_DEPTNO   IN     NUMBER,
                               P_TO_DEPTNO     IN     NUMBER)
   IS
      CURSOR C_EMP
      IS
         SELECT EMPNO,
                ENAME,
                SAL,
                HIREDATE,
                DEPTNO
           FROM EMP
          WHERE DEPTNO BETWEEN P_FROM_DEPTNO AND P_TO_DEPTNO;
   BEGIN
      FOR C_EMP_REC IN C_EMP
      LOOP
         FND_FILE.PUT_LINE (
            FND_FILE.OUTPUT,
               RPAD (C_EMP_REC.EMPNO, 3)
            || ' -- '
            || RPAD (C_EMP_REC.ENAME, 15)
            || ' -- '
            || RPAD (C_EMP_REC.SAL, 10)
            || ' -- '
            || RPAD (C_EMP_REC.HIREDATE, 10)
            || ' -- '
            || C_EMP_REC.DEPTNO);
      END LOOP;
   END H_EMP_DATA_PARAM;
END H_EMP_PKG;
/

Step2) Create Executable
Navigation : Application Developer --> Concurrent --> Executable
Give Execution File Name as Package.Procedure name.

PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps


Step3) Create Concurrent Program
Navigation : Application Developer --> Concurrent --> Program

PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps

 Step4) Click on Parameters button and give parameters in the order same as in the procedure. No need to add errbuf and retcode parameters.


PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps

Step5) Assign Program to Request group.
 Navigation : System Administrator --> Security --> Responsibility --> Request


PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps

  Step6) Run program from Responsibility

PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps

 Give the department number to get the employee information.


PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps

Submit the program and the output is


PL/SQL Stored Procedure registration with Parameters, askhareesh blog for Oracle Apps



*/