soalicious

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 ./odi.sh 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.

Test

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.

https://soalicious.blogspot.com/2022/08/oic-how-to-use-oci-object-storage-from.html

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>.oraclecloud.com

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:

oracle.cloud.connector.impl.rest.security.signature.signatureException: 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:

https://8gwifi.org/pemconvert.jsp

Reference:

https://blogs.oracle.com/integration/post/how-to-use-the-oci-object-storage-from-the-oracle-integration-cloud

OIC Interview question and Answers | Oracle Integration Cloud

OIC interview Q & A:

We have divided the interview questions into 3 sections: Non- Technical, Technical shorts and Scenario based questions.

A. Non - Technical:

Here’s a concise list of the non-technical questions for OIC interviews:

Can you briefly introduce yourself, including your overall experience and your relevant experience in OIC?
What is your current role and what are your key responsibilities in the current project, especially related to OIC? Answer: In my current role as a Technical Lead and OIC Developer, I lead a team of five members, managing end-to-end integration projects. My responsibilities include client-facing communication, gathering and analyzing requirements, designing scalable solutions, and overseeing development and delivery using Oracle Integration Cloud (OIC). I ensure smooth coordination between the team and stakeholders, mentor team members, and resolve technical challenges to deliver high-quality results.
Can you explain how you collaborate with other teams or stakeholders in your current project?
How do you handle challenges or issues in integrations that arise during your work?
How do you ensure your integrations meet business requirements and are user-friendly?
How do you prioritize tasks and manage deadlines when working with multiple integrations?
How do you keep yourself updated with the latest features or changes in OIC?
How do you handle client communications or presentations, especially when explaining complex technical topics in simple terms?
What has been your biggest challenge in working with OIC so far, and how did you overcome it?
How do you ensure quality and performance in your integration solutions?

B. Technical shorts

Can you explain the different types of integrations patterns or styles available in OIC (App Driven, Scheduled, REST, SOAP, File-based, etc.)? - https://soalicious.blogspot.com/2019/11/oracle-cloud-integrationoic-introduction.html
Which OIC adapters you have worked on? - Answer: FTP, file, DB, ATP, Rest, SOAP, ERP, HCM etc.
What is the difference between synchronous and asynchronous integrations in OIC? https://soalicious.blogspot.com/2025/06/oic-what-is-difference-between.html
How do you monitor and troubleshoot integrations in OIC? - https://soalicious.blogspot.com/2025/06/oic-monitoring-and-troubleshooting.html
What is the connectivity agent in OIC and why is it important? How to cofigure connectivity agent and its usage limitations -

How do you deploy or migrate integrations between environments (Dev, Test, Prod) in OIC? - https://soalicious.blogspot.com/2022/02/oic-migration-of-integration-from-one.html
How do you manage versions of integrations in OIC? - https://soalicious.blogspot.com/2024/12/oic-versioning-in-oracle-integration.html
What is lookups in OIC and how do you use them? - Use of Lookup | Create a Lookup and use in an Oracle Cloud Integrations
What is the difference between gen2 & gen3 OIC? https://soalicious.blogspot.com/2022/03/oic-gen-1-vs-gen-2-benefits-of-oic.html
What are the new features available in gen 3 25.04 version. -https://soalicious.blogspot.com/2025/06/oic-gen3-new-features-2504-version.html
How will you schedule an integration ,what is ICAL expression .
OIC FTP vs OIC File adapter - https://soalicious.blogspot.com/2021/05/oic-file-adapter-vs-ftp-adapter.html
How do you handle bulk data integrations (e.g., with FBDI or HDL)?
What are the different xslt functions you have worked? Have you created any custom javascript functions in OIC? - https://soalicious.blogspot.com/2024/12/oic-commonly-used-xslt-functions-in.html
How do you handle fault handling and error management in OIC integrations? - https://soalicious.blogspot.com/2021/12/oic-ootb-error-handling-in-easy-steps.html
suppose we have 4 fbdi integrations for different business objects can we have one call back integration for all these fbdi if yes how ? can you explain - https://soalicious.blogspot.com/2025/06/oic-designing-reusable-callback.html?m=1
Can you describe how you implemented OIC integrations with Oracle ERP Cloud / HCM Cloud?
How do you handle pagination when working with REST APIs in OIC?
Can you explain how you use REST and SOAP connections in OIC? - https://soalicious.blogspot.com/2025/06/oic-can-you-explain-how-you-use-rest.html
what are the differences between SOAP vs REST? - https://soalicious.blogspot.com/2025/01/oic-difference-between-soap-and-rest.html
Can we send soap xml using REST adapter? https://soalicious.blogspot.com/2025/01/oic-can-we-send-xml-input-in-rest.html
How do you secure integrations in OIC (authentication, certificates, etc.)?
What is the different type of authentication ,how will you configure OAuth
FBDI related question:

FBDI approach with an example - https://soalicious.blogspot.com/2022/02/oic-erp-supplier-bulk-import-and.html
ERP events callback or subscription. - https://soalicious.blogspot.com/2023/03/erp-register-csf-key.html?m=1
How will you generate properties file in FBDI based integration.
How will you design the callback integration.
Rest api vs Erp cloud adapter in terms of FBDI import. https://soalicious.blogspot.com/2024/12/oic-using-rest-api-vs-erp-cloud-adapter.html
FAH vs GL when to use for import?https://soalicious.blogspot.com/2023/12/oic-when-to-use-fah-vs-gl-import.html
BIP report huge size, How to handle this situation?Answer: Using BI bursting
Handling Null or Empty BIP Report Files in Oracle Integration Cloud to Prevent Flow to Downstream Applications - https://soalicious.blogspot.com/2024/12/oic-handling-null-or-empty-bip-report.html
Can we call BIP report from database?
How to handle in Integration when report has huge amount of data or How to handle when BIP report going into error due to huge data?
how to submit ESS Job in Integration
Bursting the BIP Report (FTP and Email)

How to configure ATP db connection in oic
How will you call external rest API from OIC.
How to configure Schedule parameter parameters? What is the use of these parameter.
What is the use of integration properties?
BI report related questions:

How to call BI report from OIC.
BIP vs OTBI report https://soalicious.blogspot.com/2024/05/erp-hcm-bip-vs-otbi.html

How will you enable business event .
How will you design real time integration in oic ,ex event base integration .
How to handle large file in OIC - https://soalicious.blogspot.com/2021/05/oic-how-to-handle-more-than-10-mb-file.html
What issues you observed in OIC ERP.- https://soalicious.blogspot.com/2021/03/oic-synchronous-integrations.html
Can file adapter and ftp adapter support high availability mode using connectivity agent - https://soalicious.blogspot.com/2023/07/oic-can-file-server-and-ftp-server.html
Maximum duration for integration flows | time out time for OIC services | Service limit for Scheduled orchestration or Async or Sync service - https://soalicious.blogspot.com/2023/06/oic-maximum-duration-for-integration.html
Why to avoid creating too many scheduled integrations - https://soalicious.blogspot.com/2021/07/oic-why-to-avoid-creating-too-many.html
How many different scheduled integrations can run in parallel in Oracle Integration | Decoupled scheduler and Business Logic pattern. - https://soalicious.blogspot.com/2023/09/oic.htm l
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
Why we should use OIC instead SOACS?- https://soalicious.blogspot.com/2024/05/oic-why-should-we-choose-oic-over-soacs.html
How to resubmit failed instances in OIC https://soalicious.blogspot.com/2024/12/oic-resubmit-failed-messages.html
How will you implement multiple operation based calculator service in OIC? How will you implement calculator service in OIC?Answer:For Soap:https://soalicious.blogspot.com/2022/03/oic-managing-multiple-operations-in-oic.html For rest with multiple verbs:https://soalicious.blogspot.com/2022/11/oic-use-rest-adapter-with-multiple.html
About enrichment service in OIC? Have you used any of the enrichment services - https://soalicious.blogspot.com/2024/12/oic-enrichment-service-in-oracle.html
What are the OIC integrations you have implemented in your project?Concise gist:"In my project, I have implemented several integrations using Oracle Integration Cloud (OIC). These include FBDI (File-Based Data Import) integrations for loading data into Oracle ERP Cloud, HCM Extract and HDL (HCM Data Loader) integrations for handling employee data between Oracle HCM Cloud and external systems, and real-time REST API loads for seamless data exchange between applications. Additionally, I have worked on integrating Utility Cloud Services to manage energy consumption data and automate reporting between our cloud and third-party systems."
How to embed image or barcode in email?https://soalicious.blogspot.com/2024/07/oic-how-to-embed-image-in-email-body.html?m=1#google_vignette

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.
How to change the email notification from part - https://soalicious.blogspot.com/2023/06/oic-how-to-change-from-address-for.html
What are the diffrent stage action functions?Answers: we can do the following:Read entire file, Read file in segments, write file, zip files, Unzip file, Encrypt file, decrypt file, List files
There are large number of schedule orchestrated integrations run at the same time. What to do to avoid backlogging?https://soalicious.blogspot.com/2024/06/oic-there-are-huge-number-of-scheduled.html
Have you used PLSQL in xslt or in complex logic processing?https://soalicious.blogspot.com/2024/12/oic-can-we-usecall-plsql-in-oic-xslt.html
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?https://soalicious.blogspot.com/2024/12/oic-how-to-avoid-storing-duplicate-data.html
How to get the 2nd or 3rd max salary from an employee table using sql query?Answer:https://soalicious.blogspot.com/2025/06/sql-how-to-get-2nd-or-3rd-max-salary.html
Can we convert App driven orchestration to Scheduled orchestration integration?Answer: directly there is no provision to convert from App driven to Scheduled integrations. But we can convert from scheduled to app driven integrations. As a workaround , we can create a separate scheduled orchestration integration and can call then the app driven the integration from the scheduled one.
We need to execute a schedule job in oic that will run only on weekdays excluding public holidays?Answerhttps://soalicious.blogspot.com/2024/09/oic-we-need-to-execute-schedule-job-in.html
Max number of scopes can be created in an integration? Answer: Unlimited.
What is the maximum number of instances of an integration that can run in parallel in OIC for application and scheduled integrations?Answer: In OIC, application integrations support up to 100 synchronous and 50 asynchronous concurrent executions by default. For scheduled integrations, only one instance runs at a time, but a decoupled approach (using a parent scheduled integration to invoke multiple child integrations) enables concurrent execution effectively.
Can we use data from one scope to another scope ? If yes, how?Answer: yes, using global variable and data stitch action
While we call HCM extract flow, which fields we are passing ?Answer: Extract name or flow name, Integration name, Parameters like effective date, Legislative data group
What are the different fault handling actions avaiable in OIC? And their functions?Throw new fault, rethrow fault & fault return https://soalicious.blogspot.com/2021/08/oic-ways-of-throwing-faults.html?m=1
Suppose you are calling an API for invoice bulk import, how you will track any failure for each invoices?
What are different OCI storage types available and their fucntions?Object storage , Block volume, File storage
What is max size notification action - attachment support?Answer: 2 MB
Explain one end to end integration you have developed and what issues you have faced during the development and how did you tackle the issues.
In OiC, is it possibe to trigger parallel flow?Yes, in Oracle Integration Cloud (OIC), you can trigger parallel flows using the "Parallel" action available in orchestrations.
How to debug the integration issues in OIC?
Explain one complex integration which you have worked on
Benefits of using stage read file in segment and max size it supports?
Can we configure two callbacks in single trigger? If yes, how? What is the impact?
Security mechanism used in rest adapter?
How to embed barcode or image in import to erp?
how will you achieve message level encryprion and file level encryption in OIC?

C. Scenario based questions:

How can we manage sequence numbers without having direct access to the database? https://soalicious.blogspot.com/2024/12/oic-managing-sequence-numbers-in-oic.html
In an SCM-to-SAP integration scenario, how can we design a process to call a BIP report, retrieve the inventory file, and send it to SAP?https://soalicious.blogspot.com/2024/12/oic-seamless-integration-scm-to-sap.html
If a file contains two types of records (stub and payment), how can we efficiently process it by fetching payment details for each stub record and creating detailed records for the target system?https://soalicious.blogspot.com/2024/12/oic-xslt-dynamic-xpath-mapping-in.html
What is the difference between creating integrations in projects versus packages in OIC?https://soalicious.blogspot.com/2024/09/oic-gen3-working-with-projects-packages.html
How can we design a robust error-handling mechanism in an integration flow, considering a SaaS application is available to store failure details and create tickets?
What is the best approach to validate input fields, whether they are received from a REST API or a file?https://soalicious.blogspot.com/2024/11/oic-input-validation-ensuring-mandatory.html
What are the different ways to access object storage from OIC? Answer: 2 ways. 1. Creating a object storage rest connection and use object sorage api to put the file. 2. Use gen 3 provided Object storage action.
What is the purpose of the fault data type variable in OIC?https://soalicious.blogspot.com/2024/10/oic-use-of-fault-datatype-variable.html
When performing an HDL import, how can we retrieve the import status and error details if the process fails, and share them with support or the business team https://soalicious.blogspot.com/2024/09/oic-hcm-how-to-check-or-export-report.html
How can data be extracted from HCM and sent to a target application?
If FTP does not support polling as a trigger, what alternative solutions can be used to achieve FTP polling?
How can we share a database adapter's response from one scope to another scope within an integration? Answer: using global variable and data stitch
How can we send a custom fault response to the caller of an OIC API service in case of a failure?
If a BIP report generates a large file, what design strategies can be used to overcome OIC's size limitations?
For FBDI processing, is it better to use the ERP adapter or REST service API?
Is it possible to expose multiple operations within a single SOAP integration in OIC
How to fetch incremental or delta or daily data using BI report?
How can we process large files (greater than 100 MB) in OIC?
Why should we avoid creating too many scheduled integrations in OIC?
How can we execute a scheduled job in OIC that runs only on weekdays, excluding public holidays? https://soalicious.blogspot.com/2024/09/oic-we-need-to-execute-schedule-job-in.html
What are the solutions to overcome the 10 MB limit of the DB adapter when calling a database procedure in OIC? https://soalicious.blogspot.com/2022/04/oic-db-procedure-call-db-adapter-10-mb.html
How can we call a database procedure asynchronously in OIC? https://soalicious.blogspot.com/2022/12/oic-how-to-call-db-procedure.html
How can we implement retry logic to handle timeouts when uploading files to an AWS S3 bucket?
How can XML data be converted to JSON in OIC?
How you handle complex mapping in oic?
How to recover a failed intances in oic?
Can you describe a complex integration scenario you worked on in OIC?
How do you implement file-based integrations in OIC?
How will you handle error in oic integration .If you are processing multiple records how will you ensure all the record being process even a single record is error out.Answer: https://soalicious.blogspot.com/2025/05/oic-how-will-you-handle-error-in-oic.html
How come you overcome the timeout scenario when you are invoking store procedure from OIC integration .
How will you convert a string to a json array .
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?Answer: first create an app driven integration and expose as rest / soap so that client can send data in real time. Second, create a schedule integration to call that app driven integration to run in a scheduled time manner.

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:

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.
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.
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:

{

"RequestId":"",

"directoryAdapter":"",

"sourceFileName":"",

"targetDirectory":""

}

Implementation logic steps:

Configure rest request with post verb. Request json payload spacified above.
Assign looping variables like

LoopCounter = -45.0
ESS_Status = ""
MaxLoopCount = 9

Take a while loop with condition $Loopcounter < $MaxLoopCount and $ESS_Status != "SUCCEEDED"

Call Erp cloud adapter - operation: getESSJobStatus and service : erpIntegrationService and map request Id.
Wait 59 sec
Assign status like

LoopCounter = Loopcounter +1
ESS_Status = result of the getESSJobStatus

Take a switch and check if ESS_Status = SUCCEEDED

If yes,

Call erp cloud adapter and download control report using service: erpIntegrationService and operation: downloadESSJobExecutionDetails. And map request id
Write control reports to stage location using sourceFileName, temp stage location and using opaque schema and map the content to opaque element.
Unzip the zipped contents using stage
Read text control report using stage and xsd opaque file. Specifying file name as unzipcontrolreport/properties[filetype='text']/filename
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.

If no, throw new fault with code, reason, details.

Sceenshots:

TBD

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:

First source file is there in a SFTP location. So we will list files of the location using SFTP connection
We can have multiple files, so we will iterate over the file references and take a body scope

Download each file to stage location
Write header field names to a header file.
Write LineHeader names to a Line file
Read file reference from the download response using stage File action and CSV file sample.
For each file record,

Put a switch activity and check of 1st column of the field(Transaction_Identifier) = Header ,

Then appends the header details to the header file

If Transaction_Identifier = Line,

You can map the line records to the Line file
Take a debit and credit line counts, total debit and total credit line counts etc

If your file has Trailer part , check if Transaction_Identifier = Trailer,

Take the all the trailer record counts like total debit line count, total credit line counts, total debit amount, total credit amount , total lines etc

We can also map the line records outside of the for each record loop using a Stage Read in Segments so that the performance can improve, In the XSLT , we have to check if the record identifer =Line, then go for the line record mapping to line file.
Write Metadata file like Metadata version number and Application short name etc if needed
Zip all these 3 files - header, Line and Metadata using stage file action
We can keep a back up of the zip file by calling a sub Integration (File_Handler) to archive the file to a FTP location
Now we can validate the Trailer information with the record level all the counts like total debit line , total credit line, total debit amt, total credit amt , total lines etc. Once this switch checks success