This place is for open discussion. You can keep your ideas, views, opinions or if you are interested to post an article. Also do comment if you would like to see any specific post on ODI or Data Warehouse.

184 comments

hi sir,
very helpful this site…………………

You are welcome Manohar. Feel free to post your doubts here.

What is remote file in odi? And what is the purpose of the remote file?

If you want to process any file that is available in other system. So if odi is installed in your local and you want process those remote files then you have to use one agent running on your remote system.

Hi Sir,
Thanq So much sir for ur supporting……….

Hi, My question is
PRC_CD,
RT_TBL,
CNDTN_DSCNT_VALUE,
VLD_FRM_DATE,
VLD_TO_DATE are the five columns in target table. Here I am using IKM oracle control append. The problem is when ever I execute the interface there are multiple number of duplicates are appearing. Also I used Truncate true option. This is the problem. Help me out here.

Thx

Please provide the actual count you are expecting in target and the count of C$table and I$ table. Go to operator and open Load Data step and insert flow into I$ table step. Better you can send the screenshot to mail@bhabaniranjan.com
Thanks

i have 10 interfaces in packages.if i got error at 6th interface then how can i rollback previous all 5 interfaces?

i have 10 interfaces in packages.if i got error at 6th interface then how can i restart from 6th interface?

For 1st one: You have to make the dml operation for all interface in one transaction and at the end you have to commit that is after 10th interface.

For 2nd one. You have to delete the drop temp table step from the LKM, CKM,IKM ( Might be the last step of each knowledge module. Just check it once from your side ). So that when ever you will restart the session all records will be available in your temp tables. At the same time you have arrange the transaction for INSERT ROWS STEP (i.e. I$ to Target Table). So that this transaction wont be executed.

how to run all transactions in one transaction in odi?

Can u explain these two steps in more detail. I am facing almost same issue and looking for solution.

Regds,
Aniket

Hi Bhabani,

Can you please explain how to make 5 interfaces to run in one single dml transaction.

Thanks,
Linda P

Please use one KM for all 5 interface and set the commit to false in each interface. In the last interface set the commit to true. It should work. Let me know how is it going or else i will post on this.

Hi sir,

How r u doing .Hopping you are doing well..

This site is very help full for learners. we will keep in touch with you.

Thanks

You are welcome Srikanth. Feel free to post your doubts.

i am trying to load data from flat file to oracle data base.
date format in file is DD-MON_YYYY
how can i give date format in interface in odi.
i am using to_date() funtion in interface.
i am getting invalid date format error.

Okay tell me your target table NLS format.

1. Is it mandatory to install weblogic in odi 11g?
2. What is the main purpose of weblogic?
3. Since We r using 10g for longtime, wan to understand main advantages of 11g compared fo 10g and is it easy to upgrade?

Can u pls help us to understand better in detail..

As i said in your previous thread http://bhabaniranjan.com/odi-11-1-1-6-installation-on-windows-server-2008-64-bit/ that weblogic is not mandatory. Please look into that. Regarding upgradation, you can upgrade only repository and the full installation of ODI Studio. ODI 11.1.1.6 is not a patch release.
Ofcourse its easy to upgrade.

MY oracle one SERVER NAME IS REPA,
ANTHOER oracle SERVER NAme IS MISREPL both are in different servers. and i am using LKM oracle to oracle(db link).
ODI-1228: Task INT_DBLINK (Integration) fails on the target ORACLE connection STG.
Caused By: java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified
i am unable to access synonym.please help me.

create or replace view REPA.C$_0XX_TR
(
C1_TJD,
C2_CID,
C3_BOO,
C4_TYPE,
C5_GRP,
C6_POAM,
C7_BALINT,
C8_DUIN2,
C9_CRLMT,
C10_IRN,
C11_TDUE,
C12_CHKHLD,
C13_WDLMT,
C14_ZSBU,
C15_BAL,
C16_MCHG,
C17_LCHG,
C18_ACR,
C19_CR,
C20_DR,
C21_CRCD
) as
select * from (
select
XX.TJD C1_TJD,
XX.CID C2_CID,
XX.BOO C3_BOO,
XX.TYPE C4_TYPE,
XX.GRP C5_GRP,
XX.POAM C6_POAM,
XX.BALINT C7_BALINT,
XX.DUIN2 C8_DUIN2,
XX.CRLMT C9_CRLMT,
XX.IRN C10_IRN,
XX.TDUE C11_TDUE,
XX.CHKHLD C12_CHKHLD,
XX.WDLMT C13_WDLMT,
XX.ZSBU C14_ZSBU,
XX.BAL C15_BAL,
XX.MCHG C16_MCHG,
XX.LCHG C17_LCHG,
XX.ACR C18_ACR,
XX.CR C19_CR,
XX.DR C20_DR,
XX.CRCD C21_CRCD
from REPA.XX
where (1=1)

create synonym STG.C$_0XX_TR
for REPA.C$_0XX_TR@remote

insert into STG.XX_TR
(
TJD,
CID,
BOO,
TYPE,
GRP,
POAM,
BALINT,
DUIN2,
CRLMT,
IRN,
TDUE,
CHKHLD,
WDLMT,
ZSBU,
BAL,
MCHG,
LCHG,
ACR,
CR,
DR,
CRCD
)
select
TJD, CID,
BOO,
TYPE,
GRP,
POAM,
BALINT,
DUIN2,
CRLMT,
IRN,
TDUE,
CHKHLD,
WDLMT,
ZSBU,
BAL,
MCHG,
LCHG,
ACR,
CR,
DR,
CRCD

FROM (
select
C1_TJD TJD,
C2_CID CID,
C3_BOO BOO,
C4_TYPE TYPE,
C5_GRP GRP,
C6_POAM POAM,
C7_BALINT BALINT,
C8_DUIN2 DUIN2,
C9_CRLMT CRLMT,
C10_IRN IRN,
C11_TDUE TDUE,
C12_CHKHLD CHKHLD,
C13_WDLMT WDLMT,
C14_ZSBU ZSBU,
C15_BAL BAL,
C16_MCHG MCHG,
C17_LCHG LCHG,
C18_ACR ACR,
C19_CR CR,
C20_DR DR,
C21_CRCD CRCD
from *STG.C$_0XX_TR*
where (1=1)
) ODI_GET_FROM

i am getting following error

ODI-1228: Task INT_DBLINK (Integration) fails on the target ORACLE connection STG.
Caused By: java.sql.SQLException: ORA-12154: TNS:could not resolve the connect identifier specified

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Okay, just run this code in target databse using sql developer. Because your staging is on target so this code should work fine as the C$ and I$ must be there.

Now have you tested the same interface using LKM sql to sql and IKM SQL Control APend ? Let me know. Thanks

i am using lkm oracle to oracle(dblink).bcz source contains 30 crores records

could you tell me about lkm oracle to oracle(db link) process

Hi Sir Good Evening………………..

Q) How Many Work Repositories Can be Created in a project

Manohar, you can create multiple work repository for single master repository. So there is no specific number. All work repository will use information stored in master repository (topology, agents etc). And projects are stored in work repository. So you can not access project stored in work repository A inside WR B. You need to import /export the project to other WR.

Thnq So much sir…………

Is Any difference between scheduler agent and listener agent?

Customizing Knowledge Module (KM) in ODI

Hi Sir,

This is Maheswara Reddy,ur site is very helpfull………

what is difference between odi 10g & odi 11g?

how many types of flat files in oracle data integrator?

I dont think there is any number. You can reverse any flat files in ODI. Excel or MDB files needs one odbc bridge to integrate with odi.

Hi sir,

I created one interface in flatfile ,source is oracle and target is a flatfile?
how to create in flatfile? pls give me with screen shots.

how to create multi connections procedures in oracle data integrator?

source is oracle and target is oracle how to create multi connections procedures?

I am new to ODI.Can you please tell the procedure to create physical and logical schema with snapshots and what are all the initial steps to create a transformations in ODI11g starting from the basic steps. I heve searched in many websites but couldn’t get the enough details. Thanks in advance.

Hi Rajan,
how r u doing…

I have 10 interfaces in package.if got error in 6 th interface how can i restart from 6th interface
and how can i rollback first five interfaces?

If interface 6 fails then you have to start it manually from step6. Then the flow will move forward.
If you are using loadplan then you must have option to restart from different steps.

For rollback: If you are using one KM for all interface then select the dml operation in one transaction for your KM and commit the same in the last interface.So even if any interface fails there wont be any data loaded to the target.

Hi Niranjan,

how many types interfaces in oracle data integrator ?

how create complex interface in odi?

What is your exact requirement. There is no complex interface. It depends on your design and requirement.

Hi Sir Good Mng………………

Q) Any difference between normal interface and temporary interface in oracle data integrator?

Manohar, you can exploit temporary interface in different ways which you cannot do in a normal interface. You use temp interface as a source for other interface which never happens in normal interface. You can convert a complex query into temp interface and use it as inline view in other interface. Here you can temporary tables aswell.

Good Afternoon, Sir.. My question is that when i am doing table to table mapping, I have to make the target table empty or insert values there?

Good Afternoon Sushree.
Make your target table empty. So that you can see whether the records got inserted or not. If you want to update the records in target table then select IKM Sql Incremental update KM.

Hi Bhabani ,

I have created a View on a table and for the first time i executed the view with out CDC ,
all the records from source view to target table got transferred successfully and after that i added CDC on that view and added a row in source table
and the respective view captured the new row ,
and now J$ table captured that record and when i executed that intefrface with CDC it worked . and i have deleted a row in source table and the row got deleted in
respective view but J$ table didn’t captured and when i executed nothing got deleted in target table , and fater i tried in updation the filed in a row but not
reflected in J$ view.

Seems it is working for only first time .

Thanks ,
Karthik

Hi Bhabani ,

Today i have tried to implement CDC on Views , i have imported the JKM which you have provided me , i have developed interface by dragging view in source and table in target , i have executed it before checking the Jounalization data only box , all records from source to target transferred successfully , now i have checked the Jounalization data only box and i have added a new row in source from backend then immeadiateley it got reflected in view and i executed that interface again then i can see the new row in target also , this is because of CDC on View and i have done updation also , it worked successfully , but when i delete a row in source and execute that interface it is not working .

Is there any seperate process to handle deleted records in CDC ?

Thanks ,
Karthik

1st question will be : Are you inserting/updating/deleting thru view or table ? If its view then thats fine. But if its on the base table then your trigger is not going to work since yo have created on the view (instead of). Now your question will be, how it is working in this case (for insert and update not for delete). Right ? its because you have used IKM incremental which is updating the target table based on your key column. It means your instead of trigger is not in use at all. Thats why your delete is not going work.

So the resolution which is coming to my mind is a materialized view. I will try to post on this soon.

Ohh ok ok

Hi Sir,

Already i told you before CDC on views in my Project and i explan below what is the process is going on so can you provide me any help.
what can i do for instead of every day truncate & load..and they want only insert and updated records only.
my turbo image source file was already have updated file so we i can start my process can you plz tell me ….

There loaded the data from turbo image to ‘X’ schema ( same naming tables ) of ms sql server its a jst truncate & load.
After they are creating views for X schema tables and moving(Copy what ever the data in the x schema that data will be in view in y schema ) to another schema ie ‘Y’ using procedures.
like create schema, Dest Schema, Get_Src schema,Genate views and Generates indexes on tables of ‘y’ schema and again all things done & loaded into ‘ y’ schema only….
( like: ie act as a Source & Target )
and created view on ‘X’ schema tables in ‘y’ schema , those views are used asa source and load into y schema tables.
So, at last there get the table from the ‘y ‘ schema and also loading into the ‘y’ schema in Ms sql server,

They told me like you don’t implement on Turbo image technology ( Source )
bcz its a updated file and it’s truncate and load every day.its taking long time for execution and its every time it loads truncate and load.
So you will implement CDC ON another place.
one more thing is, They loaded data from turbo image to ‘x’ schema of Ms Sql server.. using LKM Sql to mssql , IKM sql to sql Append and CKM Sql.
these are customized KM’s…….and next level is ‘y’ schema to ‘y’ schema
In y schema have the view of x schema as source and loading into y schma target table.
now where i can implement CDC

Hi Babani,

I’m facing this issue but little bit different…i.e;

we are implementing CDC on
my Source is Turbo image and Target is ms sql server
But we don’t have a JKM for Turbo image, So what i should do this situation.
if i want to use customize the JKM, How it write could you pls tell me steps ?
which LKM , IKM’s are impot ?
after importing KM’s In mapping level Flow tab any conditions i have to give ( Target level )?

And one more information is,
There loaded the data from turbo image to ‘X’ schema ( same naming tables ) of ms sql server its a jst full load.
After they are copied (moving) data ( same Tables with data ) from’ X’ schema to ‘Y’ schema in Ms sql server using procedures.like create schema, Dest Schema, Get_Src schema,Genate views and Generates indexes on tables of ‘y’ schema and again all things done & loaded into ‘ y’ schema only….
( like: ie act as a Source & Target )
and created view on ‘X’ schema also, those tables are used in some packages according to the requirement and mostly using ‘y’ schema tables for mapping and everything…
So, at last there get the table from the ‘y ‘ schema and also loading into the ‘y’ schema in Ms sql server,

Now, Client Requirement is implement CDC on Existing projects, ( Above Process already developed by some one and now we are doing support and implemening CDC on that )
Where can i implement the CDC process on turbo image ( But turbo image don’t have JKM ) (or) ‘x’ Schema (or) ‘y’ schema ?

They told me like you implement on Turbo image technology ( Source ) only bcz its a full load its taking long time for execution and its every time it loads truncate and load.
Other than CDC any option is there for inserting/ update and delete ……..on turbo image source technology.
one more thing is, They loaded data from turbo image to ‘x’ schema of Ms Sql server.. using LKM Sql to mssql , IKM sql to sql Append and CKM Sql.
these are customized KM’s…….if any customized JKM for turbo image will work (or) any existing JKM’s are supports turbo image technology…
how to write can anyone guide me.

Hi,
We have 5 interfaces in package, while executing 3rd interface got failed then I found the problem and rectified but thing is I need to execute the package from that step onwards, how can I do this? Please help me. Thank you in advance.

Regards,
Venkat

When you right click on the failed interface you must be getting restart option. Now move forward from that point

Hi Bhabani,
I am using ODI 10.1.3 version. I tried to restart the failed interface as per your above response, but I did not find that option. Please let me know where I need to find that option in my version.

Thanks & Regards,
Venkat

Hi Sir,

1) How you handle without knowledge module and through query in SCD?
2)What are the problems did you faced while loading excel file to table?
3) We have 7 or 8 interfaces in a package but at 5 stage interface got failed but while re executing we need to execute from 5 stage onwards. How it is?
4) What is the information we have in master repository?
5) Can we have more than one master repository?
6) How many work repositories you are maintaining in your project?
7) What are fact tables and dimension tables occurred in your project?
8) What is the format of down loaded components like interfaces, packages, etc
9) How system knows that is existing record where it will check either source side or target side?

Thanq Sir,
Manohar……..

1) How you handle without knowledge module and through query in SCD?
Without KM ? hmm probably you have to go for generic process. (IKM incremental and delete target row where tgt.key_col not in source_key_col)
2)What are the problems did you faced while loading excel file to table?
Not yet. Just only invalid fetch size error and buffer string error.( already discussed in my blog)
3) We have 7 or 8 interfaces in a package but at 5 stage interface got failed but while re executing we need to execute from 5 stage onwards. How it is? better go for load plan if the design have no looping.
4) What is the information we have in master repository?
what ever you can see in topology.
5) Can we have more than one master repository?
yes you can have.
6) How many work repositories you are maintaining in your project?
Six Repo.
7) What are fact tables and dimension tables occurred in your project?
8) What is the format of down loaded components like interfaces, packages, etc
Naming Convention ??
9) How system knows that is existing record where it will check either source side or target side?
Not Clear about your requirement.

Hi Sir
Thanq So Much Sir……………….

Hi Sir Good Mng………….

how to restart a session from a particular step using scheduler?(restarting points)

how to send the rejected data into a flat file when i am using join condition?

how to tune mapping in ODI and what is low level ELT and high level ELT Design?

Hi Sir Good Mng…………….

Can you join two sources without common column? In Oracle data integrator?

Have you created fact table and dimension tables?In Oracle data integrator?

What is your daily activity?In Oracle data integrator?

How you find which the latest record in SCD type2 transformation is?In Oracle data integrator?

Have you created sequence? When will you use?In Oracle data integrator?

Hi sir
these concepts are very helpful………….
thank you……….

Hi Sir…………..

look up is possible on file system in oracle data integrator

Thanks&Regards
Manohar

wish you happy new year

Same to you Srikanth.

Hi Sir Good Mng…………

1)How to excute 2 interfaces paralley in Oracle Data Integrator?

2)Can we use SCD in SnowFlake Schema in Oracle Data Integrator?

3)How to client finds the error records in production(Is there any programmers in project to track the errors records)?

4)How to write sub_query in ODI(Procedure,Yellow Interface)

5)How to truncate the target table before loading into target?

6)How to import objects from one environment to another environment?

7)Group By only one column in Oracle Data Integrator?

8)Any pre_requisites to catch the error records in Oracle Data Integrator?

9)Main use of Yellow Interface?

10)What is the difference between yellow interface and normal interface?

11)How to find error records without error table?

12)Difference between Context And optimized Context?

13)How to use equal operator in filter?

14)How to loop 10 xml files in a directory?

15)Can we use same IKM for SCD1 and SCD2?

Thanks&Regards
Manohar Reddy

Hi sir,

While trying to view data for src_sales_person datastore, i am getting the errors as follows:

Line 0: ODI-40469: Value was incorrect for column 3: Could not transform 15/01/2001 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 1: ODI-40469: Value was incorrect for column 3: Could not transform 22/02/1999 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 2: ODI-40469: Value was incorrect for column 3: Could not transform 20/04/2000 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 3: ODI-40469: Value was incorrect for column 3: Could not transform 10/06/1988 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 4: ODI-40469: Value was incorrect for column 3: Could not transform 28/02/1988 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 5: ODI-40469: Value was incorrect for column 3: Could not transform 24/09/1992 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 6: ODI-40469: Value was incorrect for column 3: Could not transform 14/03/1995 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 7: ODI-40469: Value was incorrect for column 3: Could not transform 11/03/1999 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 8: ODI-40469: Value was incorrect for column 3: Could not transform 18/03/2003 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 9: ODI-40469: Value was incorrect for column 3: Could not transform 29/05/2001 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 10: ODI-40469: Value was incorrect for column 3: Could not transform 29/05/2000 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 11: ODI-40469: Value was incorrect for column 3: Could not transform 12/08/2001 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 12: ODI-40469: Value was incorrect for column 3: Could not transform 25/08/1992 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 13: ODI-40469: Value was incorrect for column 3: Could not transform 30/01/1992 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 14: ODI-40469: Value was incorrect for column 3: Could not transform 28/02/1994 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1
Line 15: ODI-40469: Value was incorrect for column 3: Could not transform 28/03/1993 00:00:0 to date using pattern dd/MM/yyyy hh:mm:ss at position 1

Can anyone help us finding the issue…

I believe the issue is with the date value and the format. Can you please send me the file ? if its a table check the nls format in your database.

Hi Bhabani,

Thanks alot for your response.

Here i am accessing datas from flat file.
The file name is SRC_SALES_PERSON (Text document) from demo environment.The entries are as follows:

10 Georges Hamilton 15/01/2001 00:00:00
11 Andrew Andersen 22/02/1999 00:00:00
12 John Galagers 20/04/2000 00:00:00
13 Jeffrey Jeferson 10/06/1988 00:00:00
20 Jennie Daumesnil 28/02/1988 00:00:00
21 Steve Barrot 24/09/1992 00:00:00
22 Mary Carlin 14/03/1995 00:00:00
30 Paul Moore 11/03/1999 00:00:00
31 Paul Edwood 18/03/2003 00:00:00
32 Megan Keegan 29/05/2001 00:00:00
40 Rodolph Bauman 29/05/2000 00:00:00
41 Stanley Fischer 12/08/2001 00:00:00
42 Brian Schmidt 25/08/1992 00:00:00
50 Anish Ishimoto 30/01/1992 00:00:00
51 Cynthia Nagata 28/02/1994 00:00:00
52 William Kudo 28/03/1993 00:00:00

Hi Bhabani
hope u r doing well. My query is can I create more than 1 master repository in my project?

Yes you can create multiple master repository. But keep in mind that you cannot establish any relation between master repositories as they are not bound to each other. Let me know your requirement so that i can suggest whether it is productive to create multiple master repository or not.

Anindya Chatterjee / Reply

Hi Bhabani,
I have a query on error handling on odi.Actually I want to invoke a .sh script using OdiOsCommand and I get an error like this:-
ODI-30038: OS command returned 127.
Could you please tell me why this is happening(or what is its meaning)?
and please show me the way how can I handle this type of error in odi(as for another .sh file I am getting an error like this ODI-30038: OS command returned 1).

Thanks
Anindya

Error Details:

com.sunopsis.dwg.function.SnpsFunctionBaseException: ODI-30038: OS command returned 127.
at com.sunopsis.dwg.tools.OSCommand.actionExecute(OSCommand.java:294)
at com.sunopsis.dwg.function.SnpsFunctionBase.execute(SnpsFunctionBase.java:276)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execIntegratedFunction(SnpSessTaskSql.java:3437)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.executeOdiCommand(SnpSessTaskSql.java:1509)
at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:44)
at oracle.odi.runtime.agent.execution.cmd.OdiCommandExecutor.execute(OdiCommandExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1889)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:580)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:513)
at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1066)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

Anindya Chatterjee / Reply

Hi Bhabani,
1)could please tell me the difference between OSCommand and OdiOScommand?
2)could please write blog on odi error handling?
Thanks
Anindya

hi sir,
can u tell me..
1)what is yellow interface?what is the use of yellow interface?where it is used?
2)what is load plan?where it is used?

when i was reverse engineering the datastores i’m getting the message database connection is not avilable.what could be the reason,can anyone let me know this…

Are you able to tnsping that database ? Are you able to connect via toad ? Is the connection test status successful ?

Hi Bhabani,

while we creating the interface we mention the optimization context as Global.

what is the significance of optimization context and context?

can u please guide me…

Thanks&Regards,
Arjun

Arjun, optimization context used to figure out how it is going to build its flow tab. For example you have one data server for source and target in dev. Then the flowtab will use the IKM only not LKM. Now you generated the scenario and promoted to other env. Suppose in that env you have two dataserver for source and target. Guess what will happen. Your promoted scenario will not work and it will fail. Because, you have two dataserver and the flow tab will be needing one LKM and one IKM which is not available in the scenario.
Thats why before generating scenario you need to under stand the topology information of other env. Then generated the scenario accordingly.

thank you for your replay sir…….

Hi Bhabani

can u explain me about “ODISqlUnload” in detail. when we use it .

Hi Bhabani,

How r u doing,
Recently i started working on ODI earlier i used to work in OBIEE

My question :
I am trying to load the data from File to ORACLE Database and give all the proper datatype in datastore and when i was trying to run the interface below error i am getting
and we are date column in the table. Could you please resolve this issue

Line 0: ODI-40469: Value was incorrect for column 3: Could not transform 201002230000000 to date using pattern ‘YYYYMMDDHH24MISS’ at position 1
Line 0: ODI-40469: Value was incorrect for column 4: Could not transform 201102220000000 to date using pattern ‘YYYYMMDDHH24MISS’ at position 1
Line 0: ODI-40469: Value was incorrect for column 4: A ParseException occured saying Unparseable date: “201102220000000”

Try using to_DATE(substr(‘201002230000000′,1,14),’yyyymmddhh24miss’) in the mapping section of interface.

Hi,

I installed ODI 11g 11.1.1.6.0 for Win 7 with 10g DB R2 and WLS 10.3.5 successfully without any issues.

Created DEV_ODI_REPO Repository also without any issues.

Created Master Repository snpm1 and Work Repository as snpw1.

Created the Agents (Physical and Logical) and tested successfully.

Created the data servers for respective technologies

1) Technology Oracle –

Data Server – ORACLE_ORCL_LOCAL

Physical Schemas – SALES_DEV & SALES_PROD and

Logical Schema as ORACLE_ORCL_LOCAL_SALES

2) Technology FILE –

Data Server – FILE_GENERIC

Physical Schema – FILE_GENERIC.E:/oracle/Middleware/Oracle_ODI1/oracledi/demo/file

Logical Schema – FILE_DEMO_SRC

3) Technology – XML

Data Server – XML FILE

Physical Schema – GEO_D

Logical Schema – GEO_DIM

4) Technology – Hypersonic SQL

Data Server – HSQL (Is this the correct name or some other name is recommended ? )

Physical Schema – HSQL_LOCALHOST_20001_Default and HSQL_LOCALHOST_20001_Default

Logical Schema – HSQL_DEMO_SRC and HSQL_DEMO_TARG

All the Mappings are done properly using Development & Production context for Physical and Logical Schemas.

I am able to create the Models for Oracle, FIle and XML with successful Reverse Engineering (standard with development context )

with all the data displayed correctly.

The issue starts here

The Main issue is for Hypersonic SQL where I am not able to get the Reverse Engineering.

Technology – Hypersonic SQL I have defined the data server as HSQL

Is this correct data server or some other name for data server is suggested ?

I am able to start the startdemo.bat file successfully opening the 3 dos prompt windows as

a) Oracle DI Demo – Repository Server

b) Oracle DI Demo – Source Server

c) Oracle DI Demo – Target Server

Later I make entries on the JDBC tab od HSQL – Data Server as

JDBC Driver : org.hsqldb.jdbc.JDBCDriver

JDBC Url : jdbc:hsqldb:hsql://localhost

Test Connection – ok with successful connection.

Prior to this I already run the script created schemas and inserted data to the SRC_ tables in my 10g Database as

ORDERS – containing the Tables as SRC_CITY, SRC_CUSTOMER, SRC_PRODUCT, SRC_REGION…..

In the Designer I create a new Model as HSQL_SRC

Technology – Hypersonic SQL

Logical Schema – HSQL_DEMO_SRC as defined earlier in logical schema.

Reverse Engineer Tab – Selected as Standard Context selected as Development from drop down list.

When I right click on HSQL_SRC model for reverse engineer, it executes, however it does not display any tables inserted

as shown for ORDERS Schema.

Please correct me where I am wrong in giving the name for data server ? or some other place. Awaiting your quick response ASAP, as I am stuck with this
issue for last 3 days.

Thanks

Ajaz Ahmed

salam boss
did u find any solution
i can view data in model but after testing control its give me error
ODI-1217: Session SRC_CUSTOMER (8001) fails with return code -5582.
ODI-1226: Step SRC_CUSTOMER fails after 1 attempt(s).
ODI-1231: An error occurred while performing a Control operation on datastore SRC_CUSTOMER.
ODI-1228: Task SRC_CUSTOMER (Control) fails on the target HYPERSONIC_SQL connection HSQL_SRC.
if u have any solution guide me plz
thanks

just change Later I make entries on the JDBC tab od HSQL – Data Server as

JDBC Driver : org.hsqldb.jdbc.JDBCDriver

JDBC Url : jdbc:hsqldb:hsql://localhost:(use port number) for src is 20001)

and in schema use PUBLIC it work with me fine

I need to create an XMl file from database tables. I did everything and while executing the package,I got one error while creating work table

Error : Invalid schema name

Hi All,

I’m facing this issue i.e;

we are implementing CDC

my Source is Turbo image

Target is ms sql server

But we don’t have a JKM for Turbo image, So what i should do this situation.

if i want to use customize the JKM, How it write could you pls tell me steps ?

which LKM , IKM’s are impot ?

after importing KM’s In mapping level Flow tab any conditions i have to give ( Target level )?

Thanks in advance,

Hi Bhabani,

Can i implement CDC on Views ?
if it’s possible can you plz explain me process?

It will work but there are certain complexity. If you can handle then that’s good for you. 1st refer the discussion in OTN and let me your progress.
https://community.oracle.com/thread/2281913

Hi sir,

In the Topology Tab, regarding to the Physical (Schema) Architecture in Hypersonic SQL Technology, how will I create the HSQL_LOCALHOST_20001_Default? If it is a schema, what are the procedures to create this schema? same goes with HSQL_LOCALHOST_20001_Default?

Thank you so much sir.

Hi Experts,

How to create customized trigger(insert, update and delete) for the table instead of CDC trigger.

Thanks in advance…:)

Dear Bhabani,

I have installed RFI(Retail Financial Integration) for EBS to RMS integration. RFI installation gave me few readymate builds in ODI like Agent, Interface, Procedure and etc.

In my project we will be having some other technology interfaces. My quetion is
Can I use the same agent which I got with RFI intallation (as mentiiond above) for other interface? Or Should I need to create a new agent for my other interface ?
Please suggest me better way to proceed

I am writing data from a database table to flat file using odisqlunload. However the problem is that for some tables the count in source table and file is same, but for some file contains more no of rows. Any idea as to why this is hapenning?

More number of rows means what? Do you have duplicate records? What are the additional records you see in the file?

I checked the file and found that there are duplicate records that are being written to it. However this is happening for only some tables, for others it is working fine. Can you suggest what is the problem?

Mohammed khaja / Reply

Hi Bhabani,

how to CDC on synonym in ODI ?
As I am using ODI version 11.1.1.
After adding Add to CDC, I click on Start Journal. It gave primary key error. Then I have added a Primary key in ODI->MyModel->reverse enginered synonym->Constraints.
After that I have clicked on start journal then gave error as below
“ODI-1228: Task FND_LOOKUP_VALUES (Journalizing) fails on the target ORACLE connection EBIZ DS.
Caused By: java.sql.SQLException: ORA-38818: illegal reference to editioned object APPS.FND_LOOKUP_VALUES”
Can you please tell me how to resolve above error ?

Dear Sir,

Could you please tell me how to configure DB2/400 dataserver with ODI ?

Hi,
Can you please tell me detailed steps how to configure DB2/400 with ODI.
In one of the site they mentioned as it can be achived by using some jt400.jar file. But i dont have detailed information on it.

Dear Khaja,
Are you trying to achieve using Datadirect driver or native Driver?
You can download the driver from
http://www.java2s.com/Code/Jar/j/Downloadjt400jar.htm

Before that please go through below link
Please go through below link. It has detailed information on this.
http://docs.oracle.com/cd/E15586_01/integrate.1111/e12644/db2.htm#BABEIICI

If it does not work please ask your admin to ensure drivers are available.
Let me know if you need anything else.

Thanks alot bhabani, now I am able to connect ODI to DB2400.

Hi Bhabani,

I have few clarifications on DB2/400 configuration with ODI.
I have succssfully connected ODI with DB2/400 using Datadirect driver.

Do I need to install Run time agent on iSeries ?
If yes, can you plese tell me how to perform this activity.

Thanks
khaja

addition to above my quetion

I have created a simple interface that is from ORACLE to DB2/400
I have imported LKM as LKM SQL to DB2400(CPYFRMIMPF)for source (ORACLE)
In the above LKM description it says “An ODI Agent must be located on the AS/400”. I not sure how to achive this point.

Hi,

Can you please tell me, how to reverse engineer complex XML files in ODI.
For exmaple – below is my xml file


XStore
XStoreCre
8109
XStore_pub_1|2013.04.26 12:24:35.756|2

store
8109

2013-04-26 12:24:35.756 AST
8109AlDoaaeCAlDoaaeHARMAKTAFMAKMECMECSA10001DMohammed Abdultif2011-12-06T00:00:00.000+03:002011-12-07T00:00:00.000+03:002011-12-05T00:00:00.000+03:0031284412713storemanager_5007@nmc.com117291172Y100015030SAR1ENYYY10001000Y1000RAsia/Riyadh1000

F

When ever I reverse engineer above xml file, entire “message Data” coming into a single column.

I want to extract into a different columns.
How Can I achive above problem ?

Thanks
khaja

If you dont have any issues, can you please send me that xml file? There are might be some format issues i guess.

I have sent the file to your mailid: bhabaniranjan@gmail.com
I have pasted the same file(XStore.xml) below

XStore
XStoreCre
8109
XStore_pub_1|2013.04.26 12:24:35.756|2

store
8109

2013-04-26 12:24:35.756 AST
<XStoreDesc xmlns="http://www.oracle.com/retail/integration/base/bo/XStoreDesc/v1&quot; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><store>8109</store><store_name>AlDoaae</store_name><store_type>C</store_type><store_name10>AlDoaae</store_name10><store_name3>HAR</store_name3><store_add1>MAKTAF</store_add1><store_add2>MAK</store_add2><store_city>MEC</store_city><state>MEC</state><country_id>SA</country_id><store_pcode>10001</store_pcode><store_class>D</store_class><store_mgr_name>Mohammed Abdultif</store_mgr_name><store_open_date>2011-12-06T00:00:00.000+03:00</store_open_date><acquired_date>2011-12-07T00:00:00.000+03:00</acquired_date><remodel_date>2011-12-05T00:00:00.000+03:00</remodel_date><fax_number>312844</fax_number><phone_number>12713</phone_number><email>storemanager_5007@nmc.com</email><total_square_ft>1172</total_square_ft><selling_square_ft>911</selling_square_ft><linear_distance>72</linear_distance><stockholding_ind>Y</stockholding_ind><channel_id>1000</channel_id><district>150</district><start_order_days>30</start_order_days><currency_code>SAR</currency_code><lang>1</lang><iso_code>EN</iso_code><integrated_pos_ind>Y</integrated_pos_ind><copy_dlvry_ind>Y</copy_dlvry_ind><copy_activity_ind>Y</copy_activity_ind><vat_region>1000</vat_region><like_store>1000</like_store><copy_repl_ind>Y</copy_repl_ind><transfer_entity>1000</transfer_entity><tran_no_generated>R</tran_no_generated><timezone_name>Asia/Riyadh</timezone_name><org_unit_id>1000</org_unit_id></XStoreDesc>

F

I have sent you the corrected file. Let me know if you still see the issue.

I am able to reverse engineer corrected(your) xml file. Its working fine.

But, In our project, we will be getting XML files in the format which I have shared with you first time.

My concern, is it possible to reverse engineer the xmls withour changing its formate ?

I have sent detailed information to your mail as well.

Hi,

I have created an ODI procedure but while executing it , I am facing the following error :

SELECT TNAME FROM TABLE_NAME WHERE TID=#TERA_EXA_MIGRATION.LOOP
Exception getTargetTable(“METADATA_DESC”) : There is no Target Table

Please help.

Many Thanks.

Regards,
Thakur

Can you please send your procedure to my email id? I guess there is something missing.

Hi Bhabani,

I am trying to access a complex file from local server. I have placed the complex file and Schema file in same path.

When i create data server for complex file, the connection was successfull.

When i tried to create Physical Architecture for the same, i couldn’t see the Element name in drop down.

When i tried to reverse engineer that in Model i am getting the below error.

Error:
java.sql.SQLException: ODI-40865: No DTD named /home/oracle/safaricom/bank/cashmanagement/SCB/in/SWIFTM940.xsd was found so the XML schema could not be created
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchema.(SnpsXmlSchema.java:466)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchemaManager.createNewSchema(SnpsXmlSchemaManager.java:296)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchemaManager.getSchemaFromProperties(SnpsXmlSchemaManager.java:273)

But the same works fine if i place the file in Local machine (Desktop, not in server).

Could you please help me out in this?

Thanks & Regards,
Gokul M
+91 9880033598

I guess you dont have permission to create a DTD file inside “in” directory. Check your local machine and copy the DTD file generated by ODI to the Unix Machine.

Hi Bhabani,

Thanks for your response.

I couldn’t see any DTD in my local Machine, If a DTD will create in local were could i see that DTD.

I have tried by giving 777 permission to the folder path. but the issue still persist.
Thanks Once again.

Thanks & Regards,
Gokul M
+91 9880033598

If you dont have any issues, Can you please send that file to me?

Hi Bhabani,

Thanks for your response.

I couldn’t see any DTD in my local Machine, If a DTD will create in local were could i see that DTD.

Thanks Once again.

Thanks & Regards,
Gokul M
+91 9880033598

Hi Bhabani,

And also i have given 777 permission to my folder path and tried but same issue still persist.

Thanks & Regards,
Gokul M
+91 9880033598

Hi Bhabani,

We have clob data types in source in that we using KMs as LKM SQL TO ORACLE,IKM SQL CONTROL APPEND
when i am running the interface it failing with following error
Caused By: java.sql.BatchUpdateException: ORA-00942: table or view does not exist.
• what ever the clob columns not mapping to target then the interface executing successfully. Is there any Knowledge module customization required to achieve this, we are using ODI(11.1.1.5).
when i am testing it in 11.1.1.7 it executing successful, but our production environment is in 11.1.1.5.we have to do in this version only.
could you please suggest us.

Thanks
Arjun

In which step you exactly see this error message? Is this while loading to C$ or I$ ? You can try the KM suffix with PLSQL.

Hi Bhabani,

Thanks for your replay. While loading into C$ table it is failing.

Thanks,
Arjun

Hi Bhabani,

could you please provide any clue on this error.we are using IKM as IKM control append

Thanks&Regards,
Arjun.

Can you copy pest the generated code and complete error message here?

I am also getting the same error please help!!!!

Hi All,

I am loading a data from csv file to oracle table through odi,from Winscp we are extracting the files to odi for integration,the prblm is after loading into the table some blank boxes are coming in between data,when i try to copy those blank boxes its look like ���� ,after generating the report also it appearing like that only,why this boxes are coming how can i resolve this prblm plss help me to get over this.

Thanks!!

Hi Bhabani,

I have one query.

Is it possible to dynamically change the field Separator in File Datastore for interface?

Example – Currently the flat file is comma(,) separator in future if it is Pipeline(|) delimited. Can we handle this in Configuration?is that possible?

Please clarify whether we have this flexibility in ODI.

Thanks & Regards,
Gokul Nath M
+254 706 677 151

As of now I dont see any such dynamic configuration to build the logic. What you can do is to develop a java based parser to track the delimiter before the file is being processed. Then replace the delimiter with the actual delimiter used in ODI File Data store.
Let me know if it helps.

Hi Bhabani,

Thanks a lot for your Quick response.

How to replace the delimiter with actual delimiter used in ODI File Data Store during run time?

Please help me with some samples or example for the same so that i can refer.

Thanks & Regards,
Gokul Nath M
+254 706 677 151

I hope you are asking about field separator.
Create one array to store the expected list of separators. For instance (‘|’,’$’,’#’,’*’). Scan the incoming file and check the presence of any such separator. If found replace it with the ODI file separator. Take special care about comma in values ( normally comes in string such as comments, about me,text). This is normally surrounded with text delimiter.

Same applies for text delimiter.

Hi Bhabani,

Got it. Thanks a lot for your suggestion

You want to scan the file for delimiter, change the file delimiter in the file and load the file right?

Thanks & Regards,
Gokul Nath M
+254 706 677 151

Exactly though its bit slower at this moment. Will update you if i come across any other faster approach.

Updates: you can update the field separator in metadata tables before processing the file.
For example.
UPDATE SNP_TABLE SET FILE_SEP_FIELD=’\u007c’ WHERE TABLE_NAME=’YOUR_FILE_DATASTORE_NAME’;
Here \u007c is nothing but pipe and you can update it to \u002c which is a comma.
This way ODI will pick the field separator dynamically before executing the interface.

Hi Bhabani,

Is it possible to invoke ODI Scenario From Oracle APPS?

User will run one concurrent program after ODI program completes, the ODI scenario need to be invoked as one concurrent program from Oracle Apps, is that possible?

Using Triggers, Shell Script and Web Service we can access, is it possible to access from Oracle Apps?

Please give you suggestion on this.

Thanks & Regards,
Gokul Nath M

Hi Bhabani,

I have an XMLType field in a table ‘mytable’. I am trying to insert data into this table using ODI procedure

command on target:
INSERT INTO mytable VALUES (XMLType(bfilename(‘XMLDIR’, #filename),nls_charset_id(‘AL32UTF8’)));

command on source:
select #filename from anothertable;

where #filename is ‘xmlname.xml’ stored as a varchar in my ‘anothetable’.

I am getting error as below :
ORA-00904: “XMLNAME”.”XML”: invalid identifier

Do you have any suggestions for me?

Hi Julia, What I understand is that you have a table which stores the xml file names. For instance if I execute below query it will give result as follows.

Select filename from another_table;
Output:
file1.xml
file2.xml
file3.xml

…………
If my understanding is correct, you dont need to put that # on command on source. It should be as follows.
Command on source:

SELECT FILENAME FROM ANOTHER_TABLE

Command on target:

INSERT INTO mytable VALUES (XMLType(bfilename(‘XMLDIR’, #FILENAME),nls_charset_id(‘AL32UTF8′)));

Let me know if you were expecting something else.

Bhabani, Thank you so much for ur response.

Yes, My source_table has xml_file_name column.

I am using ” select xml_file_name from source_table” in my command on source. My mistake that I kept # with the query I shared with you.

I am still getting the same error.

The code for inserting xml file as clob into an xmltype field is
XMLType(bfilename(‘XMLDIR’, ‘filename’),nls_charset_id(‘AL32UTF8′))
if I am not wrong.

I am just trying to pass ‘filename’ as a variable from my command on source.

Julia

I just assumed that the column name is “filename”. Since you said its “xml_file_name”, your code should be like this. Notice I am using CAPS. Small letter doesn’t work.

Command on source:
SELECT XML_FILE_NAME FROM ANOTHER_TABLE

Command on target:
INSERT INTO mytable VALUES (XMLType(bfilename(‘XMLDIR’, #XML_FILE_NAME),nls_charset_id(‘AL32UTF8′)));

Hi,

We are facing an urgent issue in one of our data loads as given below.
We are getting below error when we try to connect to MS access databases through ODI 11g (11.1.1.7)

oracle.odi.runtime.agent.invocation.InvocationException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invoke(RemoteRuntimeAgentInvoker.java:265)
at oracle.odi.runtime.agent.invocation.RemoteRuntimeAgentInvoker.invokeTestDataServer(RemoteRuntimeAgentInvoker.java:780)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.remoteTestConn(SnpsDialogTestConnet.java:584)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$10(SnpsDialogTestConnet.java:581)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:558)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$5.doInBackground(SnpsDialogTestConnet.java:1)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:655)
at java.lang.Thread.run(Thread.java:662)

The error is signalled when we test the connection with the standalone agent through topology. fo local ( no agent ) we do not get this error.
We checked the JAVA version used in ODIParams.bat and changed it to 32-bit java and we have also created the DSNs usong 32Bit ODBC drviers.

Kindly guide us on the same ASAP.

Environment Details:
OS version: Windows 2003 64-bit
ODI Version: Odi 11g (11.1.1.7)
Java version: JDK_1.6.43 (32-bit)

Hi sir,

I am facing issue to populate an ODI variable value into target oracle table column in an interface.
I have assigned variable as alphanumeric datatype and my target oracle table column is of date datatype.

Could you please help

Please do a conversion using to_date(source_column,’dateformat’)

Hi Bhabani,

Thanks for the reply.I did the mentioned conversion using to_date function but its not working.

I am providing background of the overall procedure that i followed.

I have declared a variable runtime and its datatype is alphanumeric.
The refreshing query for variable is select to_char(sysdate,’dd-mon-yy’) from dual.
I am getting the variable value as 21-jan-15.
Now I have an oracle table column with datatype date where i want to populate this variable value.
Here I am using to_date(‘#odi_test.runtime’,’dd-mon-yy’) converson function, but getting error on execution of interface while inserting rows in table.

I am using ODI 11g.Could you please look into this.

can you post the error stack and the code generated for it?

Hi Bhabani,

Please find below the error message

ODI-1228: Task load_var (Integration) fails on the target ORACLE connection sampleconn.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: “JAN”: invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)

The code generated is:

insert into ODI_STAGE.LOAD_VARIABLE
(
ID,
DEPT
,DOR
)

select
ID,
DEPT
,to_date(#ODI_TEST.runtime,’dd-mon-yy’)
FROM (

select
C1_DEPT_ID ID,
C2_DEPT_NAME DEPT
from ODI_STAGE.C$_0LOAD_VARIABLE
where (1=1)

) ODI_GET_FROM

Hi Sir,

Can we create two targets in one interface, if yes how to create plz tell me ?

Thanks,
Sri

In 11g its not possible. But in 12c you can.

Hi Bhabani,

I have to import a column from csv file with value as £12,000.
I am using to_number(“Salary”,’U999G999G999D99′) in odi interface.
I m getting error as invalid number.

Could you please suggest on this.

Thanks!!

when i reverse engineer synonyms..i do not see the columns. please suggest how i can reverse engineer with all the columns. one of your post says..

Go to Data server properties tab. Then give

key= includeSynonyms and value= true

i did not understand this..please explain

Hello Bobby,
Open the dataserver under Oracle Technology and go to properties tab. Then click on plus button to add a row. You can refer below screenshot.
IncludeSynonyms

Thanks so much. My heart felt thanks to you.

Hi Bhabhani,

How does odi read files from remote server.In my case i have to read files from linux server. I have installed in windows. when i was trying to create datastore for files then it shows directory path not found.

Could you please help with that.

Thanks in advance

In that case you have to create an agent on linux machine. Now you can execute the interface using this agent in your windows setup.

I have requirement to process files those are resides at remote location (file topology server). When I am trying to accessing those using Jython script it is erroring out. But when I map that drive to local PC and try to execute the procedure it is working fine. I don’t want to map the drive and wanted to process the file just by giving IP address. Shall it possible? Please respond to this question.

You have to create an agent on the remote machine and configure it with your current master repository. Then while executing the jython script select the remote agent. It should now find the file.

Hi Bhabani,

I have already created agent but I don’t know how to use in jython script. Could you please help me. My requirement is to insert the file names into table so I am using below code.

import os
import fnmatch
import java.sql as sql
import java.lang as lang
import time

listingDir = ‘O:\scm_input_file’
dbConn = odiRef.getJDBCConnection(“SRC”)
myStmt = dbConn.createStatement()

file=os.listdir(listingDir)

for x in file:
if fnmatch.fnmatch(x, “*”):
#(mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime)=os.stat(listingDir+os.sep+x)
mtime=os.stat(listingDir+os.sep+x)[8]
myStmt.executeUpdate(” Insert into FILENAMES(FILENAME, TIMESTMP) values (‘%s’, ‘%s’)” %(x, time.ctime(mtime)) )

———-

Please let me know what needs to be done while passing the directory name (listingDir). Currently I have hardcoded it. I want it should pickup from that host where is agent running.

How do you execute this jython script? Is it inside ODI studio or outside ODI studio.
If it is inside ODI Studio, then put that code inside ODI Procedure. While executing the procedure, you select the context and remote agent.

I am running procedure inside ODI studio. My file topology is on Host – 10.9.10.68 and is on Agent – 10.21.123.104. Whenever I am trying to run the procedure I am getting below error.

————————-

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “”, line 11, in
File “C:\oracleodi\oracledi.sdk\lib\Lib\os.py”, line 279, in listdir
raise OSError(0, ‘No such directory’, path)
OSError: [Errno 0] No such directory: ‘\\mshs6290a-cifs.mountsinai.org\\GRPS2\\MSHSSCMDWH\\scm_input_file’

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:662)
Caused by: Traceback (most recent call last):
File “”, line 11, in
File “C:\oracleodi\oracledi.sdk\lib\Lib\os.py”, line 279, in listdir
raise OSError(0, ‘No such directory’, path)
OSError: [Errno 0] No such directory: ‘\\mshs6290a-cifs.mountsinai.org\\GRPS2\\MSHSSCMDWH\\scm_input_file’

at org.python.core.PyException.fillInStackTrace(PyException.java:70)
at java.lang.Throwable.(Throwable.java:181)
at java.lang.Exception.(Exception.java:29)
at java.lang.RuntimeException.(RuntimeException.java:32)
at org.python.core.PyException.(PyException.java:46)
at org.python.core.PyException.doRaise(PyException.java:219)
at org.python.core.Py.makeException(Py.java:1166)
at org.python.core.Py.makeException(Py.java:1170)
at org.python.core.Py.makeException(Py.java:1174)
at os$py.listdir$27(C:\oracleodi\oracledi.sdk\lib\Lib\os.py:280)
at os$py.call_function(C:\oracleodi\oracledi.sdk\lib\Lib\os.py)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyBaseCode.call(PyBaseCode.java:131)
at org.python.core.PyFunction.__call__(PyFunction.java:317)
at org.python.pycode._pyx55.f$0(:13)
at org.python.pycode._pyx55.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1204)
at org.python.core.Py.exec(Py.java:1248)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
… 19 more

——————-

Please let me where I am going wrong.

The error is itself self explanatory. Agent is not able to access the directory. Server 10.21.123.104 should see the path in 10.9.10.68. Actually the agent should have been in 10.21.123.104.
Are they both in LAN?

Yes. Both PC’s are in LAN. When I map the drive on server (10.21.123.104) and use that drive in Jython scripr (e.g O\…) then it is working. But I don’t want to map the drive. It should be accessed through agent or by placing path (e.g. IP/folder). Agent is on 10.21.123.104 server only.

Then you have to create the agent on Source server.

Hi,

I am using IKM (Oracle Incremental Update) , LKM (SQL to Oracle), No CKM. Whenever my interface submitted and if it has any duplicate rows then it gets error out at (Update existing rows ) procedure. My requirement is if there are any duplicate rows then it should delete one row automatically or process only one row. The C$ and I$ table has same count of records (12022). The error is occuring when I$ table start execute update existing rows procedure when is part of IKM.

Please let me know what needs to be done…

Thanks.

Gauri Chaudhari / Reply

Hello Bhabani,
Can you please guide on following?
If the table definition in database changes for e.g. the column “salary number(4)” changes to “salary Number(10)”.
Do I have to reverse engineer all the models(which are using the table) again and create/refresh new datastores in order to fetch its new defination in ODI studio?

Please Guide.

Thats correct. You have to reverse engineer the model/datastore to get new change.

Thank you so much for the quick reply Mr.Bhabani. I appreciate your help.

Hi,
I need help regarding ODI 11g,
I am using MSSQL Server 2012 database for ODI,
in one of target table i have given id_key column as an identity, while performing SCD Type2 i got stuck in error “updating existing row”,

and even i tried to use mssql server sequence but i am unable to use in odi..

ODI-1228: Task INT_SCD2 (Integration) fails on the target MICROSOFT_SQL_SERVER connection ODI_BI.
Caused By: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘from’.

and code is :
update T
set

from ODI_BI.dbo.TGT_Demo_SCD2 as T,
ODI_BI.dbo.I$_TGT_Demo_SCD2 as S
where T.Id = S.Id
and T.IsActive = 1
and IND_UPDATE = ‘U’

Hi Ekta,
Can you please send us a screenshot of your mapping and dimension behavior as we have given in below image.
http://dwteam.in/wp-content/uploads/2012/08/scd2_42.jpg

Hi,
i am unable to send screenshot because here is no option for upload those images..
In Target table i am having ID,Name,Salary,StartDate,EndDate,IsActive,ID_Key(Identity Column) Columns.

Mapping is done in this form:
ID=NaturalKey
Name=Add row on change
Salary= Add row on change
StartDate=Starting timestamp
EndDate=Ending TimeStamp
IsActive=Current Record Flag
ID_Key=Undefined

In interface i am giving ID as key, And ID_Key is blank no active mapping is define,
Please let me know if any solution.

Hi its working fine now, I just made change in IKM MSSQL SCD: Ignore updating exist row error.

Thanks for your reply 🙂

hell Odi team,

i am using ODI 12C how to import xml file as source and target file as oracle
while creating data server connection test
it shows odi-26039

va.sql.SQLException: ODI-40933: Unable to build XSD from DTD

how to import all the xml file from local machine in to odi 12c and load the tables into oracle

please find bello error i have get while test connection of data server
java.sql.SQLException: ODI-40933: Unable to build XSD from DTD.
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1243)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:203)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$1100(SnpsDialogTestConnet.java:62)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:400)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:396)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:961)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ODI-40933: Unable to build XSD from DTD.
at com.sunopsis.jdbc.driver.xml.SnpsXmlDTD.initialize(SnpsXmlDTD.java:374)
at com.sunopsis.jdbc.driver.xml.SnpsXmlDTD.initialize(SnpsXmlDTD.java:480)
at com.sunopsis.jdbc.driver.xml.SnpsXmlDTD.(SnpsXmlDTD.java:154)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchema.(SnpsXmlSchema.java:560)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchemaManager.createNewSchema(SnpsXmlSchemaManager.java:321)
at com.sunopsis.jdbc.driver.xml.SnpsXmlSchemaManager.getSchemaFromProperties(SnpsXmlSchemaManager.java:298)
at com.sunopsis.jdbc.driver.xml.SnpsXmlDriver.connect(SnpsXmlDriver.java:143)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:412)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:385)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:352)
at oracle.odi.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:331)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:110)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter.getConnection(OnConnectOnDisconnectDataSourceAdapter.java:87)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter$ConnectionProcessor.run(LoginTimeoutDatasourceAdapter.java:228)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
… 1 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 8
at oracle.xml.parser.v2.DTD2SchemaBuilder.getElementDecls(DTD2SchemaBuilder.java:175)
at oracle.xml.parser.v2.DTD.convertDTD2Schema(DTD.java:585)
at com.sunopsis.jdbc.driver.xml.SnpsXmlDTD.initialize(SnpsXmlDTD.java:370)
… 19 more

hi sravan did u resolve the issue if yes plz provide resolution

Hi Bhabani,
Please guide,
1-which lkm(lkm file to oracle (sqlldr/external table)) is best for 10 gb unix file loading
2.unable to use above lkm (lkm file to oracle (sqlldr) and lkm file to oracle (external table))
3-how to connect unix as the source file of odi.

1. Go for external table
2. Please justify with details
3. Create an agent on unix box and use it to access any files in that server

thanks for reply,
I am just checking lkm on windows server and getting error while use lkm file to oracle (external table)with logical agent=local(no agent)

org.apache.bsf.BSFException: exception from Jython:
Traceback (most recent call last):
File “”, line 44, in

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)

at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)

at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)

at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

java.sql.SQLException: java.sql.SQLException: ORA-06564: object DAT_DIR does not exist

at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:146)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(SnpScriptingInterpretor.java:322)
at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(SnpScriptingInterpretor.java:170)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(SnpSessTaskSql.java:2472)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:47)
at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(ScriptingExecutor.java:1)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
at java.lang.Thread.run(Thread.java:745)
Caused by: Traceback (most recent call last):
File “”, line 44, in

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)

at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)

at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)

at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

java.sql.SQLException: java.sql.SQLException: ORA-06564: object DAT_DIR does not exist

at org.python.core.Py.JavaError(Py.java:455)
at org.python.core.Py.JavaError(Py.java:448)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:177)
at org.python.core.PyObject.__call__(PyObject.java:355)
at org.python.core.PyMethod.__call__(PyMethod.java:215)
at org.python.core.PyMethod.instancemethod___call__(PyMethod.java:221)
at org.python.core.PyMethod.__call__(PyMethod.java:206)
at org.python.core.PyObject.__call__(PyObject.java:397)
at org.python.core.PyObject.__call__(PyObject.java:401)
at org.python.pycode._pyx5.f$0(:50)
at org.python.pycode._pyx5.call_function()
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1204)
at org.python.core.Py.exec(Py.java:1248)
at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:172)
at org.apache.bsf.engines.jython.JythonEngine.exec(JythonEngine.java:144)
… 19 more
Caused by: java.sql.SQLException: ORA-06564: object DAT_DIR does not exist

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2251)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2192)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:347)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:175)
… 33 more

Hi,

I’m trying to reverse engineer an object which is view using standard Reverse engineer strategy, but certainly i’m not able to do that.

It only show the datastore name i.e. view name under the model but have no columns in it.

Can you help me with this.

Thanks

how load the multiple files with different structures in odi
5o files are there how 25 files load to first day and next 25 files another day load to target

Hi Pavan,
Could you please explain your scenario?
what I can get from your question is:
“how load the multiple files with different structures in odi”
Ans: Create different models for them and load, its simple what else are you looking for.
Ques: 5o files are there how 25 files load to first day and next 25 files another day load to target
Ans: Assuming the files have the same structure and you just want to schedule them on different day.
Follow this:
first 25 files, develop a loop in odi to process the first 25 files and later archive them to some location.
Next day your directory will have the remaining 25 files , process them.
For the above processing job, develop a scenario and schedule it using an Agent, the processing will take care automatically.

Hi,
While revering the CSV file into ODI some 2k records are missing.Colud you please help me to fix it?Thanks in advance.

Hi Bhabani,

My ODI version is Build ODI_11.1.1.5, is it supports Oracle 12c DB and Any actions we need to take for that?
Can you please give me any solution.

Thanks in Advance..

Hi Srikanth,

I don’t have an infrastructure set like this, with 11.1.1.5 as the ODI version and 12c as the database.
However if you look at the way how ODI is designed, it is supposed to create Master and Work Repository in any DB that follows the standard RDBMS rules.
Moreover 12c DB has not changed the way how jdbc driver will connect to the schema, though the internal concepts have changed.
Please try to connect to the 12c DB using your ODI, I think it should work.

Hi Bhabani,

Do you have some material available or real time development by using SKM and CDC with web services?
Can you please share some of the information about this.

Hi i how can i send status report(success/fail) back to legacy system using webservices from after executing an mapping

Hello Bhabani

I am facing issue in connecting to Oracle Cloud Database with ODI12c. I followed below URL to connect to DB through ODI.
https://blogs.oracle.com/dataintegration/entry/odi_12c_and_dbaas_in

I am getting below error.
java.sql.SQLException: IO Error: Got minus one from a read call
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.doGetConnection(LoginTimeoutDatasourceAdapter.java:144)
at oracle.odi.jdbc.datasource.LoginTimeoutDatasourceAdapter.getConnection(LoginTimeoutDatasourceAdapter.java:73)
at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java:1258)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.getLocalConnect(SnpsDialogTestConnet.java:207)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet.access$1300(SnpsDialogTestConnet.java:65)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:407)
at com.sunopsis.graphical.dialog.SnpsDialogTestConnet$6.doInBackground(SnpsDialogTestConnet.java:403)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:961)
at java.lang.Thread.run(Thread.java:745)

Connection details are as follows:
jdbc:odi_ssl_oracle:thin:@//160.34.9.118:1521/EBSTRN1.compute-a419517.oraclecloud.internal

Thanks in Advance
Arun

Hi Team,

We are loading data from flat file to oracle data base by using attunity connections in SSIS, data is successfully loading into oracle 11G database, we we executed the same package with oracle12C data base data is not loading completely, only small amount of data is loading and after that data s not loading but it keep on executing, Can some one please help me here.

Lakshmi prasad / Reply

Hi Team,

We are loading data from flat file to oracle data base by using attunity connections in SSIS, data is successfully loading into oracle 11G database, we we executed the same package with oracle12C data base data is not loading completely, only small amount of data is loading and after that data s not loading but it keep on executing, Can some one please help me here.

Hi Lakshmi,

Could you provide more details on this. SSIS attunity connections will call and execute the same package that Oracle DB executes, this is interesting that the data is loaded using remote calls from SSIS, but not directly, please provide the details.

Hi Guru’s,

current email:- old@gmail.com
new email:- new@yahoo.com

All scheduled jobs are linked with current email (if any job will failed get an failure email), But I want to change the current email to new email.

using Procedure (Jython code) they did, inside procedure global variables (Email_receipnt, local_host, Local_Port like..) but now how to change emailid in Prod.

Is there any option, If I update the variables & Procedures in Dev that will reflect in Prod ?

Can you please advise me.

Note:-
1). I have changed the variable Default value: new@yahoo.com & Updated the Table as well in prod.
2). I have created the procedures using Jython code (notify/fail/finish like…) with update variables.

Thanks,
Sri

Hi All,

How to find all the Scenarios/Interfaces/Packages which are using the same ODI connection using repository tables?

ODI-30038 : OSCommand returned 127

Iam getting this error while loading data from MSSSQL to Oracle using ODI interface.Any solution plz

And what is that error?

Leave a replay to Swanand Wagh Cancel reply

required*

Are you a human? *