ORA-00904 importing Oracle 8i tables from BODI XI 3.0

[Former Member]

We are using Data Integrator XI 3.0. Our local repository is a Oracle 10g database.

When trying to import tables from Oracle 8i data source to the local repo Oracle 10g datastore, we encounter this error.

BODI-1112338: Error: Cannot import metadata.

Oracle <XXX> error message for operation <OCIStmtExecute> : <ORA-00904: Invalid column name>

Understand it is out of support, however, this Oracle 8i DB is owned by other App Team and we were told DB upgrade is not an option.

Is there a way to import tables from Oracle 8i data source? We need the data from this Oracle 8i DB for our ETL jobs.

[Former Member · Accepted solution]

In my knowledge Oracle 8i will not support datastore version so you have to use 9i datastore

[Former Member · Accepted solution]

Thank you for your reply.

I have created datastore 9i in my local repo and tried to import table from 8i db but I get same error message.

I tried with creating datastore 8i in my local repo and import table again fail with error ORA-00904.

I also created datastore ODBC with Data Source Name and TNS pointing to our 8i db, and was able to import table from 8i. Then, I created dummy job to read a table from this ODBC Datastore and write to Ora10g template table. However, when I tried to execute this job, I get this error

CON-120302  ODBC call <SQLDriverConnect> for data source <XXX> failed: <[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified>. Notify Customer Support.

My objective is to be able to read from 8i and write to 10g.

Using 9i datastore doesn't seems to work for my case. Is there another way besides using 9i datastore?

[Former Member · Accepted solution]

though 8i was not supported in DI XI R3, I think it used to work may be it was fixed in later patches, this release is already end of life so there is no way for you to get latest patch of XI R3

you can use ODBC, did you configure same DSN on the job server machine also ? if your Job Server is on different machine then it's possible that the DSN is missing on job server machine

[Former Member · Accepted solution]

Yes, you are correct. The job server is on different machine. May I know how to configure DSN on the job server?

[Former Member · Accepted solution]

how did you setup that in Designer ?

go to control panel -> Administrative Tools - > Data Sources (ODBC)

if you are on 64-bit Windows machine then go to

C:\windows\SysWOW64

and double click on odbcad32.exe, click on System DSN and define Oracle DSN with the same name as you used on Designer Machine

[Former Member · Accepted solution]

Thank you for your reply.

I figured that there might be firewall blocking my job server machine and the machine where ora8i db is hosted. I tnsping the 8i host and port from job server machine and get TNS:no listener. I can tnsping 8i host from Designer machine.

I'll try to execute the dummy job again after I solve this firewall issue, hopefully by then we can read from 8i and write to 10g.

[Former Member · Accepted solution]

We figured 2 ways to import Oracle 8i tables from DI XI 3.0.

1. via ODBC

Create ODBC datastore in Designer, and configure System DSN with Data Source Name and TNS pointing to 8i db, then you can import table from 8i db. Configure System DSN in Job Server as well.

2. via 10g datastore config

Copy the create table scripts from 8i DB and execute the scripts in any 10g DB (might need to change the schema name before execute). Create Oracle 10g datastore in Designer with 2 configurations (datastore config, not system config). 1 config is 10g connecting to 8i DB, second config connects to any 10g DB. Set the second config as default. Import tables from 10g DB. Set the first config as default. Rename the owner of tables just imported to 8i owner name.

Both ways are working. We adopt option 2 whereby one config is for import and another config is for executing ETL job.

I thank kalpana korrapati and Manoj Dhyani for valuable input.