Registering PL/SQL Stored Procedure in Oracle Apps.

Registering PL/SQL Stored procedure in Oracle Apps

Now we are going to learn about how to register the PL/SQL stored procedure in Apps.

Step1:- Create a procedure

create or replace procedure emp_test(p_empno         number,

                                                                        errbuf      out   varchar2,

                                                                         retcode   out   number)

 As

            v_empno  number;

            v_ename  varchar2(10);

v_salary number;

            v_deptno number;

            l_error varchar2(100);

                        l_retcode number;

           Begin

select empno,ename,sal,deptno into v_empno,v_ename,v_salary, v_deptno
from emp  
where empno = p_empno;

Dbms_Output.put_line('Empno:- ' ||v_empno||' Name:- '||v_ename||' Salary:- '||v_salary||'   Department:- '||v_deptno);

   

Exception

                        When Others Then

                        l_error := 'error while executing the procedure '||SQLERRM;

                        errbuf :=l_error;

                        retcode := 1;

End;

Step2:- Login To Oracle Apps and create the executable.

             Navigation --- >  System Administrator => Cncurrent => Program =>  Executable

        
            Execution Method:- PL/SQL Stored Procedure.

            Execution File Name:- Give the procedure Name.

       

Step3:- Create the concurrent programm

             Navigation --- >  System Administrator => Cncurrent => Program =>  Define

     

           If there is any parameter then click on parameters tab. And provide the parameters.

Step4:-  Attach the concurrent program to a request group.

             
              Navigation --- >  System Administrator => Security => Responsibility => Request.

             
              Attach the concurrent program to request group on which responsibility u want.
     

           

Step5:- Run the concurrent program.

     
             Switch to Order Management responsibility.

             View => Requests => Submit.

            Click on Find it will show the status of the program.

            Now the procedure is called successfully.