ODI - Read local Flat csv file and insert to Oracle database

Steps:

• Go to Topology and under Physical Architecture, choose File technology and create new data server. 
• Provide dataserver name.
• Under created dataserver , create new physical schema 
• provide the name, directory path as directory(schema) and directory(work schema).
• Create a context and add the logical schema name
• Choose Technology as Oracle and create new data server.
• Provide data server name
• User and pwd 
• Choose jndi connection or use credential file. Here we chose use credential file and selected the path.
• Under created dataserver, create new physical schema
• Provide name
• Provide db user as schema,(schema and work schema)
• Context and add logical schema
• Go to Design tab and create a model folder 
• Create a model for file 
• Provide name, technology as File, choose created file logical schema and save
• Under the file model, create a datastore 
• Provide name, choose a sample test.csv file as resource name.
• Go to files and provide the format like delimited, no of headings, unix or ms dos os, field separator as ","  etc
• Go to attributes and reverse engineer, you can see all the file fields here.
• Under the create file datastore, view data
• Create a model for oracle db
• Provide name, choose technology as Oracle , logical schema
• Reverse engineer and provide mask , here %POC%
• Selective Reverse engineering and select all the required options and it will show your table and click on reverse engineer.
• Projects and create a new project and ceate a new folder and go to mappings and create a map
• Drag and drop created data stores and map all the fields
• Go to physical and choose LKM as LKM file to SQL and IKM as IKM Oracle insert or IKM Oracle control append. And flow control as false as we dont have any PK on datastore.
• Run the map and go to operator tab
• Open session list and date check all steps and also verify in table where rows inserted or not.

POC Screenshots:

ODI - IKM Oracle Incremental Update Vs IKM SQL Control Append

Yes for bulk/large load ODI recommends to use Control append. The basic functionally of Control append is the do a Insert into the target table by selecting from the source table/file. You can't do row by row checking in this KM.

Incremental Update : Based upon the PK defined in the target data store, this KM will check row by row and do insert/update (will capture changed data).

Control Append : Blindly do a bulk insert in to target table and no changed data will be captured. It wont do truncate and insert whereas to aviod duplicates in data, have a PK defined in target table so whenever duplicate data comes it can be prevented or go for CKMs. And temp table will be Created and Dropped after every load in both the KMs.

OIC - Retry logic

Why we need retry logic: Suppose we have an API (any ftp call or service call) which we are calling from OIC. During the invoke, sometimes we are getting timed out or other errors. In this case, we may need retry logic to retry the call for a business limited attempts after specific time of intervals.

Logic steps:

• Take an assign with 2 variables.  runiteration="true" and counter=number(1)
• Take a while activity and use the condition runiteration="true"
• Take a scope
• Call the API which can fail
• Go to fault handler >> default handlet
• Take a switch and use condition counter <4 
• Add a wait for 59 sec
• Increase counter = counter +1
• Otherwise add runiteration = "false"

Detailed steps with POC screenshots: 

This will retry 4 times to read file from a ftp locarion.

OCI links

OCI links:

1. oci-oracle-cloud-infrastructure foundation 2021 exam dump
2. oci-db-active-sessions-check-in performance hub
3. odi-steps-to-increase-overall-adw storage size
4. OCI - Create a Hello World default Oracle function in Oracle Cloud Infrastructure console
5. OCI - How to get Tenancy OCID, User OCID Private key and Fingerprint details
   
6. OCI - Create an API Gateway and route to REST OIC Integration
7. OCI - API Gateway to route to oracle function and test the API
   
8. OIC - Create Secret in OCI Vault to store your keys and access from OIC
   

OIC - Secure Rest API with OAuth Security policy

OAuth is open authentication protocol to protect the resources. It is the industry standard protocol for authorization

It is authorization framework enables a third party application to obtain limited access to an HTTP service, either behalf of a resource owner or by allowing the third party application to obtain access on its behalf.

High level Steps:

• Register a Trusted application in Identity Cloud Service(IDCS)
• Extract the client id, client secret & scope
• Obtain the access token
• Hit the rest endpoint using access token

Detailed steps:

Login to IDCS

Search with Id

Go to application page

Add an application

Confidential application

Provide the app name

Provide allowed Grant type as required. We will use resource owner here.

Add scope

Select  myoic url for api call.

Select both.

Select the 1st url till all.

Provide access token properties.

Click finish

Application added.

 

Activate

Copy the client id and secret.

Copy the scope url till all.

Open postman and provide idcs url with /oauth2/v1/token

Select Oauth and provide username as client id and password as client secret.

Go to body and select x-www-form-urlencoded and provide grant_type, scope, username as oic user and password

Hit send

You will get the access token.

Below url you can use to see all the api trigger services.

Open the api url and authorization in header section as bearer access token.

Following rest trigger connection where we have select security policy as OAuth 2.0