前段时间我们公司项目 要把sql server 转oracle
分享一下心得 也记录一下问题
开始我研究了一段时间 然后下载了
oracle 11g 版本 和 PL/SQL（客户端） 和sql server 不同的是 oracle 没有自己的客户端 需要用第三方的软件运行 PL/SQL 就是一个 sqldeveloper 也是一个，PL/SQL 我觉得比较稳定一点。但是2个都安装的话 刚好互补了
oracle 容易出现 无监听 什么 的错误 可以参考
http://jingyan.baidu.com/article/03b2f78c7a0ab75ea237ae33.html
然后再用 
创建表空间 和用户 这些网上都找得到的
好了 东西都安装好了 下面开始 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 <= )
{
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["长度"]) > ? : Convert.ToInt32(citem["长度"])) + (Convert.ToInt32(citem["小数位数"])>?(","+Convert.ToInt32(citem["小数位数"])):"") + ")";
}
else if (citem["类型"].ToString() == "nvarchar" || citem["类型"].ToString() == "float")
{
cs += " VARCHAR2(" + (Convert.ToInt32(citem["长度"]) == - ? : Convert.ToInt32(citem["长度"]) * ) + ")";
}
else if (citem["类型"].ToString() == "datetime")
{
cs += " DATE";
} cs += citem["主键"].ToString() == "" ? " primary key " : "";
if (citem["标识"].ToString() == "")
{
biaoshi = citem["字段名"].ToString();
}
cs += citem["默认值"].ToString() != "" ? " default " + citem["默认值"].ToString() + " " : "";
cs += citem["允许空"].ToString() == "" ? "," : " NOT NULL,";
}
cs = cs.Substring(, cs.Length - );
string tempsql = string.Format(addtablesql, tablename, cs);
GetSingle(tempsql);
if (biaoshi != string.Empty)
{
#region 判断是否有序列号 没有就创建 就是自动标识
int xuliehao = ;
try
{
xuliehao = Convert.ToInt32(GetSingle(string.Format(@"select Seq_{0}.nextval from sys.dual", tablename)));
}
catch { }
if (xuliehao <= )
{
#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+));//创建标识
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[];
string columnsNames = string.Empty;
string values = string.Empty;
for (int i = ; i < dt.Columns.Count; i++)
{
columnsNames += dt.Columns[i].ColumnName + ","; }
columnsNames = columnsNames.Substring(, columnsNames.Length - );
foreach (DataRow dr in dt.Rows)
{
values = string.Empty;
for (int i = ; 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(, values.Length - );
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[];
}
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[];
} 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
(11G中有个新特性，当表无数据时，不分配segment，以节省空间，这样就会没有导出数据 最蠢最实用的方法就是插入一条数据 然后删除掉 这样分配了segment 就可以导出了)
IMP sa/123 BUFFER=64000 FILE=C:\OracleBack\back_20170821.dmp FROMUSER=SA TOUSER=SA