ODI 12C - Write file names from OS location to database

Usecase: Here we will use a procedure and jython script to take file names from OS location or mount location and write them to a database table. 

Step1: Create a database table to store the file names and other params

Create table poc_param_tbl (

SNO NUMBER,

FILE_NAME VARCHAR2(500),

PROCESSED_FLAG VARCHAR2(1),

INSERT_DT TIMESTAMP(6)

UPDATE_DT TIMESTAMP(6)

);

Step2: create database physical and logical architecture connection under Topology:

Step3: create a project, a variable and create a new procedure with 2 tasks - to trucate the table and to run the jython code.

Jython script:

import os

import datetime

sc=odiRef.getJDBCConnection("SRC")

cnt=0

filepath="#POC.LV_EPM_OBJ_STORAGE_SRC_FOL_PATH"

ct=datetime.datetime.now()

setFileNameQuery="insert into POC_param_tbl(sno, file_name,insert_dt,update_dt,processed_flag) values(?,?,?,?,?)"

for files in os.listdir(filepath):

   if files.endswith(".csv"):

      cnt =cnt+1

      sqls=sc.prepareStatement(setFileNameQuery)

sqls.setInt(1,cnt)

sqls.setString(2,files)

sqls.setTimestamp(3,ct)

sqls.setTimestamp(4,ct)

sqls.setString(5,"N")

executeStatement=sqls.executeUpdate()

sqls.close()

Notes:

1. Suppose any patching is done to the server where ODI studio installed, then we need to clear the cache and restart odi studio otherwise it will fail with "Script Engine not present error"

Path:

Cd /u01/oracle/mwh/odi/studio

./odi.sh -clean -initialize

2. Here, we have hardcoded the sourcr path in the project variable, that we can use as refresh variable and fetch from the database table.

ODI - Why ODI has two J$ views with CDC and Journalizing

The J$D view is called the data view. It stores the details of changed data. Other view stores status of data consumption by subscribers.

OIC - Create a Retry logic | To overcome putting file to AWS S3 bucket timeout issue

Usecase: We are trying to put file in S3 bucket using rest connection but unlucky that its getting failed and giving a timeout issue if its taking more than 2 minutes. So what we did, we have put a retry logic to try 3 times to put the files in S3.

Retry logic implemented:

1. Take a assign variable and assign 2 variables: v_S3FileStatus ="error" and v_S3Counter = 0.0
2. Assign file reference and PathAndFilename to move file to S3.
3. Take a while loop with condition: $v_S3FileStatus = "error" and $v_S3Counter < 3.0
1. Take a scope within the while loop
1. Drag and drop S3 Rest connection and configure to put files in s3.
2. Take a assign activity and assign v_S3FileStatus = "success"
4. Open the scope default fault handler and assign as => v_S3Counter = $v_S3Counter + 1.0 and v_S3FileStatus = "error"

Steps with screenshots:

OCI - How to get Tenancy OCID, User OCID, Private Key and Fingerprint

Tenancy OCID: get the Tenancy OCID from the OCI console on the Tenancy Details page.

Search Tenancy in the search box

Or from profile >> Tennacy

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.

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 available site like below:

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

OIC - How to invoke OCI Function from Oracle Integration Cloud

Usecase: Here, we will call a helloworld OCI oracle function from Oracle Integration Cloud.

This feature allows OIC to invoke custom code deployed in this serverless framework, thus expanding its range of capabilities.

Highlevel steps:

1. Create a rest connection with the function invoke endpoint, received from the OCI.
2. Create an integration and invoke function invoke endpoint and feed input name and get the response back from the function.

Detailed Steps:

Step1: First create a function in OCI console. You can follow my previous blog:

OCI Create a deafult helloworld oracle function in Oracle Cloud Infrastructure console

Step2: Create a Rest connection

To create rest connection, we need following information:

Connection type: REST API Base URL

Connection URL: this we will get from the function invoke endpoint.

Security: OCI Signature version 1

Tenancy OCID, User OCId, Private Key and Fingerprint: follow my below blog to get them from OCI.

OCI - How to get Tenancy OCID, User OCID, Private Key and Fingerprint

Step3: Create an Integration to call the function.

I have created an App driven Orchestration integration

Integration flow:

Configure expose side rest request and response part. One Input : inputName and output: Response

Call the created rest connection and configure function invoke part:

URI: get ot from OCI function endpoint

Verb: post

Select request and response payload as binary and text/plain.

Map the input to the function call. 

Map the function response to rest response.

Testing:

OCI - Create a Hello World default Oracle function in Oracle Cloud Infrastructure console

What are Functions:

"Oracle Functions is based on Fn Project. Fn project is an open source, container native, serverless platform that can be run anywhere, It's easy to use, supports every programming languages, and is extensible and performant"

These functions can be written in a variety of languages - Java , Python, Node etc. You write and deploy the code, Oracle takes care of provisioning, scaling etc.

How do Oracle Functions enhance the OIC experience?

Suppose you are porting SOA composites to OIC and you want to use of Java embedding in your BPEL process. Where we shall the code in OIC? Functions can be leveraged to implement business logic that can not be defined using standard OIC actions. We can use javascript feature but that to have its limitation upto a level.

Function Creation Steps:

Step1: Create an application 

Sign into the OCI console >> Navigation menu >> Developer Services >> Functions >> Applications >> choose the compartment >> Click Create Application

Provide Application name like helloworld-app. Select VCN and subnet in which to run the function.

Here , Selected a public subnet. >> click create.

 

Note: a Public subnet requires an Internet gateway in the VCN and a private subnet requires a service gateway in the VCN.

Step2 : Setup your Cloud Shell dev Environment.

On the applications page >> click your app , here, helloworld-app. >> click Getting started link >> click Cloud Shell Setup.

Step3: setup Fn CLI on Cloud Shell

Most of all details we will get in the getting started page steps. So dont worry of the <> bracket details.

A. Use the context for your region

fn list context

fn use context <region-context> 

My case region-context = us-ashburn-1

B. Update the context with the functions compartment ID

fn update context oracle.compartment-id <compartment-ocid>

C. Provide a unique repository name prefix to distinguish your function images from other people's.

fn update context registry <region-key>.ocir.io/<tenancy-namespace>/<repo-name-prefix>

Example:

fn update context registry iad.ocir.io/idsvxxxxxxxx/test-repo

D. Generate an Auth Token : click Generate an Auth token to display the Auth token pages >> click Generate Token >> Enter a meaningful auth token name and generate token >> copy the auth token >> close.

E. Log into the Registry using retrieved Auth Token as your password.

docker login -u '<tenancy-namespace>/<user-name>' <region-key>.ocir.io

For example:

docker login -u 'idsvxxxxxxxx/cloudconsole/xxxxx' iad.ocir.io

F. Verify your setup by listing applications in the compartment

fn list apps

Step4: Create , Deploy and Invoke your function

G. Generate a hello- world function

fn init --runtime java hello-java

This will create the following in the hello-java directory:

• func.yaml: function definition file
• /src directory: Contains source files and directories for the helloworld function
• pom.xml : a Maven config file that specifies the dependencies required to compile the function.

H. Switch into the generated directory

cd hello-java

Traverse following folders to see the created deafult java helloworld code:

 src >> main >> java >> com >> example >> fn >> HelloFunction.java

cat HelloFunction.java

package com.example.fn;

public class HelloFunction {

public String handleRequest(String input){

String name = (input == null || input.isEmpty()) ? "world" : input;

System.out.println("Inside Java Hello World function");

return "Hello, " + name + "!";

}

}

I. Deploy your fucntion

fn -v deploy --app helloworld-app

J. INVOKE or Test your function

fn invoke helloworld-app hello-java

Note: Suppose you want to create custom java code , then you have to modify the entry in the func.yaml and pom.xml files accordingly.

Step5: click the Functions >> see the created the hello-java function and invoke endpoint details.

Step6: Enable the logging:

References:

https://docs.oracle.com/en-us/iaas/Content/Functions/Tasks/functionsquickstartcloudshell.htm#