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

Back to the top

Requirements

• 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

• Microsoft Visual Basic .NET
• Microsoft ADO.NET fundamentals and syntax

Back to the top

Create the Oracle tables

Create Table Blobtable(MyID Number, Blobdata Blob)
				

Back to the top

Create the Oracle packages

CREATE OR REPLACE package InsertBlob
as
PROCEDURE TestBlobInsert (BlobParam in blob);
end InsertBlob;
				

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:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. 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

"declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
				

Back to the top