A Data Type Conversion

This appendix contains the following tables used to determine the correct parameter type mappings.

Mapping of Oracle Native Data Type to .NET Framework Data Types

Table A-1 lists the supported mapping of Oracle native data types to the .NET Framework Types.

Table A-1 Mapping of Oracle Native Data Type to .NET Framework Data Types

Oracle Native Data Type .NET Framework Data Types

BFILE *

Byte[]

BINARY_DOUBLE

Byte, Byte[], Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

BINARY_FLOAT

Byte, Byte[], Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

BINARY_INTEGER

Byte, Byte[], Char, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

BLOB

Byte[]

BOOLEAN

Not Supported

CHAR

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

CLOB

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

DATE

Byte[], Char[], String, DateTime

FLOAT

Byte, Byte[], Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

INTERVAL DAY TO SECOND

Byte[], Char, Char[], String, TimeSpan

INTERVAL YEAR TO MONTH

Byte, Byte[], Char, Char[], int, Int16, Int32, Int64, long, short, String

LONG

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

LONG RAW

Byte[]

NCHAR

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

NCLOB

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

NUMBER

Byte, Byte[], Char, DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

NVARCHAR2

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

PLS_INTEGER

Byte, Byte[], Char, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

RAW

Byte[]

REAL

Byte, Byte[], Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

ROWID

Char[], String

TIMESTAMP

Byte[], Char[], String, DateTime

TIMESTAMP WITH LOCAL TIME ZONE

Byte[], Char[], String, DateTime

TIMESTAMP WITH TIME ZONE

Byte[], Char[], String, DateTime

UROWID

Char[], String

VARCHAR2

Byte, Char, Char[], DateTime, Decimal, Double, float, int, Int16, Int32, Int64, long, SByte, short, Single, String, uint, Uint16, Uint32, Uint64, ulong, ushort

XMLType

Char[], String


Mapping of .NET Framework Data Types to Oracle Native Data Types

Table A-2 lists the supported mapping of .NET Framework Types to Oracle native data types.

Table A-2 Mapping of .NET Framework Data Types to Oracle Native Data Types

.NET Framework Data Types Oracle Native Data Type

Byte

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Byte[]

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, BLOB, DATE, FLOAT, INTERVAL YEAR TO MONTH, LONG RAW, NUMBER, PLS_INTEGER, RAW, REAL, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE

Char

BINARY_INTEGER, CHAR, CLOB, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, VARCHAR2, XMLType

Char[]

CHAR, CLOB, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NVARCHAR2, ROWID, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, UROWID, VARCHAR2, XMLType

DateTime

CHAR, CLOB, DATE, LONG, NCHAR, NCLOB, NVARCHAR2, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, VARCHAR2

Decimal

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR,CLOB, FLOAT, LONG, NCHAR, NCLOB, NVARCHAR2, NUMBER, PLS_INTEGER, REAL, VARCHAR2

Double

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

float

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

int

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Int16

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Int32

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Int64

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

long

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

SByte

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

short

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Single

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

String

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, DATE, FLOAT, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, ROWID, STRING, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, UROWID, VARCHAR2, XMLTYPE

TimeSpan

INTERVAL DAY TO SECOND

uint

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Uint16

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Uint32

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

Uint64

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

ulong

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2

ushort

BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, CHAR, CLOB, FLOAT, LONG, NCHAR, NCLOB, NUMBER, NVARCHAR2, PLS_INTEGER, REAL, VARCHAR2


Mapping of Oracle Native Data Type to ODP.NET Data Types

Table A-3 lists the supported mapping of Oracle native data types to the ODP.NET Types.

Table A-3 Mapping of Oracle Native Data Type to ODP.NET Data Types

Oracle Native Data Type ODP.NET Type

BFILE *

OracleBFile

BINARY_DOUBLE

OracleDecimal

OracleString

BINARY_FLOAT

OracleDecimal

OracleString

BINARY_INTEGER

OracleDecimal

OracleString

BLOB

OracleBinary

OracleBlob

BOOLEAN

Not Supported

CHAR

OracleString

CLOB

OracleClob

OracleString

DATE

OracleDate

OracleString

OracleTimeStamp

OracleTimeStampTZ

OracleTimeStampLTZ

FLOAT

OracleDecimal

OracleString

INTERVAL DAY TO SECOND

OracleString

OracleIntervalDS

INTERVAL YEAR TO MONTH

OracleIntervalYM

OracleString

LONG

OracleString

LONG RAW

OracleBinary

NCHAR

OracleString

NCLOB

OracleClob

OracleString

NUMBER

OracleDecimal

OracleString

NVARCHAR2

OracleString

PLS_INTEGER

OracleDecimal

OracleString

RAW

OracleBinary

REAL

OracleDecimal

OracleString

ROWID

OracleString

TIMESTAMP

OracleDate

OracleString

OracleTimeStamp

OracleTimeStampTZ

OracleTimeStampLTZ

TIMESTAMP WITH LOCAL TIME ZONE

OracleDate

OracleString

OracleTimeStamp

OracleTimeStampTZ

OracleTimeStampLTZ

TIMESTAMP WITH TIME ZONE

OracleDate

OracleString

OracleTimeStamp

OracleTimeStampTZ

OracleTimeStampLTZ

UROWID

OracleString

VARCHAR2

OracleString

XMLType

OracleClob

OracleString

OracleXmlType


Mapping of ODP.NET Data Types to Oracle Native Data Types

Table A-4 lists the supported mapping of the ODP.NET Types to the Oracle native data types.

Table A-4 Mapping of ODP.NET Data Types to Oracle Native Data Types

ODP.NET Type Oracle Native Data Type

OracleBFile

BFILE *

OracleBinary

BLOB

LONG RAW

RAW

OracleBlob

BLOB

OracleClob

CLOB

NCLOB

XMLType

OracleDate

DATE

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

OracleDecimal

BINARY_DOUBLE

BINARY_INTEGER

BINARY_FLOAT

FLOAT

NUMBER

PLS_INTEGER

REAL

OracleIntervalDS

INTERVAL DAY TO SECOND

OracleIntervalYM

INTERVAL YEAR TO MONTH

OracleString

BINARY_DOUBLE

BINARY_FLOAT

BINARY_INTEGER

CHAR

CLOB

DATE

FLOAT

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

LONG

NCHAR

NCLOB

NVARCHAR2

NUMBER

PLS_INTEGER

REAL

ROWID

STRING

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

UROWID

VARCHAR2

XMLType

OracleTimeStamp

DATE

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

OracleTimeStampTZ

DATE

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

OracleTimeStampLTZ

DATE

TIMESTAMP

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH TIME ZONE

OracleXmlType

XMLType


* BFILE Mapping to .NET Framework

An Oracle native BFILE type parameter can be converted to a .NET Byte[]. However, converting a Byte[] to an Oracle native BFILE type is not supported. This means BFILE to .NET Byte[] conversion can be done only if the BFILE parameter type on the database side is an IN parameter and the corresponding parameter on the .NET stored procedure is an IN parameter of type Byte[]. For a BFILE INOUT, or OUT parameter or a RETURN VALUE, the corresponding .NET stored procedure parameters must be of type Oracle.DataAccess.Types.OracleBFile. Otherwise, an exception is thrown.

PL\SQL Associative Array

PL\SQL Associative array is not supported.