ODI 12c - Procedure and use options

Procedure in ODI:

• A procedure in Oracle Data Integrator (ODI) consists of a series of commands executed in sequence. Commands contain code that can be executed by database engines, the operating system where the Agent is running, or directly by ODI. 
• You can also define options in the procedure to control its behavior at run time. A very useful property of procedures is that they are reusable and can be inserted into packages. Thus, just as procedures comprise commands, a package can contain several procedures as steps.
• You use procedures in a package or a scenario, or you can run them directly from the Designer Navigator.

Implementation Steps:

Step1: Designer -- Projects -- your project -- procedure -- new procedure

Step2: Define Procedure name, and source and target technology if needed.

Step3: Tasks -- Add new task

Step4: Created couple of tasks. For 1st task provide name

Step5: Target command -- select technolgy, context, logical schema, and command.

Step6: Need to provide source command if you want to select some fields.

Step7: Mention the same for remaining 2 commands

Step8: For this 3rd command, used source command to select the data from source emp table.

Step9: Assign the selected source fields in the target insert values using :fieldname

Step10: Save and run it.

Note: Select the Ignore Errors check box so that the procedure runs even if this command fails.

Step11: To use options, go to Options and add option

Step12: Added 3 options

Step13: Go to Tasks and option section and select the options to be used for a particular task.

For more details:

https://docs.oracle.com/en/middleware/fusion-middleware/data-integrator/12.2.1.3/using/procedures.html#GUID-9D85E78E-DA03-419C-866F-57F4F9AE9EA3

ODI 12c - database Free space check sql and resize datafile

Query to check the total space in mb, free space in mb and percent free for UNDO_9 tablespace.

Select

a.tablespace_name,

sum(a.bytes)/(1024*1024) total_space_MB,

round(b.free,2) Free_space_MB,

round(b.free/ (sum(a.bytes)/(1024*1024))* 100,2) percent_free

from dba_data_files a,

(Select tablespace_name, sum(bytes)/(1024*1024) free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name ='UNDO_9'

group by a.tablespace_name, b.free;

Query to manually resize db datafile for tablespace undo_9

ALTER DATABSE DATAFILE '+Data/...path/datafile/undo_9...' RESIZE 800G

The above command resize the datafile to 800 gb.

ODI 12c - Activate or Deactivate your schedule on load plan

Here we will show the steps to activate or deactivate your schedule created on load plans.

Steps:

Step1: Go to Operator tab -- Scheduling -- All Schedules -- select your schedule and expand and rightclick on Golbal/logical agent -- open

Step2: select status Active or inactive and save.

Step3: Go to Load plan and Scenarios -- double click on your load plan

Step4: Enable or disable the steps. Generally need to disable or enable 1st parent steps.

Why we need to disable or enable steps in load plan:

Sometimes we see that though you have inactivated the schedule, still its running so to avoid this scenario, we can also disable the steps in load plan as well.

OIC - Unable to convert a string in the xml to a java.sql.date - while inserting into database

Scenario: We have received an input date as YYYY/MM/DD format and when we are trying to insert it into a database, got below error:

Unable to convert a string in the xml to a java.sql.date.

Solution: Convert the YYYY/MM/ DD format to  YYYY-MM-DD format. You can use translate() function and convert the "/" to "-".

translate(datefield, "/","-")

OIC - validate date format as MM-DD-YYYY

I use this regex for validating MM-DD-YYYY:

function isValidDate(subject){
  if (subject.match(/^(?:(0[1-9]|1[012])[\- \/.](0[1-9]|[12][0-9]|3[01])[\- \/.](19|20)[0-9]{2})$/)){
    return true;
  }else{
    return false;
  }
}

It will match only valid months and you can use / - or . as separators.

ODI 12c - Loading Flat file to SQL table

Here, we will read a comma separated csv file and sends data to target Oracle table.

Steps:

File format:

Step1: Topology - Physical Architecture - File - File Generic

Step2: File data server - New Physical schema

Step3: Provide directory

Step4: Create new logical schema

Step5: Designer - new model folder - new model

Step6: provide all details like name, technology and logical schema name

Step7: create new data store

Step8: map and provide LKM and IKM and flow control as false.