Oracle ODBC connection

rmcclure · Post by **rmcclure** » Fri Nov 09, 2007 1:05 pm

I set up an Oracle Wire Protocol ODBC connection to a data source.

When I test the connection from ODBC Data Source Administrator it connects succesfully.
When I try to import a table from that ODBC connection in Designer. I see all the tables in the Oracle DB and I am able to import it.
When I create a the DRS stage I am able to load all the table columns.

But when I view data I get:
GIL_DEM_ITEM..DRS_SRC_ITEM: [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00942: table or view does not exist
GIL_DEM_ITEM..DRS_SRC_ITEM: SQLFetch: Error retrieving results from server.
GIL_DEM_ITEM..DRS_SRC_ITEM.DSLink1: DSP.Open GCI $DSP.Open error -100.

I don't understand how I can use the ODBC datasource to pull the table and columns into the DRS source but the view data gives me an error.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 09, 2007 4:10 pm

Are you using the same user ID/password in the authentication fields in the DRS? Are you the owner of the said tables? (If not, try qualifying the table with the owner name in the DRS stage.)

flashgordon · Post by **flashgordon** » Sat Nov 10, 2007 6:12 pm

oracle odbc is fussy. As Ray said, it definitely doesn't treat you like you logged in with sqlplus. All tables should be fully qualified to schema level, like:

pfdev.ps_jrnl_ln

Use odbc to look at all the tables in "Import"/ODBC. That will tell you what odbc thinks the table names are.

... Flash