[2005] getting the details of the table using Oracle

[mmary]

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

[Asgorath]

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

[GaryMazzone]

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

desc tablename

[onlyGirl]

use describe tablename

[mmary]

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

[RaviIntegra]

exec sp_tables

[mmary]

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

[RaviIntegra]

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

[mmary]

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.

[danasegarane]

Hv you tried this one

[mmary]

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

[danasegarane]

That means that you dont have a table called Products

[mmary]

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

[danasegarane]

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

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

[mmary]

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

thank u
with thanks and regards
mmary

[danasegarane]

Oh You are using Oracle

[mmary]

i am using oracle 10g.

thank u
with thanks and regards
MMary

[GaryMazzone]

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:

'"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.