Monday, December 20, 2021

ODI - Changing ODI Schedule

Step1: Go to Operator -- Scheduling -- select your load plan and Global/logical Agent -- right click on it and open /New to update or to create new schedule respectively.

Step2: Update Agent schedule:

Go to Topology -- Agents -- Right click on OracleODIAgent and click update schedule

ODI - Steps to restart ODI Agent

Using ODI on Oracle Cloud Marketplace: 

Step1: Go to Terminal(Here, Applications --System Tools -- Terminal)

Step2: Navigate to the script location:


Here, cd /u01/oracle/mwh/odi/common/scripts


Step3: Run the command to shutdown

python shutdown

Step4: Run command to start the agent:

Nohup python start &

To see the logs run as below:

tail -f nohup.out
Ctrl +C to come out

Step5: Run the command to restart the service:

python restart

Step6: Check Status
python status

Step7: Test agent from Odi studio

Go to Topology -- Agents -- Right click on OracleODIAgent and click test.

Thursday, December 16, 2021

OIC -OOTB Error handling in easy steps

 Fault handlers:

  • Scope level
  • Global fault

Scope level:

Scope level fault handles are used to handle errors that arise during the invoke actions within those scopes. It handles the faults following 2 ways:

Implicit/specific fault handlers:

Depending on the type of invokes we used like Database, soap, ERP and etc. there will be a set of specific faults provided in the OIC in the scope level. It is like specific catch block. Each implicit fault handler will handle specific type of faults like APIAccessErrorFault, ServerErrorFault, UnexpectedErrorFault etc.

Default/Generic fault handler:

A default fault handler is generated for every scope which acts like a catch all block for that scope. Any error that is not handled by one of the specific or implict fault handlers will be handled by the default fault handler.

Global fault handler:

Global fault handler is available at the overall integration flow level which acts as a super catch block. Any errors unhandled at the inner scopes or errors encounterd in non-scope blocks will be bubble up to this Global fault handler.


  1. If you dont want to define or configure any implicit fault handler then define the default fault handler as that acts like a catch all.
  2. If you dont use any fault handler, it will go to default handler called error hospital and the instance will be marked as failed.
  3. Some cases, we can catch the error defining the fault handler and send the error details to the caller / mail integration and marked the called sub integration as completed.

OIC - About Virtual File System(VFS)

Virtual File System:

  • The Virtual file system (VFS) enables us to store files and internally use references to these files in the message payload.
  • We can also map the VFS file's content to a string element.
For example,
  • The FTP adapter uses fileReference for reading / writing a file without a schema. fileReference is also a reference to a file stored in the VFS.
  • The Rest adapter supports the multipart attachment and application/octet-stream features. The attachment is stored in a staging area and an attachmentReference is generated. The attachemntReference key is sent as part of thr message payload and latet fetches the attachment instance from the staging area.
Map the content of a staged file attachment toa string element by converting the content to a base64 string. 

Following 2 xpath functions are used:
  • encodeReferenceToBase64(String reference): input - the reference and Output - base 64 encoded content of the file.
  • decodeBase64ToReference(String base64String): Input - base64 encoded value and Output - reference or location of the file.

Available reference or relative path:
  • fileReference
  • attachmentReference
  • streamReference

When an attachment is stored in the vfs, a key is generated to retrieve the attachement later time. The key is shown in the mapper as attachementReference/fileReference/streamReference. The key is propagated with the OICS as part of the payload. For rest adapter streamReference is used. For FTP fileReference is used. Depending on the adapter type, the appropriate reference gets created.  Using the reference , we can fetch the content of the file and use or map the content as needed.

Tuesday, December 7, 2021

OIC - How to modify XSLT for complex logic externally using Jdeveloper tool

Sometimes we may need to do some complex map logic which sometime not possible in OIC itself. In that case, we can add the complex logic externally using Jdeveloper tool and import back to OIC.

Steps in Brief:

  1. Export the OIC .iar file
  2. Open Jdev and import the .iar file as service bus resources
  3. After import, open the XSLT map and add your logic and test accordingly.
  4. Import the modified XSLT file back to OIC.

Implementation steps:

Export the OIC Integration

Import from Jdev

Choose "Service Bus Resources" option

Select "Zipped /Archived resources

After import, modify the map

Deactivate OIC integration and edit and select mapper activity and select More options

Select Import

Select the updated XSLT

After import , save , activate and test

See the updated logic is working as expected.

Wednesday, November 24, 2021

ODI - Sql query to monitor TEMP size and resize

Query to monitor the temp total, used and free space in mb:

Select A.tablespace_name tablespace , D.mb_total, SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free

From v$sort_segment A, ( select, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total from v$tablespace B, v$tempfile C where B.ts#= C.ts#

Group by, C.block_size) D

Where A.tablespace_name =

Group by A.tablespace_name, D.mb_total

Query to Resize the temp file space:

Alter Database TEMPFILE 'tempfile' RESIZE 300G

Wednesday, November 3, 2021

ODI 12c - Standalone and J2ee agent configuration

Steps to configure Standalone and J2ee agent:

Go to ODI installed path Oracle/Middleware/Oracle_Home/oracle_common/common/bin and run config 

Create a new domain.

Select templates to be installed required for Agents

Create an Admin server

Create ODI manage servers.

Create Machine

Create Standalone agent

Run installNodeMgrSvc

Services.msc in run and start it


Start the standalone agent

Once weblogic started, login console and check alll servers, deployment etc and test your created both standalone and j2ee agent.

From Weblogic, you can go to Deployments -- OracleDIAgent -- testing  to test the oracle j2ee agent.

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