This step-by-step article describes how to use the .NET
Managed Provider for Oracle to pass
OracleType.Blob as an
Input parameter to an Oracle stored procedure.
Back to the top
Requirements
The following list outlines the recommended hardware, software,
network infrastructure, and service packs that are required:
| • | Microsoft Windows 2000 Professional, Microsoft Windows 2000
Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows XP
Professional |
| • | Microsoft Visual Studio .NET |
| • | Oracle Client 8.1.7 or later |
| • | .NET Managed Provider for Oracle |
NOTE: To download .NET Managed Provider for Oracle, visit the
following Microsoft Web site:
This article assumes that you are familiar with the following
topics:
| • | Microsoft Visual Basic .NET |
| • | Microsoft ADO.NET fundamentals and syntax |
Back to the top
Create the Oracle tables
This sample uses tables that are defined in the Oracle
Scott/Tiger schema. By default, the Oracle Scott/Tiger schema is included with
a standard Oracle installation.
If this schema does not exist, you
must run the following table script for the tables:
Create Table Blobtable(MyID Number, Blobdata Blob)
Back to the top
Create the Oracle packages
Create the following Oracle package on the Oracle server:
CREATE OR REPLACE package InsertBlob
as
PROCEDURE TestBlobInsert (BlobParam in blob);
end InsertBlob;
Create the following Oracle package body on the Oracle server:
CREATE OR REPLACE package body InsertBlob
as
PROCEDURE TestBlobInsert (BlobParam in blob)
as
begin
INSERT INTO blobtable (myid,blobdata) values(1,BlobParam);
end TestBlobInsert;
end InsertBlob;
Back to the top
Create the Visual Basic .NET application
| 1. | Follow these steps to create a new Visual Basic Windows
Application project:
| a. | Start Microsoft Visual Studio .NET. | | b. | On the File menu, point to New, and then click Project. | | c. | Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project. |
|
| 2. | On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace. |
| 3. | Drag a Button control from the toolbox to the form. |
| 4. | Add the following code at the top of the Code window:
Imports System.Data.OracleClient
Imports System.IO
|
| 5. | Add the following code to the Button1_Click event of Form1:
Dim conn As New OracleConnection("server=Oracle;Uid=uid;pwd=pwd")
Dim filePath As String
Dim bigData As Byte()
Dim t As Date
t = Now
filePath = "C:\mytest.bmp" 'Add the path to the file you want to insert
If Not File.Exists(filePath) Then
' handle error
End If
Dim fs As Stream = _
File.OpenRead(filePath)
Dim tempBuff(fs.Length) As Byte
fs.Read(tempBuff, 0, fs.Length)
fs.Close()
conn.Open()
Dim tx As OracleTransaction
tx = conn.BeginTransaction()
Dim cmd As New OracleCommand()
cmd = conn.CreateCommand()
cmd.Transaction = tx
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
cmd.Parameters.Add(New OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim tempLob As OracleLob
tempLob = cmd.Parameters(0).Value
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
tempLob.Write(tempBuff, 0, tempBuff.Length)
tempLob.EndBatch()
cmd.Parameters.Clear()
cmd.CommandText = "InsertBlob.TestBlobInsert"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("BlobParam", OracleType.Blob)).Value = tempLob
Try
cmd.ExecuteNonQuery()
Catch myex As Exception
MsgBox(myex.Message)
End Try
tx.Commit()
|
| 6. | Modify the OracleConnection string as appropriate for your environment. |
| 7. | Change the "Filepath" variable to point to the file that
you want to insert into the database. |
| 8. | 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
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 (http://kbalertz.com/Feedback.aspx?kbNumber=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 (http://kbalertz.com/Feedback.aspx?kbNumber=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