Concurrent program to execute stored procedure in EBS

Creating Concurrent program to execute stored procedure


You have a PL/SQL procedure in your database and you want the user to be able to execute it from Oracle Apps front end.

To do so, we have to register the procedure in Oracle Apps. Now let’s see how to do that.

Step to execute PL/SQL Procedure.


1. Create package

2. Define set of procedure in Packages

3. Create Executable and Select method PL/SQL Stored Procedure

4. Create Concurrent program

5. Add this program to the required request group


If you want to register a procedure, the procedure should be created with the two below mandatory parameters:

errbuf : For printing out the error messages.
retcode: retcode return 0 or 1 or 2

return 0 for successful execution

return 1 to end the concurrent program in warning

return 2 to end the concurrent program in error

Package Specification


CREATE OR REPLACE package APPS.xxgst_asp_extraction_pkgas

PROCEDURE test;

procedure extractor_main(

errbuf OUT NOCOPY VARCHAR2,

retcode OUT NOCOPY VARCHAR2,

p_curr_sync_date IN VARCHAR2

end xxgst_asp_extraction_pkgas;

/

Package Body


CREATE OR REPLACE package body APPS. xxgst_asp_extraction_pkgas

PROCEDURE test is

 errbuf VARCHAR2(2000);

 retcode VARCHAR2(1);

 begin

 xxgst_asp_extraction_pkg.extractor_main(errbuf,retcode,'06-SEP-2017');

 end test;

procedure extractor_main(

 errbuf OUT NOCOPY VARCHAR2,

 retcode OUT NOCOPY VARCHAR2,

 p_curr_sync_date IN VARCHAR2

 ) IS

 l_last_sync_date DATE;

 l_curr_sync_seq_num VARCHAR2(10);

 begin

 /*

 Your logic for your procedure

 */ 

 extractor_core(l_last_sync_date, l_curr_sync_date, l_curr_sync_seq_num, l_sysdate, null);

 debug('End');

 

 EXCEPTION

 WHEN OTHERS THEN

 debug('Error: Exception in extractor_main: ' || sqlerrm);

 debug('Call Stack: ' || dbms_utility.format_call_stack);

 

 delete_extraction(errbuf, retcode, l_curr_sync_seq_num, null, null);

 retcode := 2;

 

 end extractor_main;

end xxgst_asp_extraction_pkg;

 

Creating Executable


Creating concurrent program


Assigning concurrent program into request group


Now You can run concurrent program

Leave a Reply

Your email address will not be published. Required fields are marked *