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.

The DatabaseColumnDefinition provides the original database column metadata in JSON format. It reports the database column’s actual metadata, including the original precision, scale, and column definition, rather than the maximum possible values returned by the existing schema table properties.

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 87 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 column precision for interval data types and maximum precision for numeric data types. The data types with valid values are Oracle `NUMBER`, `INTERVAL` `YEAR` `TO` `MONTH`, and `INTERVAL` `DAY` `TO` `SECOND`. For all other data types, the value is `null`.
`NumericScale`	`System.Int16`	The scale of the column. The data types with valid values are Oracle `NUMBER`, `INTERVAL` `DAY` `TO` `SECOND`, and `TIMESTAMP`. For all other data types, 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. For DENSE vector columns, set to .NET numeric type array based on the numeric type of the vector data type. If the numeric format of the vector data type is '`*`', `DataType` will be `typeof(Array)`. For SPARSE vector columns, set to `typeof(string)`.
`ProviderType`	`Oracle.DataAccess.Client.OracleDbType` or `Oracle.ManagedDataAccess.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`.
<p id="GUID-F249DD7B-3E38-460A-A987-2ACCA268F0CB__I1006403">`IsByteSemantic` </p>	`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.
`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
`IsIdentity`	`System.Boolean`	`true` if the column is an identity column; otherwise `false`.
`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
`DomainSchema`	`System.String`	The database schema name that contains the domain. Requires Oracle AI Database 26ai or higher.
`DomainName`	`System.String`	The domain name Requires Oracle AI Database 26ai or higher.
`Annotations`	`System.String`	All column annotations returned as a JSON string Requires Oracle AI Database 26ai or higher.
`VectorDimensions`	`System.Int32`	The dimensions of the vector data type. Returns 0 if the dimension is '*', that is the Vector is of any dimension. Not Available in ODP.NET, Unmanaged Driver
`VectorType`	`OracleVectorType`	The vector data type. The possible values are `OracleVectorType.Dense` for dense vectors; `OracleVectorType.Sparse` for sparse vectors; and `null` for all other data types. Not Available in ODP.NET, Unmanaged Driver
`IsFloatingPointNumber`	`System.Boolean`	Specifies whether column values are floating-point numbers or not. `true` for floating-point number types, such as `NUMBER`, `NUMBER()`, `REAL`, `DOUBLE PRECISION`, `FLOAT[(p)]`, `BINARY_DOUBLE`, and `BINARY_FLOAT`. `false` for fixed-point number types, such as `NUMBER(p, [s])`, `NUMBER(, s)`, and `DECIMAL`, and for any other Oracle data types, such as `VARCHAR2` and `DATE`. Not Available in ODP.NET, Unmanaged Driver
`DatabaseColumnDefinition`	`System.String`	Original database column metadata as JSON. See table below for more details. Not Available in ODP.NET, Unmanaged Driver

Table 88 DatabaseColumnDefinition JSON

JSON Fields	Data Type	Required or Conditional	Applicable Database Data Types	Description
`columnName`	`string`	Required	All	Column name
`type`	`string`	Required	All	Column type string. It does not include details, such as size, precision, scale, or other qualifiers.
`columnDefinition`	`string`	Required	All types	Full column definition, including applicable size, precision, scale, character semantics, and vector attributes.
`precision`	`number`	Conditional	`NUMBER`, `FLOAT`,`INTERVAL DAY TO SECOND`, `INTERVAL YEAR TO MONTH`	Database column precision
`scale`	`number`	Conditional	`NUMBER`, `FLOAT`, `TIMESTAMP`, `INTERVAL DAY TO SECOND`	Database column scale
`size`	`number`	Conditional	`CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`, `RAW`	Database column size
`isByteSemantic`	`boolean`	Conditional	`CHAR`, `VARCHAR2`	Indicates `BYTE` or `CHAR` semantics
`vectorType`	`string`	Conditional	`VECTOR`	`VECTOR` storage type, such as `DENSE` or `SPARSE`
`vectorDimensions`	`number` or `string`	Conditional	`VECTOR`	Vector dimension count or flexible marker
`vectorDimensionFormat`	`string`	Conditional	`VECTOR`	Vector dimension format

Fields marked as conditional are included only when they apply to the database column type. Fields that do not apply are omitted from the JSON output.

DatabaseColumnDefinition JSON is not pretty-printed.

Example

This example creates and uses the SchemaTable from the reader.

/* Database Setup, if you have not done so yet.
connect scott/<PASSWORD>@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=<PASSWORD>;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();
  }
}

See Also: