媒介
由于前段时刻我们公司项目 要把sql server 转oracle，发明网上这方面的材料较少，所以在这里分享一下心得，也纪录一下题目，下面话不多说了，来一同看看细致的引见：
最先我研讨了一段时刻 然后下载了
oracle 11g 版本 和 PL/SQL（客户端） 和sql server 差别的是 oracle 没有本身的客户端 须要用第三方的软件运转 PL/SQL 就是一个 sqldeveloper 也是一个，PL/SQL 我以为比较稳定一点。然则2个都装置的话 恰好互补了
oracle 轻易涌现 无监听 什么 的毛病 能够参考
http://www.jb51.net/article/91184.htm
然后再用
建立表空间 和用户 这些网上都找获得的
好了 东西都装置好了 下面最先 sql server 转 oracle
起首是数据库的转换 我试了许多种体式格局，都是多若干少都是有点题目，由于是2个差别的数据库，末了我照样决议本身写个顺序 转换
代码贴出来
链接字符串
<add key="OracleConnectionString" value="Password=123;User ID=SA;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost )(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)))"/> <add key="SqlServerConnectionString" value="server=localhost;database=Table;uid=sa;pwd=123"/>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace TransplantSQL
{
public partial class Form1 : Form
{
public static string OracleConnectionString = System.Configuration.ConfigurationSettings.AppSettings["OracleConnectionString"];
public static string SqlServerConnectionString = System.Configuration.ConfigurationSettings.AppSettings["SqlServerConnectionString"];
public Form1()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection(OracleConnectionString);
try
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
label5.Text = "衔接胜利";
}
}
catch (OracleException se)
{
label5.Text = "衔接失利";
}
finally
{
con.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(SqlServerConnectionString);
try
{
con.Open();
if (con.State == System.Data.ConnectionState.Open)
{
label4.Text = "衔接胜利";
}
}
catch (SqlException se)
{
label4.Text = "衔接失利";
}
finally
{
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
DataTable tablenames = GetTableNames();
foreach (DataRow item in tablenames.Rows)
{
string tablename = item["Name"].ToString().ToUpper();
setdata(tablename);
}
}
else
{
setdata(textBox1.Text);
}
label2.Text = "胜利";
}
private static void setdata(string tablename)
{
// 查找有无此表 假如没有就加
int et = Convert.ToInt32(GetSingle("select count(*) from user_tables where table_name = '" + tablename + "'"));
if (et <= 0)
{
DataTable tableInfo = GetTableInfo(tablename);
string addtablesql = "CREATE TABLE {0}({1})";
string cs = string.Empty;
string biaoshi = string.Empty;
foreach (DataRow citem in tableInfo.Rows)
{
cs += citem["字段名"].ToString();
if (citem["范例"].ToString() == "int" || citem["范例"].ToString() == "bit" || citem["范例"].ToString() == "decimal")
{
cs += " NUMBER(" + (Convert.ToInt32(citem["长度"]) > 38 ? 38 : Convert.ToInt32(citem["长度"])) + (Convert.ToInt32(citem["小数位数"])>0?(","+Convert.ToInt32(citem["小数位数"])):"") + ")";
}
else if (citem["范例"].ToString() == "nvarchar" || citem["范例"].ToString() == "float")
{
cs += " VARCHAR2(" + (Convert.ToInt32(citem["长度"]) == -1 ? 4000 : Convert.ToInt32(citem["长度"]) * 2) + ")";
}
else if (citem["范例"].ToString() == "datetime")
{
cs += " DATE";
}
cs += citem["主键"].ToString() == "1" ? " primary key " : "";
if (citem["标识"].ToString() == "1")
{
biaoshi = citem["字段名"].ToString();
}
cs += citem["默认值"].ToString() != "" ? " default " + citem["默认值"].ToString() + " " : "";
cs += citem["许可空"].ToString() == "1" ? "," : " NOT NULL,";
}
cs = cs.Substring(0, cs.Length - 1);
string tempsql = string.Format(addtablesql, tablename, cs);
GetSingle(tempsql);
if (biaoshi != string.Empty)
{
#region 推断是不是有序列号 没有就建立 就是自动标识
int xuliehao = 0;
try
{
xuliehao = Convert.ToInt32(GetSingle(string.Format(@"select Seq_{0}.nextval from sys.dual", tablename)));
}
catch { }
if (xuliehao <= 0)
{
#region 为了让序列不反复 取最大值为min 值
int max = Convert.ToInt32(GetSingle(string.Format("select max({1}) from {0}", tablename, biaoshi),null));
#endregion
string sequence = string.Format(@"create sequence Seq_{0} start with {1} increment by 1 nomaxvalue minvalue 1 nocycle nocache", tablename, (max+1));//建立标识
GetSingle(sequence);
}
#endregion
#region 建立序列的触发器
string chufaqisql = string.Format(@"CREATE OR REPLACE TRIGGER T_{0}
BEFORE INSERT ON {0} FOR EACH ROW WHEN (new.{1} is null)
begin
select Seq_{0}.nextval into:new.{1} from dual;
end;", tablename, biaoshi);//建立触发器
GetSingle(chufaqisql);
#endregion
#region 建立唯一束缚
//string weiyisql = string.Format(@"create unique index U_{0} on {0} ({1})", tablename, biaoshi);
//GetSingle(weiyisql);
#endregion
}
//int count = Convert.ToInt32(GetSingle("SELECT count(1) FROM " + tablename));
//if (count < 10000)
//{
DataSet ds = Query("SELECT * FROM " + tablename);
DataTable dt = ds.Tables[0];
string columnsNames = string.Empty;
string values = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
columnsNames += dt.Columns[i].ColumnName + ",";
}
columnsNames = columnsNames.Substring(0, columnsNames.Length - 1);
foreach (DataRow dr in dt.Rows)
{
values = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dr[i] != DBNull.Value)
{
if (dr[i].ToString() != "")
{
if (dt.Columns[i].DataType == Type.GetType("System.Double")
|| dt.Columns[i].DataType == Type.GetType("System.Decimal")
|| dt.Columns[i].DataType == Type.GetType("System.Int32"))
{
values += dr[i] + ",";
}
else if (dt.Columns[i].DataType == Type.GetType("System.String"))
{
values += "'" + dr[i].ToString().Replace('\'', '‘') + "',";
}
else if (dt.Columns[i].DataType == Type.GetType("System.DateTime"))
{
values += "to_date('" + dr[i] + "','YYYY/MM/DD HH24:MI:SS'),";
}
else if (dt.Columns[i].DataType == Type.GetType("System.Boolean"))
{
if (dr[i].ToString() == "False")
{
values += "0,";
}
else { values += "1,"; }
}
}
else
{
values += "chr(32),";
}
}
else
{
values += "NULL,";
}
}
values = values.Substring(0, values.Length - 1);
string osql = "Insert into " + tablename + "(" + columnsNames + ") values(" + values + ")";
GetSingle(osql);
}
//}
}
}
private static DataTable GetTableNames()
{
string sql = string.Format(@"SELECT Name FROM SysObjects Where XType='U' ORDER BY Name");
DataSet ds = Query(sql);
return ds.Tables[0];
}
private static DataTable GetTableInfo(string tableName)
{
string sql = string.Format(@"SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '1' else '' end) 主键,b.name 范例,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '1'else '' end) 许可空,
REPLACE(REPLACE(isnull(e.text,''),'(',''),')','') 默认值,isnull(g.[value], ' ') AS [申明]
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
And d.name='{0}'
order by a.id,a.colorder", tableName);
DataSet ds = Query(sql);
return ds.Tables[0];
}
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(SqlServerConnectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(OracleConnectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, null);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (OracleException e)
{
throw e;
}
}
}
}
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (OracleParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(SqlServerConnectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}
}
这个代码顺应于我的数据库转换 人人须要的话 能够修正一下
个中oracle 没有自增进的 ，，而是序列 别的序列能够用触发器触发 麻烦了一点 整的来讲 还算好啦
然后就是把我本来的顺序 字符串链接改成oracle 的链接 上面贴出来了
起首System.Data.SqlClient;援用 改成System.Data.OracleClient;
然后 在看报错的处所统统改掉就好了 从Sql 改成 Oracle 就行
然后我贴出一些 不报错 然则sqlserver 和oracle 差别的处所
查询前若干条数据
select * from (SELECT * FROM Table) where rownum<100
其他花样转化成字符串花样
select to_char(其他花样,字符串花样) 如select to_char(sysdate,'yyyy-mm-dd') from dual
字符串截取
（截取的字符串，最先的位置，长度）
select substr('111222',3,2) from dual
字符串花样转化为时刻花样
select to_date('2017-08-03','yyyy-mm-dd') from dual
sql参数化占位符，不能用@用:
SELECT * FROM Table where ID=:ID
体系时刻
getdate()改成sysdate
猎取当前自动增进列ID
select Seq_Table.currval from dual
Seq_Table是自动增进列的名字，每一个表都不一样，所以须要找到当前
表设置的自动增进列对应的名字
都改完以后 等顺序不报错了 运转
会涌现32和64位的什么破题目 网上种种说法的都有 说项目属性中 改成 X86 X64
然则这些对我都不管用 而且我以为 这些东西改了以后 会对我原有的项目形成许多题目
所以我研讨了良久 终究得出几个效果
起首 假如是控制台 或窗体 顺序的话 直接把项目中 首选32位 勾上就好了
假如是 asp.net 页面顺序就没这么简朴了
我本机是64位体系 然后我装置oracle 和客户端都是64位 按原理不应该给我报什么64位错啊
然后网上说要装置32位Instant Client Setup
然后我就下了一个 装置了 顺序就能够了 ，
然则我在服务器上面 再次用一样的要领又不可了，找了许多材料 终究 从新装置了一下iis好了（实际上是要从新注册一遍framework）
装置32位的版本最好和oracle 版本一样
那时刻我下了许多个版本 调试 有的时刻 装置 Instant Client Setup的时刻会卡在一个点不动，能够多试频频 假如照样不可 在从新找一个吧！
装置完后 在控制面板内里能够找到的
别的贴出备份 复原的 敕令 用CMD翻开 运转 修正对应参数
Exp sa/123@ORCL file=C:\OracleBack\back_%date:~0,4%%date:~5,2%%date:~8,2%.dmp owner=sa IMP sa/123 BUFFER=64000 FILE=C:\OracleBack\back_20170821.dmp FROMUSER=SA TOUSER=SA
以上就是asp.net教程之sql server转换成oracle的要领详解的细致内容，更多请关注ki4网别的相干文章！
