giovedì 14 novembre 2013

Oracle Tip: Oracle Warehouse Builder (OWB) import table error

I have installed the latest version of Oracle Warehouse Builder 11g 64 bit on a Windows pc (my work pc, actually) since I needed to connect to a customer OWB ETL implementation. 
Everything looked ok (I could connect to the OWB Workspace, navigate through the project, open and deploy mappings, etc.), since I have tried to import new tables in the Oracle Schema. 
As soon as I clicked on Import Database Object, I got the following (and cryptic) error:

SQL Exception 
Persistent Layer Error: SQL Exception
Class name: CacheMediator
Method Name: getSQLResult
Error Message from Persisten Layer: ORA-02085
If I clicked on OK, the next step was a grey window with much more known error Java null pointer exception.
I really needed to import the new tables I had created to be able to work, so I started Googling the error, with no success. After a considerable amount of time I got on this note:

"Note the property "User Global Name", it specifies the unique name of the database, which is composed of the database name and the domain, in the form of database_name.database_domain. If you see the error ORA-02085: database link *** connects to *** during the import of metadata of database objects into OWB. It may mean the database has the system parameter global_names set to true, but you did not select the option "Use Global Name" of the Oracle location, as a result, the generated database link name for the import doesn't include the global database name and hence cannot access the database."

Basically I needed to specify the DB global name in each location that specified a connection to the DB. 
If you do not know the global name of your DB, this SQL query can give you a precious hand. 

SELECT * FROM global_name;

So unregister your location, specify the DB global name option and register the location again.

Nessun commento:

Posta un commento