ORA-00904 importing Oracle 8i tables from BODI XI 3.0
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.
Fina Wu replied
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.