ODBC Connection to Oracle database- no data returned from Views

user641396

HI,

I Have created an  ODBC Connection to my Oracle Database.  All working fine. However-  when i write queries to any views-  no data is returned.  when i write queries to the actual tables-  all is ok.

example :

select *  from apps.fnd_territories_vl ;   this returns no rows.  i am thinking it might be to do with some language configuration setting -  but i do not know how this is setup with Excel or ODBC.

the above query does return rows in Toad or SQL developer.

Anyone have any ideas ?

i recently installed Power BI desktop-  so not sure if that has any relevance

kind regards

Alan

Ashish Sahu-Oracle

Please confirm which application you are using to connect to Oracle database through ODBC driver?

I have observed such issue when SSRS is used. There are configurations in SSRS to allow views.

John_in_Florida_5646

this is worth a try:  Run this query in both SQL Developer and your ODBC client; 

  "Select SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') as CURRENT_SCHEMA from DUAL;"

if they're different, then try this from within your ODBC Session:  "ALTER SESSION SET CURRENT_SCHEMA = xxxyyyzzz"   ----- {{{where xxxyyyzzz is the current_schema that you saw in your SQL Developer session.}}}

Are you using the same login credentials in both places?

user641396

hi John,

thanks for the reply.  i am connecting to Oracle through Excel-  using an Oracle client ODBC connection .  do you know where i could set the schema name ?

the current schema value is APPSRO  in sql developer.  this is also the user name i am using.  but still no data returned from the Views. i am using the tnsnames.ora to connect to the Oracle database

thanks

Alan

John_in_Florida_5646

>> i am connecting to Oracle through Excel-  using an Oracle client ODBC connection 

That's new information.  Not to be the person who lectures, but it helps to provide as much up-front information, well, up front. 

The alter session command is normally run from within the session, once established.  The way Excel wraps the connection up, I don't believe it provides that option.

There is an extreme minimalist query tool application called Query Express, it's a way to connect to many platforms, including oracle.  Try that, and test out your query that way, it's a way to run the raw query via ODBC knowing for sure that few things are interjecting themselves into the process.  (There are many SQL clients out there.  Query Express is convenient as one of the most extremely minimalist of them all, it's nice for specifically testing/confirming connectivity. It doesn't even need to go through in installation process to run.) 

Also, your sample query refers to the schema "APPS", not "APPSPRO".  That doesn't necessarily mean anything, other than it's a possible clue.  Have you double then triple checked that the server name in your TNSNAMES matches the server information you're putting into SQL Developer when you connect that way?   Try "select * from APPSPRO...." too. 

Also run "Select count(*) from ..." the view.  Do you get a valid response (including 0) or is it potentially hiding an error?  Obscure possibilities include obscure permissions rules perhaps.   You ahve a couple things you can try in the meantime though.