[2005] getting the details of the table using Oracle

hi to all,
i want to get the details of the table (column names and datatype). if i am using the Getschema(), it took lots of time. any other way is possible to get the details of the table in Oracle. if, possible plz help me.

thank u

with thanks and regards
MMary

Hi
Read this http://www.oracle.com/technology/ora...04/081604.html

Just colums and datatype you can use the describe command of SQL (for Oracle).

desc tablename

use describe tablename

hi to all,
thank u for ur kind reply. i have used "desc tablename". but it given the error "ORA-00900 Invalid SQL statement". my code is


oledbcon = new OleDbConnection("Provider=OraOLEDB.Oracle;User Id=" + struname + ";Password=" + strpwd + "");
oledbcon.Open();
oledbcmd = new OleDbCommand("desc " + tablename, oledbcon);
oledbreader = oledbcmd.ExecuteReader();
while (oledbreader.Read())
{
cmbcolumns.Items.Add(oledbreader[0].ToString());
}
oledbreader.Close();
oledbcon.Close();


plz help me to solve the problem.

With thanks and regards
MMary

vb Code:

exec sp_tables

hi,
thank u for ur reply , exec sp_tables also giving the same error "Invalid SQL Statment". i gave the query like that "exec tablename". is it correct?
and also , ple somebody help me to solve the problem.

With thanks and regards
mmary

Dim con1 As New OracleClient.OracleConnection("Connection string")
con1.Open()
Dim dt1 As New DataTable
Dim da1 As New OracleClient.OracleDataAdapter("exec sp_tables", con1)
ret = da1.Fill(dt1)
If ret > 0 Then
For i As Integer = 0 To ret - 1
ComboBox1.Items.Add(dt1.Rows(i).Item("table_name"))
Next
End If

hi, thank u for ur kind reply.i need the table details(column name with data types". i think using ur code i can get the tables name. am i correct?. and also i am using the Oledb connection. i am not using the oracle connection.

plz help me to solve the problem


thank u

with thanks and regards
mmary.

Hv you tried this one

hi to all,
when i execute the "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =products", it also given the error "table or view does not exist". so,plz help me to solve th problem. i am using the oledb connection. in 2005 , there is no oracle connection. any other way is possible plz inform me. plz help me for that.

thank u.

with thanks and regards
mmary

That means that you dont have a table called Products

hi, thanks for ur reply. i am having the table like "Products". i was created one more table and i have used the same query it also, giving the same error. so. plz tell me how to rectify that?

thank u.

with thanks and regards
mmary

can you give the screen shot for this command from the query analyzer

vb Code:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

thank u for ur reply. it giving the error "ORA-00942: table or view does not exist".

thank u
with thanks and regards
mmary

Oh You are using Oracle :rolleyes:

i am using oracle 10g.

thank u
with thanks and regards
MMary

OK I got the time at last to run this down. What I did was use the User_Tab_Columns View to get the information. Here is what I did:

vb Code:

'"Select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE From User_tab_Columns Where Table_Name = 'CANDIDATE'"
        Dim oDS As System.Data.DataSet
        Dim strSQL As String = String.Empty
        strSQL = "Select COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE From User_tab_Columns Where Table_Name = 'CANDIDATE'"
        da = New clsDataAccess
        oDS = da.Return_Oracle_DataSet(strSQL)
        If Not oDS Is Nothing Then
            Me.DataGridView1.DataSource = oDS.Tables(0)
        End If
        If mdlGeneral.errMessage.intErrNum <> 0 Then
            MessageBox.Show(mdlGeneral.errMessage.strMess.Trim(), "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        da.Dispose()

Now da is a class that I use to do things against the DB. Return_Oracle_DataSet is a function in the class that returns an Oracle Data Set. I placed a datagrid on a page and set the datagrids datasource to the returned datasets table o (the only table in the resultset).

Now I did this as I said using the User_Tab_Columns View so that was a table owned by the user logged into the DB. (There own schema). If you want to use this to get someone elses schema then you use the DBA_Tab_Columns View and supply the table owner also.

RaviIntegra sp_tables is used in MS SQL Server only not in Oracle DBs.
danasegarne INFORMATION_SCHEMA is also used only in MS SQL Server.