Deploy Servlet in Oracle EBS 12.2
12 min readFeb 18, 2023
In the dynamic realm of enterprise applications, effective integration of web technologies is crucial for organizations seeking to enhance their business processes and optimize efficiency. Oracle E-Business Suite (EBS) has long been recognized as a powerful and comprehensive software suite that enables businesses to streamline their operations. With its wide array of modules, EBS offers robust solutions for managing various aspects of an enterprise, ranging from finance and supply chain to human resources and customer relationship management.
One of the key strengths of Oracle EBS lies in its extensibility and flexibility, allowing businesses to customize and extend the suite’s functionality to meet their specific requirements. A common approach to achieving this customization is by leveraging servlets, which are Java-based components that facilitate dynamic content generation and enable seamless integration of web applications within EBS.
1. Java Servlet Development
App.java
package com.myapp.web;
// Public Imports
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import java.io.IOException;
// Servlet File Upload
import org.apache.commons.fileupload.servlet.ServletFileUpload;
// Private Package Imports
import NCB.myapp.controller.AppController;
import NCB.myapp.controller.FileController;
public class App extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
AppController app = new AppController(request, response);
app.processRequest();
}
@Override
protected void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
if ( ServletFileUpload.isMultipartContent(request) ) {
FileController fpp = new FileController(request, response);
fpp.processRequest();
} else {
AppController app = new AppController(request, response);
app.processRequest();
}
}
@Override
protected void doPut(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
if ( ServletFileUpload.isMultipartContent(request) ) {
FileController fpp = new FileController(request, response);
fpp.processRequest();
} else {
AppController app = new AppController(request, response);
app.processRequest();
}
}
@Override
protected void doDelete(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
AppController app = new AppController(request, response);
app.processRequest();
}
}AppController.java
package com.myapp.controller;
/* HTTP Support */
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletOutputStream;
/* IO Support */
import java.io.Reader;
import java.io.BufferedReader;
import java.io.PrintWriter;
import java.io.IOException;
import java.lang.StringBuffer;
import java.util.Enumeration;
/* JSON Support */
import org.json.JSONObject;
import org.json.JSONException;
/* SQL Support - From Ebsuite Environment */
import oracle.apps.fnd.common.WebRequestUtil;
import oracle.apps.fnd.common.WebAppsContext;
import oracle.jdbc.OracleCallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Clob;
public class AppController {
public HttpServletRequest httpRequest = null;
public HttpServletResponse httpResponse = null;
PrintWriter printWriter = null;
public AppController(HttpServletRequest request,
HttpServletResponse response) {
httpRequest = request;
httpResponse = response;
}
public JSONObject getObjectFromResponse(Clob responseFromDB)
throws SQLException, JSONException, IOException {
StringBuilder sb = new StringBuilder();
Reader reader = responseFromDB.getCharacterStream();
// Throwing SQLException
BufferedReader br = new BufferedReader(reader);
// Throwing IOException
String lineClob;
while (null != (lineClob = br.readLine()))
sb.append(lineClob);
JSONObject respJson = new JSONObject(sb.toString());
// Throws JSONException
if (respJson.has("status")) {
httpResponse.setStatus((int) respJson.get("status"));
} else {
httpResponse.setStatus(200);
}
if (respJson.has("ContentType")) {
httpResponse.setContentType(respJson.getString("ContentType"));
} else {
httpResponse.setContentType("application/json");
}
return respJson;
}
public void processRequest() {
httpResponse.setContentType("application/json");
JSONObject requestData;
Clob responseFromDB;
JSONObject respJson = new JSONObject();
try {
printWriter = httpResponse.getWriter();
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC101");
respJson.put("message",
"IOException in getting response writer.");
printWriter.write(respJson.toString());
return;
}
try {
requestData = getRequestData();
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC102");
respJson.put("message", "JSONException in request data.");
printWriter.write(respJson.toString());
return;
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC103");
respJson.put("message", "IOException in request data.");
printWriter.write(respJson.toString());
return;
}
try {
responseFromDB = getResponseFromDB(requestData);
} catch (SQLException e) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC104");
respJson.put("message",
"SQLException in getting response from DB.");
respJson.put("errorMessage", e.getMessage());
printWriter.write(respJson.toString());
return;
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC105");
respJson.put("message", "IOException in request data.");
printWriter.write(respJson.toString());
return;
}
try {
respJson = getObjectFromResponse(responseFromDB);
} catch (SQLException e) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC106");
respJson.put("message",
"SQLException in reading character stream.");
printWriter.write(respJson.toString());
return;
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC107");
respJson.put("message", "JSONException in reading response.");
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMAPC108");
respJson.put("message", "IOException in reading response.");
}
if (respJson.has("ContentType")) {
String rvContentType = respJson.getString("ContentType");
if (rvContentType != "application/json"
&& respJson.has("RawData"))
{
String rawData = respJson.getString("RawData");
printWriter.write(rawData);
printWriter.flush();
return;
}
}
printWriter.write(respJson.toString());
printWriter.flush();
}
public Clob getResponseFromDB(JSONObject requestData)
throws IOException, SQLException {
/* Start - Send Request Body to Database using WebAppsContext. */
WebAppsContext webAppsContext = null;
Connection connection = null;
webAppsContext = WebRequestUtil.createWebAppsContext(httpRequest,
httpResponse); // throws IO
connection = webAppsContext.getJDBCConnection();
Clob data = null;
String query = "begin XXX_REST_API_PKG.REST_API(:1, :2); END;";
OracleCallableStatement stmt = (OracleCallableStatement) connection
.prepareCall(query);
stmt.setString(1, requestData.toString());
stmt.registerOutParameter(2, java.sql.Types.CLOB);
stmt.execute();
data = stmt.getClob(2);
return data;
}
public JSONObject getRequestData() throws IOException {
JSONObject requestData = new JSONObject();
JSONObject headerData = new JSONObject();
JSONObject queryData = new JSONObject();
Enumeration headerNames = httpRequest.getHeaderNames();
while (headerNames.hasMoreElements()) {
String paramName = (String) headerNames.nextElement();
headerData.put(paramName, httpRequest.getHeader(paramName));
}
requestData.put("Method", httpRequest.getMethod());
requestData.put("ContextPath", httpRequest.getContextPath());
requestData.put("Header", headerData);
requestData.put("ContentLength", httpRequest.getContentLength());
requestData.put("ServerPort", httpRequest.getServerPort());
requestData.put("isSecure", httpRequest.isSecure());
requestData.put("RemoteHost", httpRequest.getRemoteHost());
requestData.put("RemoteAddr", httpRequest.getRemoteAddr());
requestData.put("ServletPath", httpRequest.getServletPath());
requestData.put("RequestURI", httpRequest.getRequestURI());
requestData.put("Protocol", httpRequest.getProtocol());
// Optional Request Information - May not be set if values null.
requestData.put("PathInfo", httpRequest.getPathInfo());
requestData.put("QueryString", httpRequest.getQueryString());
requestData.put("RemoteUser", httpRequest.getRemoteUser());
requestData.put("RequestedSessionId",
httpRequest.getRequestedSessionId());
requestData.put("Controller", "AppController");
// If Request Method is GET -> Pass Query Params to DB in Request
if (httpRequest.getMethod() == "GET"){
Enumeration queryNames = httpRequest.getParameterNames();
while (queryNames.hasMoreElements()) {
String paramName = (String) queryNames.nextElement();
queryData.put(paramName, httpRequest.getParameter(paramName));
}
requestData.put("Query", queryData);
}
// Fetching Body
StringBuffer buffer = null;
buffer = new StringBuffer();
String line = null;
BufferedReader bufferReader = null;
String body = null;
bufferReader = httpRequest.getReader();
while ((line = bufferReader.readLine()) != null) {
buffer.append(line);
}
body = buffer.toString();
requestData.put("Body", body);
bufferReader.close();
return requestData;
}
}FileController.java
package com.myapp.controller;
/* IO Support */
import java.io.File;
import java.io.Reader;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.PrintWriter;
import java.io.IOException;
import java.lang.StringBuffer;
import java.util.Enumeration;
import java.util.List;
/* Http Support */
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/* JSON Support */
import org.json.JSONArray;
import org.json.JSONObject;
import org.json.JSONException;
/* File Upload Support */
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
/* SQL Support - From Ebsuite Environment */
import oracle.apps.fnd.common.WebRequestUtil;
import oracle.apps.fnd.common.WebAppsContext;
import oracle.jdbc.OracleCallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Clob;
public class FileController {
public HttpServletRequest httpRequest = null;
public HttpServletResponse httpResponse = null;
PrintWriter printWriter = null;
// upload settings
private static final int MEMORY_THRESHOLD = 1024 * 1024 * 3; // 3MB
private static final int MAX_FILE_SIZE = 1024 * 1024 * 40;
private static final int MAX_REQUEST_SIZE = 1024 * 1024 * 50;
public FileController(HttpServletRequest request,
HttpServletResponse response) {
httpRequest = request;
httpResponse = response;
}
public JSONObject getFileAttributes(FileItem item) {
String fieldName = item.getFieldName();
String fileName = item.getName();
String contentType = item.getContentType();
boolean isInMemory = item.isInMemory();
long sizeInBytes = item.getSize();
JSONObject filejson = new JSONObject();
filejson.put("fieldName", fieldName);
filejson.put("fileName", fileName);
filejson.put("contentType", contentType);
filejson.put("isInMemory", isInMemory);
filejson.put("sizeInBytes", sizeInBytes);
// String rand = UUID.randomUUID().toString();
return filejson;
}
public JSONObject getObjectFromResponse(Clob responseFromDB)
throws SQLException, JSONException, IOException {
StringBuilder sb = new StringBuilder();
Reader reader = responseFromDB.getCharacterStream();
BufferedReader br = new BufferedReader(reader);
String lineClob;
while (null != (lineClob = br.readLine()))
sb.append(lineClob);
JSONObject respJson = new JSONObject(sb.toString());
return respJson;
}
public JSONObject getMultipartBodyObject(List<FileItem> formItems) {
JSONObject multipartBody = new JSONObject();
JSONArray filesBody = new JSONArray();
for (FileItem item : formItems) {
if (!item.isFormField()) { // If file
JSONObject filejson = getFileAttributes(item);
// String rand = UUID.randomUUID().toString();
filesBody.put(filejson);
} else {
String name = item.getFieldName();
String value = item.getString();
multipartBody.put(name, value);
}
}
multipartBody.put("files", filesBody);
return multipartBody;
}
public List<FileItem> getRequestFormItems() throws FileUploadException {
DiskFileItemFactory factory = new DiskFileItemFactory();
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setFileSizeMax(MAX_FILE_SIZE);
upload.setSizeMax(MAX_REQUEST_SIZE);
List<FileItem> formItems = upload.parseRequest(httpRequest);
return formItems;
}
public void processRequest() {
httpResponse.setContentType("application/json");
JSONObject respJson = new JSONObject();
try {
printWriter = httpResponse.getWriter();
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC101");
respJson.put("message", "IOException in response writer.");
printWriter.write(respJson.toString());
return;
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC102");
respJson.put("message", ex.getMessage());
printWriter.write(respJson.toString());
return;
}
JSONObject requestInfo;
try {
requestInfo = getRequestInfo();
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC103");
respJson.put("message", "JSONException in request data.");
printWriter.write(respJson.toString());
return;
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC104");
respJson.put("message", "IOException in request data.");
printWriter.write(respJson.toString());
return;
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC105");
respJson.put("message", ex.getMessage());
printWriter.write(respJson.toString());
return;
}
List<FileItem> formItems;
try {
formItems = getRequestFormItems();
} catch (FileUploadException exf) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC106");
respJson.put("message", "FileUploadException in request.");
printWriter.write(respJson.toString());
return;
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC107");
respJson.put("message", ex.getMessage());
printWriter.write(respJson.toString());
return;
}
if (formItems == null || formItems.size() <= 0) { // Return
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC108");
respJson.put("message", "Multipart items cannot be null.");
printWriter.write(respJson.toString());
return;
}
// Check if Middleware passes the request successfully.
// Sending request to server with Multipart = 1
Clob responseFromDB;
try {
JSONObject requestBody = getMultipartBodyObject(formItems);
requestInfo.put("Body", requestBody);
requestInfo.put("MultiPart", 1);
responseFromDB = getResponseFromDB(requestInfo);
respJson = getObjectFromResponse(responseFromDB);
} catch (SQLException e) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC109");
respJson.put("message", "SQLException from DB.");
printWriter.write(respJson.toString());
return;
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC110");
respJson.put("message", "JSONException in response.");
printWriter.write(respJson.toString());
return;
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC111");
respJson.put("message", "IOException in response.");
printWriter.write(respJson.toString());
return;
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC112");
respJson.put("message", ex.getMessage());
printWriter.write(respJson.toString());
return;
}
JSONArray fileIds = new JSONArray();
int uploadStatus = 0;
try {
if (respJson.has("midStatus")) {
if (respJson.get("midStatus") == 1) {
requestInfo.remove("MultiPart");
// Request has passed Middleware check.
// Save the files in media table.
for (FileItem item : formItems) {
if (!item.isFormField()) { // If file
JSONObject fileMeta = getFileAttributes(item);
Clob fileData = uploadFile(
requestInfo,
fileMeta,
item
);
respJson = getObjectFromResponse(fileData);
int fileId = respJson.getInt("fileId");
fileIds.put(fileId);
uploadStatus = respJson.getInt("uploadStatus");
}
}
if (uploadStatus == 1)
respJson.put("runCallback", 1);
}
}
} catch (SQLException e) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC113");
respJson.put("message", "SQLException from DB.");
printWriter.write(respJson.toString());
return;
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC114");
respJson.put("message",
"JSONException in reading response.");
printWriter.write(respJson.toString());
return;
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC115");
respJson.put("message",
"IOException in reading response.");
printWriter.write(respJson.toString());
return;
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC116");
respJson.put("message", ex.getMessage());
printWriter.write(respJson.toString());
return;
}
try {
if (respJson.has("runCallback")) {
responseFromDB = getResponseFromDB(requestInfo);
respJson = getObjectFromResponse(responseFromDB);
}
} catch (SQLException e) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC117");
respJson.put("message", "SQLException from DB.");
} catch (JSONException exj) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC118");
respJson.put("message",
"JSONException in reading response.");
} catch (IOException exi) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC119");
respJson.put("message", "IOException in reading response.");
} catch (Exception ex) {
httpResponse.setStatus(400);
httpResponse.setContentType("application/json");
respJson.put("status", "error");
respJson.put("code", "RSMFPC120");
respJson.put("message", ex.getMessage());
}
httpResponse.setStatus(200);
printWriter.write(respJson.toString());
}
public Clob getResponseFromDB(JSONObject requestData)
throws SQLException, IOException {
WebAppsContext webAppsContext = null;
Connection connection = null;
webAppsContext = WebRequestUtil.createWebAppsContext(httpRequest,
httpResponse);
connection = webAppsContext.getJDBCConnection();
Clob data = null;
String sql = "begin XXX_REST_API_PKG.REST_API(:1, :2); END;";
OracleCallableStatement stmt = (OracleCallableStatement) connection
.prepareCall(sql);
stmt.setString(1, requestData.toString());
stmt.registerOutParameter(2, java.sql.Types.CLOB);
stmt.execute();
data = stmt.getClob(2);
return data;
}
public Clob uploadFile(JSONObject requestData,
JSONObject fileMeta,
FileItem item)
throws SQLException, IOException {
InputStream uploadStream = item.getInputStream();
WebAppsContext webAppsContext = null;
Connection connection = null;
webAppsContext = WebRequestUtil
.createWebAppsContext(httpRequest, httpResponse);
connection = webAppsContext.getJDBCConnection();
Clob data = null;
String sql="begin XXX_REST_API_PKG.UPLOAD_API(:1,:2,:3,:4); END;";
OracleCallableStatement stmt = (OracleCallableStatement) connection
.prepareCall(sql);
stmt.setString(1, requestData.toString());
stmt.setString(2, fileMeta.toString());
stmt.setBlob(3, uploadStream);
stmt.registerOutParameter(4, java.sql.Types.CLOB);
stmt.execute();
data = stmt.getClob(4);
uploadStream.close();
return data;
}
public JSONObject getRequestInfo() throws JSONException, IOException {
JSONObject requestData = new JSONObject();
JSONObject headerData = new JSONObject();
Enumeration headerNames = httpRequest.getHeaderNames();
while (headerNames.hasMoreElements()) {
String paramName = (String) headerNames.nextElement();
headerData.put(paramName, httpRequest.getHeader(paramName));
}
requestData.put("Method", httpRequest.getMethod());
requestData.put("ContextPath", httpRequest.getContextPath());
requestData.put("Header", headerData);
requestData.put("ContentLength", httpRequest.getContentLength());
requestData.put("ServerPort", httpRequest.getServerPort());
requestData.put("isSecure", httpRequest.isSecure());
requestData.put("RemoteHost", httpRequest.getRemoteHost());
requestData.put("RemoteAddr", httpRequest.getRemoteAddr());
requestData.put("ServletPath", httpRequest.getServletPath());
requestData.put("RequestURI", httpRequest.getRequestURI());
requestData.put("Protocol", httpRequest.getProtocol());
// Optional Request Information -
// May not be set if the values are null.
requestData.put("PathInfo", httpRequest.getPathInfo());
requestData.put("QueryString", httpRequest.getQueryString());
requestData.put("RemoteUser", httpRequest.getRemoteUser());
requestData.put("RequestedSessionId",
httpRequest.getRequestedSessionId());
requestData.put("Controller", "FileController");
return requestData;
}
}PL/SQL
CREATE OR REPLACE PACKAGE XXX_REST_API_PKG AS
PROCEDURE REST_API(P_REQ_BODY IN CLOB, P_RESP_BODY OUT NOCOPY CLOB);
PROCEDURE UPLOAD_API(P_REQ_BODY IN CLOB,
P_FILE_META IN CLOB,
P_FILE IN BLOB,
P_RESP_BODY OUT NOCOPY CLOB);
FUNCTION GET_CONFIG(CNF_NAME IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE SAMPLE_CALLBACK(REQ_JSON IN CLOB,
OUT_JSON OUT NOCOPY JSON_OBJECT_T);
PROCEDURE SAMPLE_MIDDLEWARE(REQ_JSON IN CLOB,
RES_JSON OUT NOCOPY JSON_OBJECT_T);
END XXX_REST_API_PKG;
CREATE OR REPLACE PACKAGE BODY XXX_REST_API_PKG IS
/**
* Setup Application
*/
PROCEDURE WP_SETUP_APP IS
APP_CTN VARCHAR2(250) := 'XX_APP_CONFIG';
APP_RTN VARCHAR2(250) := 'XX_APP_ROUTER';
APP_LTN VARCHAR2(250) := 'XX_APP_LOGS';
APP_UTN VARCHAR2(250) := 'XX_APP_UPLOADS';
TEMP_1 VARCHAR2(20000);
BEGIN
-- Create Sequence.
TEMP_1 := 'CREATE SEQUENCE ' || APP_CTN || '_SEQ';
EXECUTE IMMEDIATE TEMP_1;
-- Create Config Table.
TEMP_1 := 'CREATE TABLE ' || APP_CTN ||
'(ID NUMBER,NAME VARCHAR2(250),VALUE VARCHAR2(250),CREATED_AT DATE,UPDATED_AT DATE)';
EXECUTE IMMEDIATE TEMP_1;
TEMP_1 := 'CREATE SEQUENCE ' || APP_RTN || '_SEQ';
EXECUTE IMMEDIATE TEMP_1;
TEMP_1 := 'CREATE TABLE ' || APP_RTN ||
'(ID NUMBER, METHOD VARCHAR2(20), PATH VARCHAR2(250), CALLBACK VARCHAR2(250),
MIDDLEWARE VARCHAR2(250), STATUS SMALLINT, ALLOW_FILE SMALLINT,
CREATED_AT DATE, UPDATED_AT DATE)';
EXECUTE IMMEDIATE TEMP_1;
TEMP_1 := 'CREATE SEQUENCE ' || APP_LTN || '_SEQ';
EXECUTE IMMEDIATE TEMP_1;
TEMP_1 := 'CREATE TABLE ' || APP_LTN ||
'(ID NUMBER(10),HEADER CLOB,BODY CLOB,REQ CLOB, CREATED_AT DATE,UPDATED_AT DATE)';
EXECUTE IMMEDIATE TEMP_1;
-- Create Sequence.
TEMP_1 := 'CREATE SEQUENCE ' || APP_UTN || '_SEQ';
EXECUTE IMMEDIATE TEMP_1;
TEMP_1 := 'CREATE TABLE ' || APP_UTN || '(
ID NUMBER PRIMARY KEY, FILE_NAME VARCHAR2(250), FILE_SIZE_BYTES NUMBER,
CONTENT_TYPE VARCHAR2(250), ACTUAL_FILE BLOB, FIELD_NAME VARCHAR2(250),
CREATED_AT VARCHAR2(250)
)';
EXECUTE IMMEDIATE TEMP_1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('SQLERR:' || sqlerrm);
RETURN;
END;
/**
* Get Config Value using Name
*/
FUNCTION GET_CONFIG(CNF_NAME IN VARCHAR2) RETURN VARCHAR2 IS
CNF_VAL VARCHAR2(500);
BEGIN
SELECT APC.VALUE
INTO CNF_VAL
FROM XX_APP_CONFIG APC
WHERE APC.NAME = CNF_NAME;
RETURN CNF_VAL;
END;
PROCEDURE WP_SAVE_LOG(P_REQ_BODY IN CLOB) IS
REQ_JSON JSON_OBJECT_T;
HEAD_JSON JSON_OBJECT_T;
HEAD_STR CLOB;
BODY_STR CLOB;
REQ_STR CLOB;
BEGIN
REQ_JSON := new JSON_OBJECT_T(P_REQ_BODY);
REQ_STR := REQ_JSON.to_String;
SELECT JSON_SERIALIZE(REQ_STR PRETTY) INTO REQ_STR FROM DUAL;
HEAD_JSON := REQ_JSON.get_Object('Header');
HEAD_STR := HEAD_JSON.to_string;
SELECT JSON_SERIALIZE(HEAD_STR PRETTY) INTO HEAD_STR FROM DUAL;
BODY_STR := REQ_JSON.get_String('Body');
SELECT JSON_SERIALIZE(BODY_STR PRETTY NULL ON ERROR)
INTO BODY_STR
FROM DUAL;
IF BODY_STR IS NULL THEN
BODY_STR := REQ_JSON.get_String('Body');
END IF;
INSERT INTO XX_APP_LOGS
(ID, HEADER, BODY, REQ, CREATED_AT, UPDATED_AT)
VALUES
(XX_APP_LOGS_SEQ.NEXTVAL,
HEAD_STR,
BODY_STR,
REQ_STR,
SYSDATE,
SYSDATE);
COMMIT;
END;
/**
* Rest API Setup
* @param P_REQ_BODY
* @param P_RESP_BODY
*/
PROCEDURE REST_API(P_REQ_BODY IN CLOB, P_RESP_BODY OUT NOCOPY CLOB) IS
REQ_JSON JSON_OBJECT_T;
-- Router
REQ_METHOD VARCHAR2(20);
REQ_PATH VARCHAR2(250);
PATH_COUNT SMALLINT;
ROUTE_CLOB CLOB;
ROUTE_JSON JSON_OBJECT_T;
HEADER_OBJ JSON_OBJECT_T;
-- Middleware
MIDDLEWARE_SQL VARCHAR2(500);
-- Controller
CALLBACK_SQL VARCHAR2(500);
INP_CLOB CLOB;
OUTPUT_JSON JSON_OBJECT_T;
-- Content Type
REQ_CONTENT_TYPE VARCHAR2(20);
CONTENT_TYPE VARCHAR2(20);
BEGIN
OUTPUT_JSON := new JSON_OBJECT_T;
REQ_JSON := JSON_OBJECT_T.parse(P_REQ_BODY);
REQ_METHOD := REQ_JSON.get_String('Method');
HEADER_OBJ := REQ_JSON.get_Object('Header');
-- 1. If PathInfo is not present add / it to request.
IF (NOT REQ_JSON.has('PathInfo')) THEN
REQ_JSON.put('PathInfo', '/');
END IF;
-- 2. Save Request <= Response Log
IF REQ_JSON.get_String('PathInfo') <> '/logs' THEN
WP_SAVE_LOG(P_REQ_BODY);
END IF;
-- 3. Check the path and method exist in router table
REQ_PATH := REQ_JSON.get_String('PathInfo');
SELECT COUNT(*)
INTO PATH_COUNT
FROM XX_APP_ROUTER XAR
WHERE XAR.PATH = REQ_PATH
AND XAR.METHOD = REQ_METHOD
AND XAR.STATUS = 1;
IF PATH_COUNT != 1 THEN
-- Write your output here.
OUTPUT_JSON.put('code', 'MYAPP102');
OUTPUT_JSON.PUT('message', 'Invalid / Inactive Route Path Request.');
P_RESP_BODY := OUTPUT_JSON.to_String;
RETURN;
END IF;
-- 4. Get the Method, Path, Middleware and Callback to be applied
SELECT JSON_OBJECT('id' value XAR.ID,
'method' value XAR.METHOD,
'path' value XAR.PATH,
'middleware' value XAR.MIDDLEWARE,
'status' value XAR.STATUS,
'contentType' value XAR.RES_TYPE,
'callback' value XAR.CALLBACK)
INTO ROUTE_CLOB
FROM XX_APP_ROUTER XAR
WHERE XAR.PATH = REQ_PATH
AND XAR.METHOD = REQ_METHOD
AND XAR.STATUS = 1;
-- Parse the clob and put it in JSON_OBJECT_T
ROUTE_JSON := JSON_OBJECT_T(ROUTE_CLOB);
MIDDLEWARE_SQL := ROUTE_JSON.get_String('middleware');
CALLBACK_SQL := ROUTE_JSON.get_String('callback');
CONTENT_TYPE := ROUTE_JSON.get_String('contentType');
INP_CLOB := REQ_JSON.to_Clob;
-- 5. Pass the request through middleware
IF TRIM(MIDDLEWARE_SQL) <> 'SKIP' THEN
BEGIN
EXECUTE IMMEDIATE MIDDLEWARE_SQL
USING IN INP_CLOB, OUT OUTPUT_JSON;
EXCEPTION
WHEN OTHERS THEN
OUTPUT_JSON.put('code', 'MYAPP103');
OUTPUT_JSON.PUT('status', SQLCODE);
OUTPUT_JSON.PUT('message', SQLERRM);
IF SQLCODE = -6550 THEN
OUTPUT_JSON.put('message',
'Invalid middleware defined for requested path.');
END IF;
END;
ELSE
OUTPUT_JSON.put('midStatus', 1);
END IF;
-- 6. If Request has MultiPart in it send it back to Servlet.
IF REQ_JSON.has('MultiPart') THEN
P_RESP_BODY := OUTPUT_JSON.to_Clob;
RETURN;
END IF;
-- 7. Pass the request to the controller after the middleware passed.
IF OUTPUT_JSON.get_Number('midStatus') = 1 THEN
BEGIN
EXECUTE IMMEDIATE CALLBACK_SQL
USING IN INP_CLOB, OUT OUTPUT_JSON;
EXCEPTION
WHEN OTHERS THEN
OUTPUT_JSON.put('code', 'MYAPP104');
OUTPUT_JSON.PUT('status', SQLCODE);
OUTPUT_JSON.PUT('message', SQLERRM);
IF SQLCODE = -6550 THEN
OUTPUT_JSON.put('message',
'Invalid controller defined for requested path.');
END IF;
OUTPUT_JSON.put('Controller', 'Failed In Controller.');
END;
END IF;
-- 8. Set all accumulated data into OUTPUT.
P_RESP_BODY := OUTPUT_JSON.to_Clob;
EXCEPTION
WHEN OTHERS THEN
OUTPUT_JSON.PUT('error', SQLERRM);
P_RESP_BODY := OUTPUT_JSON.to_String;
END REST_API;
/**
* Upload API
* @param REQ_JSON The ID of the partner we want to check
* @param OUT_JSON The ID of the partner we want to check
*/
PROCEDURE UPLOAD_API(P_REQ_BODY IN CLOB,
P_FILE_META IN CLOB,
P_FILE IN BLOB,
P_RESP_BODY OUT NOCOPY CLOB) IS
OUTPUT_JSON JSON_OBJECT_T := new JSON_OBJECT_T();
FILE_META JSON_OBJECT_T;
FILE_NAME VARCHAR2(250);
FILE_SIZE NUMBER(10);
CONTENT_TYPE VARCHAR2(250);
FIELD_NAME VARCHAR2(250);
TBL_SEQ NUMBER(10);
BEGIN
FILE_META := new JSON_OBJECT_T(P_FILE_META);
FILE_NAME := FILE_META.get_String('fileName');
FILE_SIZE := FILE_META.get_Number('sizeInBytes');
CONTENT_TYPE := FILE_META.get_String('contentType');
FIELD_NAME := FILE_META.get_String('fieldName');
TBL_SEQ := XX_APP_UPLOADS_SEQ.NEXTVAL;
-- Blob
INSERT INTO XX_APP_UPLOADS
(ID,
FILE_NAME,
FILE_SIZE_BYTES,
CONTENT_TYPE,
ACTUAL_FILE,
FIELD_NAME,
CREATED_AT)
VALUES
(TBL_SEQ,
FILE_NAME,
FILE_SIZE,
CONTENT_TYPE,
P_FILE,
FIELD_NAME,
SYSDATE);
OUTPUT_JSON.put('uploadStatus', 1);
OUTPUT_JSON.put('fileId', TBL_SEQ);
P_RESP_BODY := OUTPUT_JSON.to_Clob;
END;
/**
* Sample Middleware
* @param REQ_JSON The ID of the partner we want to check
* @param OUT_JSON The ID of the partner we want to check
*/
PROCEDURE SAMPLE_MIDDLEWARE(REQ_JSON IN CLOB,
RES_JSON OUT NOCOPY JSON_OBJECT_T) IS
MID_JSON JSON_OBJECT_T := new JSON_OBJECT_T();
BEGIN
MID_JSON.put('REQ', JSON_OBJECT_T(REQ_JSON));
MID_JSON.put('midStatus', 1);
RES_JSON := MID_JSON;
END;
/**
* Sample Callback
* @param REQ_JSON The ID of the partner we want to check
* @param OUT_JSON The ID of the partner we want to check
*/
PROCEDURE SAMPLE_CALLBACK(REQ_JSON IN CLOB,
OUT_JSON OUT NOCOPY JSON_OBJECT_T) IS
RES_JSON JSON_OBJECT_T;
BEGIN
RES_JSON := new JSON_OBJECT_T();
-- RES_JSON.put('REQ', JSON_OBJECT_T(REQ_JSON));
RES_JSON.put('RES', 'Upload success');
-- RES_JSON.put('request', RES_JSON.get('Body'));
OUT_JSON := RES_JSON;
END;
END XXX_REST_API_PKG;2. Updating Deployment Descriptors
Update the temporary template which will automatically update the web.xml in $OA_HTML.
Location: $FND_TOP/admin/template
Edit File: oacore_web_xml_FMW.tmp
<servlet>
<servlet-name>XXMyWebApp</servlet-name>
<servlet-class>com.myapp.web.App</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>XXMyWebApp</servlet-name>
<url-pattern>/myapp/*</url-pattern>
</servlet-mapping>Security
File: custom.conf
# Sample entry for URL without *
# /OA_HTML/signin
# Sample entry with URL mapping that ends with *
servlet /OA_HTML/myappCommand:
$ java oracle.apps.fnd.security.resource.WLDataMigration MODE=custom
INPUT_FILE=custom.conf DBC=$FND_SECURE/<SID>.dbc
Enter APPS username:
Enter APPS password:
- O/P:
OUTPUT
======
---
*** Time [06/12/2022 10:40] ***
WLDataMigration run with following parameters:
Mode: custom
Input File: custom.conf
Input File Type: CONF
Parsing mode: Recursive
---
Parsing the input file for web resources...
Gathering resource information from APPL_TOP...
Processing file... [custom.conf]
Finished parsing.
Loading parsed web resources into the DB now...3. Generating customall.jar
$ cd $JAVA_TOP
$ adcgnjar
Enter APPS username:
Enter APPS password:4. Bounce Server
$ admanagedsrvctl.sh stop oacore_server1
$ admanagedsrvctl.sh start oacore_server1