Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Copyright (c) 2021, Oracle. All rights reserved. Oracle Confidential.
How to Run an .sql Script as Concurrent Request in E-Business Suite (Doc ID 1575955.1)
In this Document
Goal
Solution
1) Copy the script into the source ($PRODUCT_TOP/sql) directory
2) Modify script_name.sql script
3) Create a new Executable (SCRIPT_SQL)
4) Define a new Concurrent Program (Data Collection Script)
5) Add the new Script Program to the Responsibility's Request Group.
6) Submit a new 'Data Collection Script' Concurrent Request and Verify the output.
APPLIES TO:
Oracle Concurrent Processing - Version 11.5.1 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
GOAL
What are the steps to register and configure an sql script to be executed as a standard Concurrent Request from Oracle
Applications.
Manual execution of the existing script requires a System Administrator or DBA user with access to SQL*Plus and
Applications Owner (APPS) password. The creation of the Concurrent Request needs to be performed by a System
Administrator; however, this will be a one time task. Once installed, end users with the proper Responsibility will have the
ability to execute the script as a single request, or schedule periodically without the need of SQL access or the Applications
Owner (APPS) password.
So instead of using:
sqlplus apps user/apps user password @script_name.sql
you can define a concurrent request to do this job.
SOLUTION
For the purpose of registering an SQL*Plus program, you can use any PRODUCT_TOP.
For example if it related to Human Resources you can use $PER_TOP (Human Resources). If the script is related to Fixed
Assets, you can use $FA_TOP.
The same steps can be followed to create and register a SQL*Plus program in custom top as well.
Please review " Using the Custom Library" Chapter in Oracle Applications Developer's Guide for more details.
1) Copy the script into the source ($PRODUCT_TOP/sql) directory
1 of 6 03/11/2021, 13:19
Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Logon to the Middle Tier as the applmgr account (or any user that has permissions on the Application files),
and put the .sql file.
$ cd $PRODUCT_TOP/sql
$ ls -lart script_name.sql
-rw-r--r-- 1 <SID> dba 105788 Aug 1 07:57 script_name.sql
2) Modify script_name.sql script
Ensure that you remove/comment any SPOOL command as below:
From:
SPOOL &spoolFile
To:
REM SPOOL &spoolFile
ie spool command should be commented out for Concurrent program to generate the output file.
3) Create a new Executable (SCRIPT_SQL)
Logon to Oracle Applications as System Administrator Responsibility and navigate to Concurrent >
Program > Executable
Executable SCRIPT_SQL
Short Name SCRIPT_SQL
Application depending on the usage, for example Human Resources (Use the LOV)
Description Oracle Script
Execution Method SQL*Plus
Execution File Name script_name (Note: Do not include .sql Extension)
2 of 6 03/11/2021, 13:19
Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Click on the to Save the changes.
4) Define a new Concurrent Program (Data Collection Script)
Navigate to Concurrent > Program > Define
Program Data Collection Script
Enabled Checked
Shortname SCRIPT_SQL
depending on the usage, for example
Application
Human Resources (Use the LOV)
Description Data Collection Script
Executable Section
Name SCRIPT_SQL
Method SQL*Plus
Output Section
Format HTML/Text (as you need)
Style Portrait (or as you need)
Leave all others as default
3 of 6 03/11/2021, 13:19
Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Click on the to Save the changes.
Optional Note: If the script has parameters, ie:
sqlplus apps user/apps user password @script_name.sql
parameter1 parameter 2
then you need to define parameters also:
Define parameters needed for Concurrent request
Click on Parameters button
Seq 10 20
Parameter Parameter 1 Parameter 2
Description Parameter 1 Description Parameter 2 Description
Enabled Yes Yes
choose a value set as choose a value set as
Value Set
needed needed
Display Yes Yes
Display
20 20
Size
Leave all others as default
5) Add the new Script Program to the Responsibility's Request Group.
Navigate to System Administrator > Security > Responsibility > Request
Select the responsibility, for example which we have been using (or the responsibility you chose)
Using the Group Name Field, query for responsibility.
In the list of requests section, click to add a new row to the record as per following :
Type Program
Name Data Collection Script
depending on the usage, for example Human
Application
Resources
4 of 6 03/11/2021, 13:19
Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Click on the to Save the changes.
6) Submit a new 'Data Collection Script' Concurrent Request and Verify the output.
As the responsibility used, for example XX HRMS Manager Responsibility, navigate to Process and Reports
> Submit Processes and Reports
Click to Find the Concurrent Program 'Data Collection Script' and submit.
5 of 6 03/11/2021, 13:19
Document 1575955.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state...
Once the request completes click on View Output and verify you can see the output.
Didn't find what you are looking for?
6 of 6 03/11/2021, 13:19
