GetSchemaTable

This method returns a DataTable that describes the column metadata of the OracleDataReader.

Declaration

// C#
public override DataTable GetSchemaTable();

Return Value

A DataTable that contains the metadata of the result set.

Implements

IDataReader

Exceptions

InvalidOperationException - The connection is closed or the reader is closed.

Remarks

The OracleDataReader.GetSchemaTable method returns the SchemaTable.

OracleDataReader SchemaTable

The OracleDataReader SchemaTable is a DataTable that describes the column metadata of the OracleDataReader.

The value of ColumnSize can show value up to 32K depending on the definition of VARCHAR2, NVARCHAR2, or RAW type columns in the table definition.

The columns of the SchemaTable are in the order shown.

Table 7-85 OracleDataReader SchemaTable

Name	Name Type	Description
`ColumnName`	`System.String`	The name of the column.
`ColumnOrdinal`	`System.Int32`	The `0`-based ordinal of the column.
`ColumnSize`	`System.Int64`	The maximum possible length of a value in the column. `ColumnSize` value is determined as follows`:` `CHAR` and `VARCHAR2` types: in bytes - if `IsByteSemantic` boolean value is `true` in characters - if `IsByteSemantic` boolean value is `false` All other types: in bytes
`NumericPrecision`	`System.Int16`	The maximum precision of the column, if the column is a numeric data type. This column has valid values for Oracle `NUMBER`, Oracle `INTERVAL` `YEAR` `TO` `MONTH`, and Oracle `INTERVAL` `DAY` `TO` `SECOND` columns. For all other columns, the value is `null`.
`NumericScale`	`System.Int16`	The scale of the column. This column has valid values for Oracle `NUMBER`, Oracle `INTERVAL` `DAY` `TO` `SECOND`, and the Oracle `TIMESTAMP` columns. For all other columns, the value is `null`.
`IsUnique`	`System.Boolean`	Indicates whether or not the column is unique. `true` if no two rows in the base table can have the same value in this column, where the base table is the table returned in `BaseTableName`. `IsUnique` is guaranteed to be `true` if one of the following applies in descending order of priority: the column constitutes a base table primary key by itself and a `NOT NULL` constraint has been defined on the column there is a unique constraint or a unique index that applies only to this column and a `NOT` `NULL` constraint has been defined on the column the column is an explicitly selected `ROWID` `IsUnique` is false if the column can contain duplicate values in the base table. The default is `false`. The value of this property is the same for each occurrence of the base table column in the select list.
`IsKey`	`System.Boolean`	Indicates whether or not the column is a key column. `true` if the column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with `IsKey` set to `true` must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns can be generated from one of the following in descending order of priority: A base table primary key with the following condition: A `NOT NULL` constraint must be defined on the column or on all of the columns, in the case of a composite primary key. Any of the unique constraints or unique indexes with the following condition: A `NOT` `NULL` constraint must be defined on the column or on all of the columns, in the case of a composite unique constraint or composite unique index. A base table composite primary key with the following condition: A `NULL` constraint must be defined on at least one, but not all, of the columns. Any of the composite unique constraints or composite unique indexes with the following condition: A `NULL` constraint must be defined on at least one, but not all, of the columns. An explicitly selected `ROWID`. `false` if the column is not required to uniquely identify the row. The value of this property is the same for each occurrence of the base table column in the select list.
`IsRowID`	`System.Boolean`	`true` if the column is a `ROWID`, otherwise `false`.
`BaseColumnName`	`System.String`	The name of the column in the database if an alias is used for the column.
`BaseSchemaName`	`System.String`	The name of the schema in the database that contains the column.
`BaseTableName`	`System.String`	The name of the table or view in the database that contains the column.
`DataType`	`System.RuntimeType`	Maps to the common language runtime type.
`ProviderType`	`Oracle.DataAccess. Client.OracleDbType`	The database column type (`OracleDbType`) of the column.
`AllowDBNull`	`System.Boolean`	`true` if `null` values are allowed, otherwise `false`.
`IsAliased`	`System.Boolean`	`true` if the column is an alias; otherwise `false`.
`IsByteSemantic`	`System.Boolean`	`IsByteSemantic` is: `true` if the `ColumnSize` value uses bytes semantics `false` if `ColumnSize` uses character semantics This value is always `true` when connected to a database version earlier than Oracle9i.
`IsExpression`	`System.Boolean`	`true` if the column is an expression; otherwise `false`.
`IsHidden`	`System.Boolean`	`true` if the column is hidden; otherwise `false`.
`IsReadOnly`	`System.Boolean`	`true` if the column is read-only; otherwise `false`.
`IsLong`	`System.Boolean`	`true` if the column is a `LONG`, `LONG` `RAW`, `BLOB`, `CLOB`, or `BFILE`; otherwise `false`.
`UdtTypeName`	`System.String`	The type name of the UDT.
`IsIdentity`	`System.Boolean`	`true` if the column is an identity column; otherwise `false`.
`IsAutoIncrement`	`System.Boolean`	`true` if the column assigns values to new rows in fixed increments; otherwise `false`. Not Available in ODP.NET, Managed Driver and ODP.NET Core
`IdentityType`	`OracleIdentityType`	An `OracleIdentityType` enumeration value that specifies how the identity column values are generated; otherwise `DbNull.Value`, if the column is not an identity column. Not Available in ODP.NET, Managed Driver and ODP.NET Core

Example

This example creates and uses the SchemaTable from the reader.

/* Database Setup, if you have not done so yet.
connect scott/tiger@oracle 
CREATE TABLE empInfo (
empno NUMBER(4) PRIMARY KEY,
empName VARCHAR2(20) NOT NULL,
hiredate DATE,
salary NUMBER(7,2),
jobDescription Clob,
byteCodes BLOB
);
 
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(1,'KING','SOFTWARE ENGR', '5657');
Insert into empInfo(EMPNO,EMPNAME,JOBDESCRIPTION,byteCodes) values 
(2,'SCOTT','MANAGER', '5960');
commit;
 
*/
// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types;
 
class GetSchemaTableSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    string cmdstr = "SELECT EMPNO,EMPNAME FROM EMPINFO where EMPNO = 1";
    OracleCommand cmd = new OracleCommand(cmdstr, con);
 
    //get the reader
    OracleDataReader reader = cmd.ExecuteReader();
 
    //get the schema table
    DataTable schemaTable = reader.GetSchemaTable();
 
    //retrieve the first column info.
    DataRow row = schemaTable.Rows[0];
 
    //print out the column info
    Console.WriteLine("Column name: " + row["COLUMNNAME"]);
    Console.WriteLine("Precision: " + row["NUMERICPRECISION"]);
    Console.WriteLine("Scale: " + row["NUMERICSCALE"]);
    reader.Close();
 
    // Close the connection
    con.Close();
  }
}