Microsoft KB Archive/322796

= How to pass a BLOB as a parameter to an Oracle package by using the .NET Managed Provider for Oracle =

Article ID: 322796

Article Last Modified on 10/4/2005

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 1.1
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q322796





SUMMARY
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.

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:

.NET Managed Provider for Oracle

http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/940/msdncompositedoc.xml

This article assumes that you are familiar with the following topics:
 * Microsoft Visual Basic .NET
 * Microsoft ADO.NET fundamentals and syntax

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)

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;

Create the Visual Basic .NET application
 Follow these steps to create a new Visual Basic Windows Application project:  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.  On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace. Drag a Button control from the toolbox to the form.</li>  Add the following code at the top of the Code window: Imports System.Data.OracleClient Imports System.IO                   </li>  Add the following code to the Button1_Click event of Form1: Dim conn As New OracleConnection(&quot;server=Oracle;Uid=uid;pwd=pwd&quot;) Dim filePath As String Dim bigData As Byte Dim t As Date

t = Now

filePath = &quot;C:\mytest.bmp&quot; '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 = &quot;declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;&quot; cmd.Parameters.Add(New OracleParameter(&quot;tempblob&quot;, 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 = &quot;InsertBlob.TestBlobInsert&quot; cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add(New OracleParameter(&quot;BlobParam&quot;, OracleType.Blob)).Value = tempLob Try cmd.ExecuteNonQuery Catch myex As Exception MsgBox(myex.Message) End Try tx.Commit </li> Modify the OracleConnection string as appropriate for your environment.</li> Change the &quot;Filepath&quot; variable to point to the file that you want to insert into the database.</li> Press F5 to compile and to run the application.</li></ol>

Additional information
If the column in the table is specified as a &quot;Blob&quot; 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: &quot;declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;&quot; 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 &quot;TempLob&quot; 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.

<div class="references_section">