Basic SQLs – Oracle EBS ISG – SOAP, REST Web Services

 

Business Need:

Miscellaneous SQLs for analysis and troubleshooting of Oracle EBS module ISG (Integrated SOA Gateway) hosted SOAP and REST based web services.

 

Queries:

All WS/APIs/Services – Return both SOAP and REST

-- all SOA web services available in the system
SELECT *
FROM APPS.FND_SOA_SERVICES
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE WSDL_URL LIKE '%_XXOCM_%';
 

All REST / RESTful Web Services

-- all RESTful web services deployed in an instance
SELECT SERVICE_ALIAS, WSDL_URL, CLASS_ID, TO_CHAR(CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE
FROM APPS.FND_SOA_SERVICES FSS
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE WSDL_URL LIKE '%%'
--AND WSDL_URL LIKE '%/rest/%'   -- get only RESTful Web Services
ORDER BY SERVICE_ALIAS;
 

All Services, more details

-- web services, class, internal program, type - details
--SELECT FSS.*, TO_CHAR(FSS.CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE, ICL.*
SELECT FSS.SERVICE_ALIAS, FSS.WSDL_URL, FSS.CLASS_ID, TO_CHAR(FSS.CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE,
    ICL.CLASS_NAME, ICL.IREP_NAME, ICL.CLASS_TYPE, ICL.PRODUCT_CODE, ICL.SOURCE_FILE_PATH, ICL.SOURCE_FILE_NAME, ICL.DISPLAY_NAME
FROM APPS.FND_SOA_SERVICES FSS, FND_IREP_CLASSES_VL ICL
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE FSS.WSDL_URL LIKE '%%'
AND FSS.CLASS_ID = ICL.CLASS_ID(+)
--AND WSDL_URL LIKE '%/rest/%'   -- get only RESTful Web Services
ORDER BY FSS.SERVICE_ALIAS;
 

All SQLs from a single DB Schema

-- all sqls from a single db user
SELECT  S.MODULE, SQL_TEXT , S.EXECUTIONS   
FROM SYS.V_$SQL S, SYS.ALL_USERS U
WHERE S.PARSING_USER_ID=U.USER_ID
AND (s.MODULE='agt:isg:oracle.apps.fnd.isg.common.util.IRepAcce')
--and s.serial = '40903'
ORDER BY S.LAST_LOAD_TIME;
 
 

Other Important ISG/SOA Related tables

FND_SOA_ATTACHMENT
FND_SOA_BODY_PIECE
FND_SOA_JMS_IN
FND_SOA_JMS_OUT
FND_SOA_LOG_CATEGORY
FND_SOA_LOG_SERVICE_DETAILS
FND_SOA_REQUEST
FND_SOA_RESPONSE
FND_SOA_RESPONSE_METHOD
FND_SOA_RUNTIME_ERROR
FND_SOA_SERVICES
FND_SOA_SERVICE_DT_ERRORS
FND_SOA_SERVICE_OPERATIONS
FND_SOA_SERVICE_POLICIES
 

 

 

Keywords:

Oracle EBS, R12, R12.2.8, ISG, Integrated SOA Gateway, APPLSYS, FND, SOAP, REST, XML, JSON, SOAPUI, WS, Web Services, API, Query, SQL, SQLPLUS