Get Help Now
1. Dim conn As New OracleConnection("server=Oracle;Uid=uid;pwd=pwd
Contact a support professional by E-mail, Online, or Phone
") 2.
Dim filePath As String
3.
Dim bigData As Byte()
4.
Dim t As Date
Article Translations
5. 6.
t = Now
7. 8.
filePath = "C:\mytest.bmp" 'Add the path to the file you want to insert
9. 10.
If Not File.Exists(filePath) Then
11.
' handle error End If
12.
Search related topics
• • • •
13. 14.
Dim fs As Stream = _
15. 16.
File.OpenRead(filePath)
•
System.InvalidOperationException Windows XP system requirements Microsoft Visual Studio 2005 download System Path environment variable Run cmd script
Dim tempBuff(fs.Length) As Byte
17. 18.
fs.Read(tempBuff, 0, fs.Length)
19.
fs.Close()
20.
conn.Open()
21. 22.
Dim tx As OracleTransaction
23.
tx = conn.BeginTransaction()
24. 25.
Dim cmd As New OracleCommand()
26.
cmd = conn.CreateCommand()
27. 28.
cmd.Transaction = tx
29. 30.
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
31.
cmd.Parameters.Add(New
Related Support Centers
•
Visual Basic .NET 2002
• Visual Basic .NET 2003 Page Tools •
Print this page
•
E-mail this page
OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output 32.
cmd.ExecuteNonQuery()
33. 34. 35. 36.
Dim tempLob As OracleLob
37.
tempLob = cmd.Parameters(0).Value
38.
tempLob.BeginBatch(OracleLobOpenMode.R eadWrite)
39.
tempLob.Write(tempBuff, 0, tempBuff.Length)
40.
tempLob.EndBatch()
41. 42. 43. 44.
cmd.Parameters.Clear()
45.
cmd.CommandText = "InsertBlob.TestBlobInsert"
46.
cmd.CommandType = CommandType.StoredProcedure
47.
cmd.Parameters.Add(New OracleParameter("BlobParam", OracleType.Blob)).Value = tempLob
48.
Try
49.
cmd.ExecuteNonQuery()
50.
Catch myex As Exception
51.
MsgBox(myex.Message)
52.
End Try
53.
tx.Commit()
54.
55. Modify the OracleConnection string as appropriate for your environment. 56. Change the "Filepath" variable to point to the file that you want to insert into the database.
57. Press F5 to compile and to run the application.
Back to the top
Additional information If the column in the table is specified as a "Blob" type, as it is in this scenario, Oracle only accepts a Blob type parameter. You cannot create a Binary Large Object (BLOB) from a byte array. Therefore, the code sample contains the following code:
"declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;" This code has Oracle create a BLOB and pass the BLOB back as a parameter to the command object. The command object is then assigned to the "TempLob" variable, which is declared as OracleLob.
The OracleTransaction is required by Oracle when modifying any LOB. If you do not have a transaction, you receive the following exception: An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.oracleclient.dll
Additional information: Modifying a LOB requires that the connection be transacted. Back to the top REFERENCES
For more information about a similar topic using Visual Basic 6.0, click the following article number to view the article in the Microsoft Knowledge Base: 185958 How to use ADO GetChunk/AppendChunk with Oracle for BLOB data For more information, click the following article number to view the article in the Microsoft Knowledge Base: 321718 How to call Oracle stored procedures in Visual Basic .NET with the Microsoft Oracle Managed Provider The third-party products that this article discusses are manufactured by
companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products. Back to the top
APPLIES TO
•
Microsoft ADO.NET (included with the .NET Framework)
•
Microsoft ADO.NET 1.1
•
Microsoft Visual Basic .NET 2002 Standard Edition
•
Microsoft Visual Basic .NET 2003 Standard Edition
Back to the top
Keywords: kbhowtomaster kboracle kbsystemdata KB322796 Back to the top
Provide feedback on this information Did this information solve your problem? Yes No I don't know Was this information relevant? Yes No What can we do to improve this information?
To protect your privacy, do not include contact information in your feedback.
Get Help Now Contact a support professional by E-mail, Online, or Phone
Help and Support Feedback | Services Agreement Contact Us | Terms of Use | Trademarks | Privacy Statement
©2009 Microsoft