Oracle APPS Java Application with Shell Concurrent Program

Share Button


In my previous tutorial, I did an example about Java Concurrent Program with External Jars. In this tutorial, I will develop a simple java application which generates an Excel(xlsx) file and run it as a shell concurrent program in Oracle EBS.

First of all, we need to understand the differences between java concurrent program and shel concurrent program for custom java applications.

Java Concurrent Program  Java Application With Shell Concurrent Program
JDBC is not required JDBC is required and must be manually configured
APPS Context is already initialized APPS Context should be manually initialized
Logging is not a problem Logging is a problem
Hard to configure Easy to configure
Database credentials are hidden Database credentials must be open

1.Since JDBC connection will be manually configured, the correct version of driver must be identified. In order to do this, check database version with following sql script.

2. Download jdbc driver by clicking to following link



3. Since java application will generate excel files, we need to download apache-poi API. Click to the following link

4. Open Netbeans and select “New Project” from menu.

sh-1 5. Choose Java Application and click to Next.

sh-2 6. Define project name and main class.

sh-3 7. Create a java class for jdbc connection.

sh-4 8. Define class name and package.

sh-5 9. Open project properties by right clicking to the project.

sh-6 10. Move on to the “Libaries” section. We need to add POI and jdbc jars into project classpath. Therefore click to the “Add Jar/Folder” button.

sh-711. Add all of the poi jars and ojdb6.jar file.

sh-8 12. Create one more class for excel operations.

sh-9 13. Define class name and package.

sh-1014. XXConnection: This class has two methods which return Connection and Oracle Connection. At line 21, Update connection string and credentials for your database.

14. Test the connection in main method.


15.After excel file is generated, it will be better to keep some information about transaction, request and file. To do that we need to create a database table with following script

16. ExcelOperations: Excel file is generated in this class. createExcel method takes sql query, file name, sheet name as parameters. Excel rows and columns are generated dynamically depending on sql query. Pay attention to disposing poi workbook in finally block. Since poi creates temporary xml files in /tmp folder, there is a possibility that there may not be free spaces anymore. This may cause EBS not working properly.

17. Do the following changes in Main class. Please pay attention that application will get parameters from main method arguments which will be sent by Shell Script.

18. Clean and Build the application and upload it into application server $PER_TOP/bin via FTP. Pay attention to uploading net beans project folder (with every other files inside it).

19. Write a shell script which has “.prog” extension. Pay attention to first four parameters are provided by concurrent program. These are:

$0: The shell script to be executed
$1: Oracle user/password
$2: Applications user_id
$3: Application user_name
$4: Concurrent program request_id

$5 and after $5 parameters are the parameters that defined in concurrent program.

20. Upload “prog” file into $PER_TOP/bin. We need to link the shell script. In order to this, execute the following unix command. If this operation is successful, a file with same name must be created in same path.

21. Give neccessary authorization to the file with CHMOD command.


22. At this point, file structure should be look like:

ftp 23. Define executable file: sh-12 24. Define concurrent program: SH-13 sh-14 sh-15 sh-16   25. Run the program and test it; sh-17 Not: If program ends with error like “File not found”, upload prog file via FTP by using BINARY mode.

26. Result:

sql_res   sh-18Source: http:/