ADO.NET Data Providers
http://msdn.microsoft.com/en-us/data/dd363565
1、MYSQL http://www.mysql.com/products/connector/
2、ORACLE http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
3、Oracle and Visual Studio 2012 Quickstart ：http://www.oracle.com/technetwork/topics/dotnet/whatsnew/vs2012welcome-1835382.html
===========================================================
I installed the required data provider for oracle and I was able to connect from server explorer but from light switch it give this error:
error 2019: Member Mapping specified is not valid. The type 'Edm.Boolean[Nullable=False,DefaultValue=]' of member 'IsNullable' in type 'Store.Column' is not compatible with 'OracleEFProvider.number[Nullable=False,DefaultValue=,Precision=1,Scale=0]' of member 'IsNullable' in type 'Oracle.TableColumn'.
I found this solution as well but it didn't work for me ??!
The error message means, that the used Oracle Provider cannot map the column in the Oracle table of type Number to the defined type boolean. I resolved the issue by inserting a custom mapping into the web.config file of the ServerGenerated project of my LightSwitch Solution.
1. Switch to the File view of the Lightswitch Solution (through the Project Explorer view) 2. Add the following mapping to the web.config file: <?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> </connectionStrings> <oracle.dataaccess.client> <settings> <add name="bool" value="edmmapping number(1,0)" /> <add name="byte" value="edmmapping number(3,0)" /> <add name="int16" value="edmmapping number(4,0)" /> <add name="int32" value="edmmapping number(9,0)" /> <add name="int64" value="edmmapping number(18,0)" /> </settings> </oracle.dataaccess.client> </configuration>
==============
Datatypes translation between Oracle and SQL Server part 1: character, binary strings
Datatypes translation is one of the most important things you need to consider when migrate your application from one database to the other. This is one of the series of articles that we talking about translate SQL query among different databases.
This article will focused on conversion of those datatypes: character, binary strings between Oracle and SQL Server. We will talk about conversion of other datatypes such as nunber, float, date and etc in other articles later.
When you convert character datatypes from Oracle to SQL Server or vice verse, you not only need to find corresponding datatype name but also need to find out how string was stored in database. Is this string stored in character or byte? and you must be aware of the maximum length of datatype in source and target databases.
In SQL Server, char [ ( n ) ] is fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. You can easily find corresponding datatype name “char” in Oracle, but char in oracle with a maximum length of 2000 bytes. So you can’t migrate char(2048) in your SQL Server script to Oracle without any changes, you should use clob instead if n > 2000.
In Oracle database, char[(size)] can be also be used in fixed-length character data of length size in characters. When you use char qualifier, for example char(10 char), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. When translate this datatype to SQL Server, target datatype can be char(10) or char(40) depends on the database character set in source database(Oracle).
Detailed information about Oracle datatypes and SQL Server datatypes: including datatype name, description and what’s the corresponding datatype in other databases.
Below are summary tables show how Character and binary string datatypes translated from Oracle to SQL Server and vice verse.
| Oracle(source) | SQL Server(target) |
| CHAR [(size [BYTE | CHAR])] | char[(size)] |
| VARCHAR2(size [BYTE | CHAR]) | varchar(size) |
| NCHAR[(size)] | nchar[(size)] |
| NVARCHAR2(size) | nvarchar(size) |
| long | varchar(max) |
| long raw | varbinary(max) |
| raw(size) | varbinary(size) |
| blob | varbinary(max) |
| clob | varchar(max) |
| nclob | ntext |
| bfile | N/A |
How Character and binary string datatypes translated from SQL Server to Oracle.
| SQL Server(source) | Oracle(target) |
| char [ ( n ) ] | char[(n)], 1<=n<=2000; clob, n>2000 |
| varchar [ ( n | max ) ] | varchar2(n), 1<=n<=4000; clob (n>4000) |
| text | clob |
| nchar [ ( n ) ] | nchar[(n)], 1<=n<=2000; nclob(n>2000) |
| nvarchar [ ( n | max ) ] | nvarchar2[(n)], 1<=n<=4000; nclob( n>4000 ) |
| ntext | nclob |
| binary [ ( n ) ] | raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000) |
| SQL Server(source) | Oracle(target) |
| char [ ( n ) ] | char[(n)], 1<=n<=2000; clob, n>2000 |
| varchar [ ( n | max ) ] | varchar2(n), 1<=n<=4000; clob (n>4000) |
| text | clob |
| nchar [ ( n ) ] | nchar[(n)], 1<=n<=2000; nclob(n>2000) |
| nvarchar [ ( n | max ) ] | nvarchar2[(n)], 1<=n<=4000; nclob( n>4000 ) |
| ntext | nclob |
| binary [ ( n ) ] | raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000) |
| varbinary [ ( n | max) ] | raw(1)(n was omitted); raw(n), 1<=n<=2000; blob(n>2000) |
| image |
blob |
----------------------
Datatype conversion is one of the key issues when convert one dialect of SQL to another. This article documents Oracle datatypes and how to convert it to corresponding datatype of other databases.
Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes.
A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a “collection”) contains a set of values. A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data.
We focused on bulit-in datatypes and ansi supported datatypes in this article. user defined datatypes and Oracle supplied types(Any Types, XML Types, Spatial Types and Media Types) were not discussed here.
bulit-in datatypes: Character
- CHAR [(size [BYTE | CHAR])]
- Description: Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
- ISO synonym: character, char
- SQL Server: char[(size)], 1 <= size <= 8,000 bytes
- VARCHAR2(size [BYTE | CHAR])
- Description: Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters,and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.
- ISO synonym: character varying, char varying
- SQL Server: varchar(size), 1 <= size <= 8,000 bytes
- NCHAR[(size)]
- Description: Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.
- ISO synonym: national character, national char, nchar
- SQL Server: nchar[(size)]
- NVARCHAR2(size)
- Description: Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.
- ISO synonym: national character varying, national char varying, nchar varying
- SQL Server: nvarchar(size)
bulit-in datatypes: long and raw
- long
- Description: Character data of variable length up to 2 gigabytes, or 2^31 -1 bytes. Provided for backward compatibility.
- ISO synonym: N/A
- SQL Server: varchar(max)
- long raw
- Description: Raw binary data of variable length up to 2 gigabytes.
- ISO synonym: N/A
- SQL Server: image/varbinary(max)
- raw(size)
- Description: Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.
- ISO synonym: N/A
- SQL Server: varbinary(size)
bulit-in datatypes: large object
- clob
- Description: A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes – 1) * (database block size).
- ISO synonym: N/A
- SQL Server: varchar(max)
- nclob
- Description: A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes – 1) * (database block size). Stores national character set data.
- ISO synonym: N/A
- SQL Server: ntext
- blob
- Description: A binary large object. Maximum size is (4 gigabytes – 1) * (database block size).
- ISO synonym: N/A
- SQL Server: varbinary(max)/image
- bfile
- Description: Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.
- ISO synonym: N/A
- SQL Server: VARBINARY(MAX)
bulit-in datatypes: Number
- NUMBER [ (p [, s]) ]
- Description: Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. In a NUMBER column, floating point numbers have decimal precision. In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision.
- ISO synonym: numeric[(p [, s])]
- SQL Server:number to float; number(p [,s]) to numeric(p [,s]), if s > p, then set p = s.
- FLOAT [(p)]
- Description:A subtype of the NUMBER datatype having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 in binary digits. A FLOAT value requires from 1 to 22 bytes.
- ISO synonym: float
- SQL Server: float[(p)], if p>53, then convert to float.
- BINARY_FLOAT
- Description:32-bit floating point number. This datatype requires 5 bytes, including the length byte.
- ISO synonym: real
- SQL Server: float(24)
- BINARY_DOUBLE
- Description:64-bit floating point number. This datatype requires 9 bytes, including the length byte.
- ISO synonym: double precision
- SQL Server: float(53)
bulit-in datatypes: Datetime and Interval Datatypes
- date
- Description:stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
- ISO synonym: N/A
- SQL Server: DATETIME
- timestamp[(fractional_seconds_precision)], 0<= fractional_seconds_precision <=9
- Description:The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values.
- ISO synonym: N/A
- SQL Server: DATETIME
- timestamp[(fractional_seconds_precision)] with time zone, 0<= fractional_seconds_precision <=9
- Description:a variant of TIMESTAMP that includes a time zone region name or a a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for collecting and evaluating date information across geographic regions.
- ISO synonym: N/A
- SQL Server:VARCHAR(37), fetched from this article
- timestamp[(fractional_seconds_precision)] with local time zone, 0<= fractional_seconds_precision <=9
- Description:is another variant of TIMESTAMP that includes a time zone offset in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user’s local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.
- ISO synonym: N/A
- SQL Server:VARCHAR(37)
- INTERVAL YEAR [(year_precision)] TO MONTH, 0<= year_precision <=9, default is 2.
- Description:stores a period of time using the YEAR and MONTH datetime fields. This datatype is useful for representing the difference between two datetime values when only the year and month values are significant.
- ISO synonym:N/A
- SQL Server:N/A
- INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)] , 0<= day_precision <=9, default is 2. 0<= fractional_seconds_precision <=9,default is 6.
- Description:stores a period of time in terms of days, hours, minutes, and seconds. This datatype is useful for representing the precise difference between two datetime values.
- ISO synonym:N/A
- SQL Server:N/A
ANSI SQL datatypes
SQL statements that create tables and clusters can also use ANSI SQL datatypes. Oracle recognizes the ANSI SQL datatype name that differs from the Oracle Database datatype name. It converts the datatype to the equivalent Oracle datatype, records the Oracle datatype as the name of the column datatype, and stores the column data in the Oracle datatype based on the conversions shown in the table that follow.
| ANSI SQL Datetype | Oracle data type |
| character(n) | char(n) |
| char(n) | char(n) |
| character varying(n) | varchar2(n) |
| char varying(n) | varchar2(n) |
| national character(n) | nchar(n) |
| national char(n) | nchar(n) |
| nchar(n) | nchar(n) |
| national character varying(n) | nvarchar2(n) |
| national char varying(n) | nvarchar2(n) |
| nchar varying(n) | nvarchar2(n) |
| numberic[(p,s)] | number(p,s) |
| decimal[(p,s)] | number(p,s) |
| integer | number(38) |
| int | number(38) |
| smallint | number(38) |
| float | float(126) |
| double precision | float(126) |
| real | float(63) |