![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
class
BlobDemo
{
private static readonly string ConnectionString =
" Data Source=Tcco;User ID=scott;Password=tiger; " ;
public void AddWithProcedure( string file)
{
if (File.Exists(file) == false )
{
return ;
}
string strSql = " AddPhoto " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new OracleParameter( " vPhoto " , OracleType.Blob));
cmd.Parameters[ 0 ].Value = File.ReadAllBytes(file); // A
cmd.ExecuteNonQuery();
}
}
// 方法1
public void AddWithSqlText( string file)
{
if (File.Exists(file) == false )
{
return ;
}
// string strSql = "begin insert into blobdemo(Photo) values(:Photo);end;"; // B
string strSql = " insert into blobdemo(Pno,Photo) values(pno.nextval,:Photo) " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.Parameters.Add( new OracleParameter( " :Photo " , OracleType.Blob));
cmd.Parameters[ 0 ].Value = File.ReadAllBytes(file);
cmd.ExecuteNonQuery();
}
}
// 方法2
public void AddWithProcedure2( string file)
{
if (File.Exists(file) == false )
{
return ;
}
string strSql = " AddPhoto " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new OracleParameter( " vPhoto " , OracleType.Blob));
byte [] arr = null ;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
{
arr = new byte [fs.Length];
fs.Read(arr, 0 , arr.Length);
}
// 生成OracleLob类型
OracleCommand cmd2 = conn.CreateCommand();
OracleTransaction tx;
tx = conn.BeginTransaction();
cmd2.Transaction = tx;
cmd2.CommandText = " declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end; " ;
cmd2.Parameters.Add( new OracleParameter( " tempblob " , OracleType.Blob));
cmd2.Parameters[ " tempblob " ].Direction = ParameterDirection.Output;
cmd2.ExecuteNonQuery();
OracleLob tempLob;
tempLob = (OracleLob)cmd2.Parameters[ " tempblob " ].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(arr, 0 , arr.Length);
tempLob.EndBatch();
// tx.Commit();
cmd.Parameters[ 0 ].Value = tempLob;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
}
}
}
{
private static readonly string ConnectionString =
" Data Source=Tcco;User ID=scott;Password=tiger; " ;
public void AddWithProcedure( string file)
{
if (File.Exists(file) == false )
{
return ;
}
string strSql = " AddPhoto " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new OracleParameter( " vPhoto " , OracleType.Blob));
cmd.Parameters[ 0 ].Value = File.ReadAllBytes(file); // A
cmd.ExecuteNonQuery();
}
}
// 方法1
public void AddWithSqlText( string file)
{
if (File.Exists(file) == false )
{
return ;
}
// string strSql = "begin insert into blobdemo(Photo) values(:Photo);end;"; // B
string strSql = " insert into blobdemo(Pno,Photo) values(pno.nextval,:Photo) " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.Parameters.Add( new OracleParameter( " :Photo " , OracleType.Blob));
cmd.Parameters[ 0 ].Value = File.ReadAllBytes(file);
cmd.ExecuteNonQuery();
}
}
// 方法2
public void AddWithProcedure2( string file)
{
if (File.Exists(file) == false )
{
return ;
}
string strSql = " AddPhoto " ;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(strSql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( new OracleParameter( " vPhoto " , OracleType.Blob));
byte [] arr = null ;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
{
arr = new byte [fs.Length];
fs.Read(arr, 0 , arr.Length);
}
// 生成OracleLob类型
OracleCommand cmd2 = conn.CreateCommand();
OracleTransaction tx;
tx = conn.BeginTransaction();
cmd2.Transaction = tx;
cmd2.CommandText = " declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end; " ;
cmd2.Parameters.Add( new OracleParameter( " tempblob " , OracleType.Blob));
cmd2.Parameters[ " tempblob " ].Direction = ParameterDirection.Output;
cmd2.ExecuteNonQuery();
OracleLob tempLob;
tempLob = (OracleLob)cmd2.Parameters[ " tempblob " ].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(arr, 0 , arr.Length);
tempLob.EndBatch();
// tx.Commit();
cmd.Parameters[ 0 ].Value = tempLob;
cmd.Transaction = tx;
cmd.ExecuteNonQuery();
}
}
}
在调用存储过程(脚本如下)时出现上述错误AddPhoto,原因:
A处语句将byte[]数组赋给参数,在存储过程执行时,应该有一个转换(猜测):暂且称为byte[]到blob的转换,
而这个转换应该是plsql完成的,如果数组长度大于32767(外来,未证实)的话,就会报上述错误。
那么怎样避免这个错误呢?
方法1:不用存储过程,用sql语句
注:但不能有多个语句,如B处,否则还是报上述错误。
方法2:还是用存储过程,但在赋值前将byte[]转换成OracleLob类型
附sql语句:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--
创建表
create table BLOBDEMO
(
PHOTO BLOB,
PNO NUMBER ( 18 )
)
-- 创建序列
create sequence PNO
minvalue 1
maxvalue 9999999999999999999999999
start with 27
increment by 1
nocache;
-- 创建存储过程
create or replace procedure AddPhoto
(vPhoto in blob)
is
l_pno number ( 18 );
begin
select pno.nextval into l_pno from dual;
insert into BlobDemo(pno,Photo) values (l_pno,vPhoto);
end ;
create table BLOBDEMO
(
PHOTO BLOB,
PNO NUMBER ( 18 )
)
-- 创建序列
create sequence PNO
minvalue 1
maxvalue 9999999999999999999999999
start with 27
increment by 1
nocache;
-- 创建存储过程
create or replace procedure AddPhoto
(vPhoto in blob)
is
l_pno number ( 18 );
begin
select pno.nextval into l_pno from dual;
insert into BlobDemo(pno,Photo) values (l_pno,vPhoto);
end ;