Creating a PL/SQL Concurrent Program in Oracle Applications
===========================================================
This note describes the basic steps to set-up PL/SQL programs as Standalone concurrent program in Oracle Applications
NOTE - Although this article has been prepared on a Release 11, the steps for 10.7 , R12 will be similar.
Introduction
============
These steps will create a package with four procedures.
The first procedure is a simple standalone procedure that just writes some messages to the log file.
Secondly create a procedure that calls another procedure as a child process.
Set-up a procedure as a standalone process that accepts a parameter and outputs the entered parameter to the log file.
1) Create custom application
============================
Customizations should be created in a separate area in a custom application.
This process relies on a custom table created as described below:
create table mzRunTable (source_name varchar2(20), status varchar2(20), date_created date);
2) Create and load PL/SQL program
=================================
Create the following two files:
a) mzRunS.pls
+++ START OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package mzConcTest is
/** Header - Test package from Oracle Support note 73492.1 - Version 1.0.0**/
procedure mzFirst(errbuf out varchar2, retcode out varchar2);
procedure mzCallMe(errbuf out varchar2, retcode out varchar2);
procedure mzMain(errbuf out varchar2, retcode out varchar2);
procedure mzParameter(errbuf out varchar2, retcode out varchar2, mzVar in varchar2 );
end mzConcTest ;
/
show errors
commit;
+++ END OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
b) mzRunB.pls
+++ START OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
create or replace package body mzConcTest as
procedure mzFirst(errbuf out varchar2, retcode out varchar2) as
mzVar varchar2(20);
begin
mzVar := 'OK';
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzFirst procedure '|| mzVAR);
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
end mzFirst;
procedure mzCallMe(errbuf out varchar2, retcode out varchar2) as
begin
fnd_file.put_line(FND_FILE.LOG,'mzCallMe procedure');
retcode := 0;
insert into mzRunTable
(source_name
,status
,date_created)
values
('MZCALLME'
,'WORKING OK'
,sysdate);
commit;
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
exception
when others then
retcode := 4;
fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
end mzCallMe;
procedure mzMain(errbuf out varchar2, retcode out varchar2) as
l_errbuf varchar2(2000) := '' ;
l_retcode number(1) := 0;
l_phase varchar2(2000) := '' ;
l_status varchar2(2000) := '' ;
l_dev_phase varchar2(2000) := '' ;
l_dev_status varchar2(2000) := '' ;
l_message varchar2(2000) := '' ;
l_request_id number := 0;
l_get_request_status boolean ;
begin
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzMain procedure');
l_request_id := fnd_request.submit_request('FND', 'MZCALLME', 'Mikes MZCALLME routine');
commit;
if l_request_id = 0 then
l_errbuf := 'Request id is zero' ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
retcode := 1;
else
l_errbuf := 'Request id '||to_char(l_request_id) ;
FND_FILE.PUT_LINE(fnd_file.log, l_errbuf);
l_errbuf := 'Monitoring request '||to_char(l_request_id) ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
l_get_request_status := fnd_concurrent.wait_for_request(l_request_id, 60, 0, l_phase, l_status, l_dev_phase, l_dev_status, l_message);
if l_dev_status = 'ERROR' then
l_errbuf := 'Import failed with message '||l_message ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
retcode := 2;
else
l_errbuf := 'Request finished with status '||l_status ;
FND_FILE.PUT_LINE(FND_FILE.log,l_errbuf);
end if ;
end if;
fnd_file.put_line(FND_FILE.LOG, 'COMPLETED RUN. Retcode = '|| retcode);
exception
when others then
retcode := 3;
fnd_file.put_line(FND_FILE.LOG, 'ABORTED RUN. Retcode = '|| retcode);
end mzMain ;
procedure mzParameter(errbuf out varchar2, retcode out varchar2, mzVar in varchar2) as
begin
retcode := 0;
fnd_file.put_line(FND_FILE.LOG,'mzParameter procedure');
fnd_file.put_line(FND_FILE.LOG,'Parameter received was: '|| mzVar);
fnd_file.put_line(FND_FILE.LOG,'Retcode = '|| retcode);
end mzParameter;
end mzConcTest ;
/
show errors
commit;
+++ END OF SCRIPT ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now run mzRunS.pls then mzRunB.pls against the database. It was loadied against the APPS schema,
but one should load them into the Custom Schema and create appropriate grants and synonyms if required.
The files above compile successfully against an Oracle 8.0.4 database. If any errors are received,
check the source files for incorrect spacing, spelling or extra carriage returns.
3) Set-up concurrent program and test results
=============================================
For this section it is assumed that a custom application called 'PLSQL Test' was created.
Enter whatever the custom application is called where 'PLSQL Test' is mentioned in the following set-up steps.
Set-up three separate tests one at a time.
Test One - simple standalone procedure
--------------------------------------
As System Administrator do the following:-
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzFirst
Short Name = mzFirst
Application = PLSQL Test
Description = First Test
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzFirst
b) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzFirst
Short name = mzFirst
Application = PLSQL Test
Description = First Test
Executable Name = mzFirst
(Leave all other settings as the default)
c) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = First Test
Requests Type = Program
Requests Name = mzFirst
Requests Application = PLSQL Test
d) Now log in to the user/responsibility that has access to the 'PLSQL Test'
request group and run the 'mzFirst' request. Monitor the request which should complete with 'Normal' status.
There will not be an output file, so just look at the Log file that has been generated.
It should look similar to that listed below:
+---------------------------------------+
MZFIRSTPLSQL module: mzFirst
Start of log messages from Plsql program
+---------------------------------------+
mzFirst procedure OK
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
Test Two - Procedure that spawns another procedure as a child concurrent process
--------------------------------------------------------------------------------
As System Administrator do the following:-
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzMain
Short Name = mzMain
Application = PLSQL Test
Description = Calling PLSQL procedure
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzMain
b) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzCallMe
Short Name = mzCallme
Application = PLSQL Test
Description = Called PLSQL procedure
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzCallme
c) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzMain
Short name = mzMain
Application = PLSQL Test
Description = Calling PLSQL procedure
Executable Name = mzMain
(Leave all other settings as the default)
d) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzCallMe
Short name = mzCallMe
Application = PLSQL Test
Description = Calling PLSQL procedure
Executable Name = mzCallMe
(Leave all other settings as the default)
e) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = Calling PLSQL procedure
Requests Type = Program
Requests Name = mzMain
Requests Application = PLSQL Test
d) Now log in to the user/responsibility that has access to the 'PLSQL Test' request group and run the 'mzMain' request.
Monitor the request which should first spawn the 'mzCallme' request,
then once this child process has completed 'Normal' the 'mzMain' process will also complete with 'Normal' status.
There will not be an output file, so just look at the Log file for the two concurrent processes.
They should look similar to that listed below:-
+---------------------------------------+
MZMAIN module: mzMain
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzMain procedure
Request id 6428
Monitoring request 6428
Request finished with status Normal
COMPLETED RUN. Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
+---------------------------------------+
MZCALLME module: mzCallMe
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzCallMe procedure
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+
Test Three - standalone procedure with passes parameter
-------------------------------------------------------
As System Administrator do the following:
a) Setup the Executable. Concurrent-->Program-->Executable
Executable = mzParameter
Short Name = mzParameter
Application = PLSQL Test
Description = Procedure with parameter
Execution Method = PL/SQL Stored Procedure
Execution File Name = mzConcTest.mzParameter
b) Define the Concurrent Program. Concurrent-->Program-->Define
Program = mzParameter
Short name = mzParameter
Application = PLSQL Test
Description = Procedure with parameter
Executable Name = mzParameter
(Leave all other settings as the default)
Click on 'Parameters' button and enter the following:-
Seq = 1
Parameter = mzVar
Description = Text sent to log file
Value Set = 15 Characters
(Leave all other settings as the default)
c) Add Concurrent Program to Concurrent Request Group. Security-->Responsibility-->Request
Group = PLSQL Test
Application = PLSQL Test
Code =
Description = First Test
Requests Type = Program
Requests Name = mzParameter
Requests Application = PLSQL Test
d) Now login to the user/responsibility that has access to the 'PLSQL Test'
request group and run the 'mzParameter' request. When selected it should pop up a window to ask to enter
the 'mzVar' parameter value. Enter 'This is a test' as the text. Monitor the request which should complete with 'Normal' status.
The parameter entered will be seen on the 'View Requests' window in the 'Parameters' column.
There will not be an output file, so just look at the Log file that has been generated.
It should look similar to that listed below:
+---------------------------------------+
MZPARAMETER module: mzParameter
+---------------------------------------+
Start of log messages from Plsql program
+---------------------------------------+
mzParameter procedure
Parameter received was: This is a test
Retcode = 0
+---------------------------------------+
End of log messages from PlSql program
+---------------------------------------+