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 6-81 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();
  }
}