DSXchange

Posted: **Tue Oct 28, 2008 12:21 pm**

Hi All,

Importing metadata from Orcacle schema is failing with below error message, any idea on how to resolve this.
I am able to import metadata from SQL server DB with out any errors.

Unable to get list of columns catalog function 'SQLColumns' on table

Repository is Oracle Schema
Connection is ODBC (Ascential wire protocol driver for Oracle)

Source is Oracle schema
Connction is ODBC (Oracle driver)

Thanks
Srimitta

Posted: **Tue Oct 28, 2008 12:52 pm**

Do you have SELECT privilege on the Oracle system tables?

Posted: **Tue Oct 28, 2008 1:00 pm**

Yes I have SELECT privileges and able to query same table from TOAD without any issues.

Thanks
Srimitta

Posted: **Tue Oct 28, 2008 1:03 pm**

Sorry, I didn't mention one thing.

I selected 20 tables and able to import for 4 tables but for rest of tables I have error message.

Posted: **Tue Oct 28, 2008 1:42 pm**

Are they in the same schema/have the same owner, and can you import them one at a time? It might be a timing issue.

Posted: **Mon Nov 17, 2008 1:59 pm**

Hi Ray,

I tried to import metadata one-by-one, still same error.

What wonders me is, PS imports metadata for some tables from same schema.

Is it ODBC driver or ProfileStage setup or something else I ignoring or missing, any idea what I am going wrong.

Posted: **Mon Nov 17, 2008 11:46 pm**

I've really run out of ideas. I assume they're all ordinary tables. You've said you have all requisite privileges, even to system tables, and the fact that you can import some tables' definitions seems to bear that out. I'd take a punt that the problem is in how the ODBC mechanism is set up; after all, ProfileStage simply executes SQLColumns() with the appropriate arguments. Establish an ODBC trace to find out precisely what requests are being issued.

Posted: **Tue Nov 18, 2008 12:32 pm**

Hi Ray,

Thanks for pointing me to look at logs.

What I found in PS logs is

Code: Select all

main::WriteColumnFile: Unable to get list of columns using catalog
function 'SQLColumns' on table 'MARKET' due to:
CoreOdbc::_ExecCatalogFetchAll: Problem encountered during FETCH: 
------- DBI Result Information -------
	SQL  State  :  '22003'
	Error Number:  '-1'
	-- Error  text --
	[Oracle][ODBC]Numeric value out of range. (SQL-22003)(DBD: st_fetch/SQLFetch err=-1)

What I did is.
Changed ODBC driver from Oracle native ODBC to Ascentail Oracle (Data Direct) driver, it worked like charm.