How to get all database names in oracle using c# ?

Question

1.00/5 (1 vote)

See more:

I like to know their is any way to get all database names in oracle using c#. My requirement is to display all database names in oracle. Then i need to list all tables, procedures in each database. I know how to take tables in each database. But i do not know how to list all database names using c#. I am using oracle 9i server.

Posted 9-Dec-13 4:58am

Member 10455053

Updated 9-Dec-13 5:00am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bowlturner · Answer 1 · 2013-12-09T05:13:00

Solution 1

First, I'm assuming you mean all Schema's, not databases, this is oracle after all.

To select a list of schema's you can use this. But you need to have valid permissions for it.

SQL

SELECT * FROM dba_users;

Of course not all schema's have tables so another way is to use this

SQL

SELECT owner, table_name FROM dba_tables;

which will give you the 'schema' and all the table names for that schema.

you can use similar calls to get the procedures, functions, Packages and the column names, datatypes and sizes as well. All that data is stored in system tables.

SQL

SELECT Owner, Object_Name, Object_type, Status FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE');

Posted 9-Dec-13 5:13am

bowlturner

Updated 9-Dec-13 5:16am

v2

Comments

Member 10455053 9-Dec-13 11:23am

I think this is based on users and these users list belongs to any particular database. I like know their is any way to get all the database names or all the service names in oracle 9i in c# code.

bowlturner 9-Dec-13 11:46am

In Oracle, each 'dba_user' in a specific database is a 'Schema' and can have tables and other DDL objects. Most databases have more 'users' than working schema's. The only way to get this from a database is Querying the database, this requires SQL.

In oracle different databases is a different thing and will need a separate connection for each one.

Sergey Alexandrovich Kryukov 9-Dec-13 11:24am

5ed. Worse thing is: such code in not portable between different RDBMS...
—SA

bowlturner 9-Dec-13 11:28am

right. I wrote code to compare Dev/Test/Prod databases on SQL server to look for changes in the DDL. But it was only useful for SQL Server...

Member 10455053 9-Dec-13 12:44pm

So their is no way to get these database names? i created 2 database like http://docs.oracle.com/cd/B10501_01/win.920/a95491/create.htm

bowlturner 9-Dec-13 12:50pm

What are you really trying to do? If you created actual oracle databases you should already know what the names are. Why do you need 2 databases? How come you are creating these databases if you don't appear to know what you are doing? I think we need more information.

Member 10455053 9-Dec-13 20:38pm

My requirement is to display all oracle databases from an oracle server. User can select any of it. Then based on the selection need to create a connection string . Then table procedures in that particular database and all.. I am able to select all table, procedures from a database. But am not able to select list different database.

Member 10455053 9-Dec-13 20:46pm

My requirement is to display all oracle databases in one system. User an select any of it. Then based on the selection need to create a connection string . Then table procedures in that particular database and all.. I am able to select all table procedures from a database. But am not able to select list different database.

bowlturner 10-Dec-13 9:06am

Why don't you just put the list of databases into an App.config file? Do you really need a dynamic list of actual databases? Most places have only a couple actual databases and many different schema's on each database.

Member 10455053 10-Dec-13 11:30am

yes dynamic list of databases.

bowlturner 10-Dec-13 11:54am

I guess I'm trying to understand why a server is going to have multiple oracle databases installed. Why does the list need to be dynamic? Really what is the purpose of all this since all the things I can think of this seems like a strange way to do anything.

Like how can you dynamically create a connection string to a database that you have 'found' on a server? Unless you have no real security, you'll have to at least get a username and password from the user. There are also many different tools already out there to manage dbs so I just come up with more and more questions of what the reason is for this 'need'.

Member 10455053 11-Dec-13 0:14am

So their is no way to get such a list of database names?

bowlturner 11-Dec-13 9:08am

I suspect there is, I don't know how, but I suspect you are barking up the completely wrong tree in your design. The way databases work it seems rather pointless to me to do what you are trying to do. If I knew more I might suggest a better design/approach.

KmgKrishnan · Answer 2 · 2013-12-09T13:06:00

Solution 2

Why shouldn't you use Tnsnames.ora file and read it to identify the list of instances inside it?
You can find it under oracle_home/network/admin or your team might be using it from the share folder.

Please refer the link below just an example for a single db connection using tns entries.

http://www.oracle.com/technetwork/articles/dotnet/cook-dotnet-101788.html[^]

Posted 9-Dec-13 13:06pm

KmgKrishnan

Comments

Member 10455053 9-Dec-13 20:53pm

Actually my requirement is to select all database in a server. Then user can select any of that. Based on that selection need to create a connection string and display all tables and procedures. I do not know how to select all database names in oracle server.

KmgKrishnan 10-Dec-13 2:07am

If i understand your requirement correctly, you wanted to have a feature how Toad/Sql developer lists the database. Once we select one and login, it connects to one database.

My idea is that you can check for the names within TNSNames.Ora file using Regex functions. I haven't tried in this case. I will check if i can bring some sample code.

In the mean time, please check the below links if it helps.

http://stackoverflow.com/questions/3004171/how-to-display-databases-in-oracle-11g-using-sqlplus

http://dba.stackexchange.com/questions/27725/how-to-see-list-of-databases-in-oracle