Can we create procedures for views in ODI 11g?

Question

Can we create procedure for views in Oracle data integrator 11g ? As I am aware of procedure in Oracle Data Integrator (ODI) to create and populate a relational table.

Can any one suggest me.Thanks in advance.

Answer 1

You can create an ODI procedure and add a new step. In the Properties pane for that step, expand Target Command and choose the Technology of you database (for instance Oracle) and select the right Logical Schema. Then in the Command box you can type your SQL query :

INSERT INTO AIF_OPEN_INTERFACE (BATCH_NAME, COL01, COL02, DESC1, COL03, AMOUNT)
    SELECT BATCH_NAME, COL01, COL02, DESC1, COL03, AMOUNT FROM MY_VIEW

Make sure the schema used for connexion in the dataserver has INSERT privilege on AIF_OPEN_INTERFACE.

If the source data is coming from another dataserver, you will need to use a different approach to move the data from source to target dataserver. One of them is to use the query you posted in comment for the Target command, and a Select on the view for the Source Command. Target Command :

INSERT INTO AIF_OPEN_INTERFACE ( BATCH_NAME, COL01, COL02, DESC1, COL03, AMOUNT )
    VALUES ( :employees, :entity_id, :department_id, :employee_category, :account, :amount )

Source Command:

SELECT BATCH_NAME, COL01, COL02, DESC1, COL03, AMOUNT FROM MY_VIEW

However, I would recommend using an interface instead of a Procedure. You can reverse-engineer your view into the ODI model and use it as a source in an interface.

Alternatively, you can use an interface with the IKM SQL as a Source. It becomes interesting if you reuse the same query in more than one mapping. You can find more information and a download link here : https://blogs.oracle.com/warehousebuilder/entry/odi_11g_simple_flexible_powerful