ORACLE DBA

Oracle Apps Concurrent Programs Basics

Lets first discuss the below scenario

Being a developer, you have just developed a SQL script or a PL/SQL package procedure. The end user wants to be able to run this script ad-hoc or they wish to schedule this to run every night.

Question : How can an end-user be given control to run a script developed by a developer, given that an end user will never have access to apps password(and rightly so)?
Answer: This script can be attached to a Concurrent Program via a concurrent program executable. The user will then be given access to this Concurrent Program.

Question : But how will the end user or Oracle Apps make this script run every 10hours daily?
Answer : A concurrent program can be scheduled to run at desired intervals. The schedule is defined at the time of submission.

Question: What are the different types of scripts/programs that can be attached to concurrent programs?
Answer :
A sql script
A Sql*Loader program
Java program
Oracle Report
Pl/SQL program ( or call it a stored procedure/package procedure)
Host script, like a unix shell script
Pro*C/Spawned
Perl

Question: What are the basic steps when defining a concurrent program.
Answer: Broadly speaking there are three steps when developing a concurrent program in Oracle Apps
Step 1. Make Oracle Apps identify the executable
Step 2. Provide a handle to the executable by means of defining a concurrent program
Step 3. Make this concurrent program accesible to selected users via their responsibility.

Question: Please can you describe the above steps in detail
Answer:
Step 1. Make Oracle Apps identify the executable
In Oracle Apps we have something called as concurrent program executable. Concurrent program executable is defined to register a script or a procedure for its usage within oracle apps.

Step 2. Provide a handle to the executable by means of defining a Concurrent Program.
We need to define a concurrent program and attach that to the executable defined in above step.

Step 3. Make this concurrent program accesible to selected users via their responsibility.
We do this by adding the concurrent program to something called as request group. The request group is either associated with a responsibility or is passed in as a parameter to the function request form function. Don't worry if you do not understand this step, I will be writing a dedicated article to explain this step.

Question : Please explain the steps for defining a pl/sql concurrent program, with screenshots and with almost real life example?
Answer:
1. Create a table and a stored procedure in pl/sql
create table xx_hello_world_tab
(
message_text VARCHAR2(100)
,creation_date DATE
) ;

CREATE OR REPLACE PROCEDURE
xx_register_user_prc(errbuf OUT VARCHAR2,retcode OUT VARCHAR2) IS
BEGIN
INSERT INTO xx_hello_world_tab VALUES ('Hello World' ,SYSDATE);
END xx_register_user_prc;

SELECT * FROM xx_hello_world_tab ;
--Zero records will be returned here

2. Note the two parameters, namely errbuff and retcode
These parameters for used for the interaction between the concurrent program and the pl/sql Program. I suggest to the beginners not to spend much time on these parameters.

3. Define an executable attached to this procedure.
This can be done by navigating to the responsibility “Application Developer”, and selecting the menu /Concurrent/Executable