Wednesday, September 7, 2022

ODI12C - How to open ODI studio from Console or terminal

This is simple. If the Odi 12c is installed on linux system, then open the terminal and navigate to odi/studio path and run ./ script.

Steps with screenshots:

Thursday, August 25, 2022

OIC - Get object from OCI object storage using OIC | Oracle Integration Cloud

Usecase: Here, we will create an object storage rest connection and using that connection, we will create an app driven integration, configure rest call to object storage and get the file from object storage.

Implementation Steps:

Step1: Create Rest connection.

Oracle home >> Integrations >> Connections >> Create >> Rest 

Provide rest API base URL

Select Securtiy as OCI Signature version 1

Provide Tenancy OCID, user OCID, private ley and fingerprint.

Test and save.

Step2: Create an app driven integration 

Integration flow:

Configure Rest input as object name and output as Binary

Call the rest connection to get the file or object from object storage

URI: /n/{namespaceName}/b/{bucketName}/o/{objectName}
Verb: GET
Configure Request Payload as Binary Format

Map the bucket name, object name and namespace.

Map the object storage response file reference to rest response.


How to create a OCI object storage bucket, how to get Tenancy OCID, User OCID, API private RSA key and finger print and Namespace, everything mentioned in the previous blog. Pleae check below link.

Tuesday, August 23, 2022

OIC How to use OCI Object storage from the Oracle Integration Cloud | Put a file to OCI Object storage from Oracle Integration Cloud

UseCase: Here, we will create a OCI object storage bucket. From OIC, read a file and put that file to bucket using Rest connection.

Step 1 : Create a bucket in OCI object storage

In the Oracle console >> Storage >> object storage & Archive storage >> select the required compartment >> create bucket

Step 2 : Create a Object storage Rest connection

To create a Rest connection , we need to collect the following information:

Connection type : REST API Base URL

Connection URL : https://objectstorage.<region>

My case, region is: us-ashburn-1

Securtity: OCI Signature Version 1

Tenancy OCID and Object storage Namespace: get the Tenancy OCID from the OCI console on the Tenancy Details page.

Search Tenancy in the search box

Or from profile >> Tennacy

Here we also got the Object storage namespace.

User OCID : get the user's OCID in the console on the User details page.

Profile >> User Settings

Private Key & FingerPrint: 

Profile >> User settings >> API Keys >> Add API Key >> download private key >> add >> note the fingerprint.

Oracle home >> Integrations >> Connections >> Create >> Rest 

Test and save.

Step 3:  Create the Integration

Integration flow:

Read the file 

Call the rest connection to put the file to object storage

URI: /n/{namespaceName}/b/{bucketName}/o/{objectName}
Verb: PUT
Configure Request Payload as Binary Format.

Map the namespace, bucket name, object name and fileReference to stream reference.

Save, Add Tracking and activate and test

See the below file uploaded to object storage bucket.

Note: Private key downloaded from the Oracle Cloud Infrastructure Console are in PKCS8 format. The OCI Signature version 1 security policy available with the Rest adapter only supports reading of the private key in RSA format(PKCS1) format.

If you receive the following error, you must convert the private key from PKCS8 to RSA(PKC1) format: java.lang.ClassCastException: org.bouncycastle.asn1.pkcs.PrivateKeyInfo can not be cast to org.bouncycastle.openssl.PEMKeyPair.

Convert the private key with the following command:

openssl rsa -in private_key_in_pkcs8_format.pem -out new_converted_file.pem

We can also convert it using online availavle site like below:


OIC Interview question and Answers | Oracle Integration Cloud

OIC interview Q & A:

1. FBDI approach with an example

2. OIC FTP vs OIC File adapter


3. ERP events callback or subscription.


4. Gen1 vs gen2 OIC pack

5. OIC Agents

6. How to handle large file in OIC

7. OIC error handling:

8. OIC Integration pattern/ Styles

9. OIC migration from one instance to higher one.

10. What issues you observed in OIC ERP.

11. Can file adapter and ftp adapter support high availability mode using connectivity agent?

12. Maximum duration for integration flows | time out time for OIC services | Service limit for Scheduled orchestration or Async or Sync service

13. Why to avoid creating too many scheduled integrations

14. How many different scheduled integrations can run in parallel in Oracle Integration | Decoupled scheduler and Business Logic pattern.

15. Does OIC Support Asynchronous request response service?

- It is permissible to call asynchronous fire and forget(one way).

- Oracle integrations presently does not support modelling an asynchronous request response service. However, all scheduled orchestration patterns implement an asynchronous request response internally, As a result, asyn integration through scheduled orchestration is an anti pattern

16. Why we should use OIC instead SOACS?

17. Version controlling in OIC?

18. How to do monitoring in OIC?

19. How will you implement multiple operation based calculator service in OIC? How will you implement calculator service in OIC?

For Soap:

For rest with multiple verbs:

20. Have you used enrichment service in OIC?

21. What are the OIC integrations you have implemented in your project?

22.  How to embed barcode or image in import to erp? How to embed image or barcode in email?

23. We have a Project requirement that we have to schedule a service to load data and also need to do a real time data feed as needed. How you will implement this?

24. Rest api vs Erp cloud adapter in terms of FBDI import.

25. What  OIC adaptets you have worked on?

Answer: Ftp, file, DB, ATP, Rest, SOAP, ERP, HCM

26. BIP vs OTBI report

27. FAH vs GL when to use for import?

28. Can we read excel file in OIC? If no, how we can achieve it?

Answer:  OIC does not support excel read. If we really need to read it then follow the below steps:

Create a serverless OCI function with python/Java logic to convert excel to csv >> create a bucket in oci object storage >> create a rest connection to put excel file from oic to Object storage >> do another call to function to convert and save to OS >> do another OS call to get the converted CSV file and use.

29. How to change the email notification from part?

30. What are the diffrent stage action functions?

31. BIP report huge size, How to handle this situation?

32. Suppose you are invoking a BIP report and received null or empty file how to handle this so that it should not flow to Downstream applications?

33. There are large number of schedule orchestrated integrations run at the same time. What to do to avoid backlogging??

34. Have you used PLSQL in xslt or in complex logic processing?

35. Can we call BIP report from database?

36. What is your current role in the project?

37. Suppose we are getting data from ERP and then storing them in a database table. How to avoid the duplicate data to be stored in the database?

38. Apex reports, Can we use plsql in apex?

39. We have a requitement to digitally sign the email while sending the email notification from OIC. Is there any way we can embed an image in the email notification in OIC?

40. How to get the 2nd or 3rd max salary from an employee table using sql query?

41. Can we convert App driven orchestration to Scheduled orchestration integration?


OIC Tracing | How to enable and use OIC Tracing | Oracle Integration Cloud

Why we need tracing: 

  • When Tracing is enabled, OIC integration prints detailed information before and after each action / activity that is executed. It also prints the message / payload of needed. 
  • We should make sure that it is enabled only for debugging purposes and turned off before going into production.
  • When tracing is enabled, integration activity can be viewed in Activity Stream.
Types of Tracing:
  1. Integration Level : If your requirement is to enable the tracing for one or more integrations and disable tracing for the rest of the integrations, you can ise Integration level tracing.
  2. Global Tracing On : It will turn tracing on for all activated flows and we can't enbale/disable it at individual level on the integration page.
  3. Global Tracing Off :  It will turn tracing off for all activated flows and we can't further enbale/disable it at individual level on the integration page.

How to change the level of Tracing:

Oracle home >> Settings >> Integrations >> Tracing

If we choose the Integration level tracing, we can enbale or disable the tracing at individual integration level following below steps:

Go To Integrations Page >> select actions >> Tracing >> Enable Tracing & Include Payload >> Save

Tuesday, August 16, 2022

OIC to ERP : Move custom report | download custom report based on ess job id and then move to desired folder

Usecase: Here, we will create a sub integration which will take a request id and check if ESS job status, if its succceeded, it will download the report and move the file to desired mail dl or sftp directory.

HighLevel steps:

  • Main flow (where import and child process runs) call to callback integration
  • Archive the success file
  • Execute the execution accounting report based on child request id for support help.
  • Call ESS job which internally calls the BI report to execute the SQL query to get the callback report
  • Call a Sub Integration like MoveCTRLReport and feed the ESS job process id
    • Here, check the ESS job status using getESSJobStatus(Interesting fact is that we are looping max 55 times to check if the ESS process completes )
    • If ESS process gets completed,
      • Download the control report using downloadESSJobExecutionDetails operation
      • Write the control report in stage
      • Unzip the report
      • Read the text report
      • Move the report to FTP location

Request payload:







Implementation logic steps:

  1. Configure rest request with post verb. Request json payload spacified above.
  2. Assign looping variables like
    1. LoopCounter = -45.0
    2. ESS_Status = ""
    3. MaxLoopCount = 9
  3. Take a while loop with condition $Loopcounter < $MaxLoopCount and $ESS_Status != "SUCCEEDED"
    1. Call Erp cloud adapter - operation: getESSJobStatus and service : erpIntegrationService and map request Id.
    2. Wait 59 sec
    3. Assign status like
      1. LoopCounter = Loopcounter +1
      2. ESS_Status = result of the getESSJobStatus
  4. Take a switch and check if ESS_Status = SUCCEEDED 
    1. If yes,
      1. Call erp cloud adapter and download control report using service: erpIntegrationService and operation: downloadESSJobExecutionDetails. And map request id
      2. Write control reports to stage location using sourceFileName, temp stage location and using opaque schema and map the content to opaque element.
      3. Unzip the zipped contents using stage
      4. Read text control report using stage and xsd opaque file. Specifying file name as unzipcontrolreport/properties[filetype='text']/filename
      5. Call another sub integration like OIC_File_Handler to move the control report to desired sftp or s3 folder. Or send a mail attaching the report.
    2. If no, throw new fault with code, reason, details.

OIC To ERP - Design steps for Import and Custom callback | Oracle Integration Cloud

Usecase: Here we will see a design how we can import to Cloud ERP and get a Custom callback. 

Implementation Steps:

Follow the below design steps:

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...