How to find Oracle sequence column?

[Damjan Kovac]

Hi!

I'm working with Oracle 8.x database with MS ADO.NET Provider for Oracle
(System.Data.OracleClient). I'm wondering how to check if there is any
sequence column in Oracle data table. Is there any way to do it with table
metadata?
It's simple to find out which column is identity in MS SQL server through
IDataReader.GetSchemaTable():
...
string sql = "SELECT * FROM MyTable WHERE 1=2";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.CommandText = CommandType.Text;
cmd.Connection = new SqlConnection("....");
cmd.Conneciton.Open();
IDataReader dr = cmd.ExecuteReader();
DataTable metaData = dr.GetSchemaTable();
string identityColName = null;
for (int i=0; i<metaData.Rows.Count; i++)
if ((bool)metaData.Rows["IsIdentity"] &&
(bool)metaData.Rows["IsAutoIncrement"]) {
identityColName = metaData.Rows["ColumnName"].ToString();
break;
}
dr.Close();
if (identityColName!=null) { ... }
....

Any similar solution for Oracle 8.x ?


Thank you for your help,
Damjan

 

[Paul Clement]

¤ Hi!
¤
¤ I'm working with Oracle 8.x database with MS ADO.NET Provider for Oracle
¤ (System.Data.OracleClient). I'm wondering how to check if there is any
¤ sequence column in Oracle data table. Is there any way to do it with table
¤ metadata?
¤ It's simple to find out which column is identity in MS SQL server through
¤ IDataReader.GetSchemaTable():
¤ ..
¤ string sql = "SELECT * FROM MyTable WHERE 1=2";
¤ SqlCommand cmd = new SqlCommand();
¤ cmd.CommandText = sql;
¤ cmd.CommandText = CommandType.Text;
¤ cmd.Connection = new SqlConnection("....");
¤ cmd.Conneciton.Open();
¤ IDataReader dr = cmd.ExecuteReader();
¤ DataTable metaData = dr.GetSchemaTable();
¤ string identityColName = null;
¤ for (int i=0; i<metaData.Rows.Count; i++)
¤ if ((bool)metaData.Rows["IsIdentity"] &&
¤ (bool)metaData.Rows["IsAutoIncrement"]) {
¤ identityColName = metaData.Rows["ColumnName"].ToString();
¤ break;
¤ }
¤ dr.Close();
¤ if (identityColName!=null) { ... }
¤ ...
¤
¤ Any similar solution for Oracle 8.x ?

Not that I am aware of. There is no attribute that identifies an Oracle column in a table as auto
increment (sequence number generated). The sequence number generation is independent of the column
definition.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)