URGENT ODAC 5.x problem when trying to access dba_users(and dba_'views') views with Oracle9i

silverspoon · Post by **silverspoon** » Fri 05 May 2006 14:49

Hi there,

I'm using Delphi 7 and ODAC to connect to Oracle9i as sys/pwd with ConnectMode as SYDBA. The connections are succesfully made; but whenever I am trying to query dba_users or similar dba_ views I am getting an error like "table or view does not exist". It is working fine with Oracle8.

Usually when I connect to Oracle9i using iSQL*Plus as SYSDBA another dialogbox asks me for username/password. So is there anything extra to be included with the ConnectionString?

Please help in this regard

Challenger · Post by **Challenger** » Sat 06 May 2006 12:25

Please try to execute these SELECT SQL statements using SQL*Plus. To connect to Oracle as SYSDBA you should use the following command conn SYS/PWD@SERVER AS SYSDBA.

silverspoon · Post by **silverspoon** » Sat 06 May 2006 13:01

Thank you for your reply.

All these DataDictionary views are working and fetch rows in SQL*Plus. The application I created using ODAC returns results for some of these dictionary views when I logged in as SYSTEM/manager.

Do applications created using ODAC need any special privilege?
Do I have to include 'AS SYSDBA' in my connection string (ConnectionString property) apart from specifying ConnectionMode as SYSDBA ?

Challenger · Post by **Challenger** » Sat 06 May 2006 13:58

Please specify situation when you can view data in SQL*PLUS and can not do this using ODAC with the same user and valid connect mode (ConnectMode property)

Guest · Post by **Guest** » Mon 08 May 2006 12:14

ok! here is the entire scenario.....

My aim is to create an application that can display all the database objects(tables, views,synonyms etc.) of all the users. So I have to log on to the Oracle server as SYS AS SYSDBA and query some data dictionaries like dba_objects and others. The components I am using are OraSession and OraQuery and uses the option Net := True and in the ConnectionString I specify ConnectionString := 'sys/sys@hostname:port:sid' and ConnectionMode as SYSDBA and I can successfully connect to Oracle9i (but in Oracle8 I have to specify the ConnectionMode as Normal though).

In Oracle8 and Oracle10g I can query all the datadictionary views and everything is working properly. BUT with Oracle9i I am unable to query views such as DBA_OBJECTS.... and when I log in as SYSTEM/manager I can query some of the data dictionaries but not all of them.

I query all the data dictionaries in SQL*Plus without any problem.

silverspoon · Post by **silverspoon** » Tue 09 May 2006 12:09

ok! here is the entire scenario.....

My aim is to create an application that can display all the database objects(tables, views,synonyms etc.) of all the users. So I have to log on to the Oracle server as SYS AS SYSDBA and query some data dictionaries like dba_objects and others. The components I am using are OraSession and OraQuery and uses the option Net := True and in the ConnectionString I specify ConnectionString := 'sys/sys@hostname:port:sid' and ConnectionMode as SYSDBA and I can successfully connect to Oracle9i (but in Oracle8 I have to specify the ConnectionMode as Normal though).

In Oracle8 and Oracle10g I can query all the datadictionary views and everything is working properly. BUT with Oracle9i I am unable to query views such as DBA_OBJECTS.... and when I log in as SYSTEM/manager I can query some of the data dictionaries but not all of them.

I query all the data dictionaries in SQL*Plus without any problem

Challenger · Post by **Challenger** » Wed 10 May 2006 11:35

Please try to connect with Net option set to False for TOraSession component. Then query DBA dictionary views.

silverspoon · Post by **silverspoon** » Sat 13 May 2006 09:37

I tried with both Net option False and True but no result.
Have you tried it on your system with Oracle9i? Is data dict. views working in your system?
In Oracle's Fundamentals-I study guide they says SYSTEM/manager is used by oracle's tools and utilities. Do this OCI apps require any special privilege in Oracle9i?

Challenger · Post by **Challenger** » Mon 15 May 2006 14:17

We tried to query dictionary views on Oracle 9i server and succeeded. To access DBA dictionary views it is enough to log on to the server as SYSDBA.

silverspoon · Post by **silverspoon** » Tue 16 May 2006 08:04

Hi there,

It is very unfortunate for me that my program is not working in any Oracle9i server with SYS account. :(

What I did in the program is that : there are options for entering Username, Password, Mode, Host, Port and SID. On pressing the connect button the mode (SYSDBA/SYSOPER/Normal) is assigned and for the rest a connection string is formed and Connect Method is called. In the case of SYS the connection is properly made but unable to query data dictionary saying table or view doesn't exist.

If it won't bother you please download a copy of the program from this link : [url]http://www.stringriver.com/urdsetup.exe[/url] It is just 1.94MB, a setup file, and will NOT install any dll.

Challenger · Post by **Challenger** » Tue 16 May 2006 09:48

Please specify the version of ODAC you use.