This content has been marked as final. Show 6 replies
The issue is that you can not direct insert more than 4K (as you have discovered). Two methods I use in this situation are:
1) sql statement or procedure that returns a lob locator into an ODP.NET OracleBlob parameter such as:
insert into the_table (primary_key, blob_column) values (pk_value, empty_blob()) returning blob_column into :blob_parameter.
Once you have the OracleBlob you should be able to write/update the data.
2) use an anonymous pl/sql block or package that uses the dbms_lob supplied package to work with the LOB.
Here is a simple console example...
SQL> create table blob_test
3 blob_id number,
4 blob_data blob,
5 constraint blob_test_pk primary key (blob_id)
7 tablespace users;
Dim strFileName As String = "c:\temp\testimage.jpg"
Dim fsBlob As FileStream = New FileStream(strFileName, FileMode.Open)
Dim bytes(fsBlob.Length) As Byte
fsBlob.Read(bytes, 0, fsBlob.Length)
Dim con As OracleConnection = New OracleConnection("User Id=/; Data Source=lt8i")
Dim trans As OracleTransaction = con.BeginTransaction()
Dim sql As String = "insert into blob_test (blob_id, blob_data) values (1,empty_blob()) returning blob_data into :blob_parameter"
Dim blob_parameter As OracleParameter = New OracleParameter
blob_parameter.OracleDbType = OracleDbType.Blob
blob_parameter.Direction = ParameterDirection.InputOutput
Dim cmd As OracleCommand = New OracleCommand(sql, con)
Dim blob As OracleBlob = blob_parameter.Value
blob.Write(bytes, 0, bytes.Length)
sql = "update blob_test set blob_data = :blob_parameter where blob_id = 1"
cmd.CommandText = sql
blob_parameter.Value = blob
This sample was tossed together pretty quickly, so it is not a complete, bullet-proof sample, but hopefully it will give you what you need to get started. The 'testimage.jpg' file is just over 5MB in size.
Hope that helps,
Mark A. Williams
Author, Professional .NET Oracle Programming
I am having the same problem during executing an update which tries to update the contents of six Varchar2(2000) fields, I am using the oracle driver that shipped with .NET and C#.
What's your serverside charset? Something like UTF8, AL32UTF8 or similar? Maybe you hit that BUG 1400539 where you can not insert more than the maximal number of possible bytes (i.e. 1333 chars = 3999 Bytes).
Try one of the following workarounds:
- Use the database character set also as the client character set
- Decrease the size of the columns
- Do not use the multibyte character set as the database character set :-(
- use 10g where this is "fixed"
I don't know any fix for 9i or 8i. If you have access to Metalink, see note 241358.1 for detailed information.
I am having the same problem during executing anDid you solve your problem? I've been struggling with the same problem for some time now.
update which tries to update the contents of six
Varchar2(2000) fields, I am using the oracle driver
that shipped with .NET and C#.
I having the same problem when I insert the data to clob cloumn,
Database Oracle : 10g
C# + NHibernate + Oracle10gClient,
who have the good solution?
my contact email: email@example.com.
Hi Cheng, i have the exact same setup as you, same issue, has anyone resolved this ?