Wednesday, November 5, 2008

Upload Oracle BLOB in Stored Procedure using OracleClient OracleLob

I ran into the 32k limit on OracleClient parameters of stored procedures when trying to upload documents to Oracle using a byte array:


comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType
comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = o.DocumentBlob
comm.ExecuteNonQuery()

Then I found a “fast way” using the Oracle.DataAccess client.


Dim startOffset As Integer = 0
Dim writeBytes As Integer = 0

conn.Open()
Dim blob As New OracleBlob(conn)

blob.BeginChunkWrite()
Do
writeBytes = IIf(startOffset + BUFFER_SIZE > o.DocumentBlob.Length, o.DocumentBlob.Length - startOffset, BUFFER_SIZE)
blob.Write(o.DocumentBlob, startOffset, writeBytes)
startOffset += writeBytes
Loop While startOffset < o.DocumentBlob.Length
blob.EndChunkWrite()

comm = New OracleCommand("ADD_DOCUMENT_PROC", conn)
comm.CommandType = CommandType.StoredProcedure
comm.Parameters.Add("IN_PARENT_ID", OracleDbType.Int32).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleDbType.Varchar2).Value = docType
comm.Parameters.Add("IN_DOC_BLOB", OracleDbType.Blob).Value = blob
comm.ExecuteNonQuery()
It worked locally, but the client is not installed on our servers, so going back to the ADO.Net OracleClient, I found an example in the MSDN docs that is working quite well. It seems the key is to use DBMS_LOB.CREATETEMPORARY:

Private Sub AddDocument(ByVal parentId As Integer, ByVal docType As String, ByVal o As Document)
Dim conn As OracleConnection
Dim comm As OracleCommand

Try

conn = OpenConnection()

Dim tx As OracleTransaction = conn.BeginTransaction()

comm = conn.CreateCommand()
comm.Transaction = tx

' This will create a temp blob object in the DB
comm.CommandText = "declare xx blob; " & _
"begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"

comm.Parameters.Add(New OracleParameter("tempblob", _
OracleType.Blob)).Direction = ParameterDirection.Output
comm.ExecuteNonQuery()

Dim tempLob As OracleLob
tempLob = comm.Parameters(0).Value
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)

' You may be able to chunk this to improve performance?
tempLob.Write(o.DocumentBlob, 0, o.DocumentBlob.Length)
tempLob.EndBatch()

comm.Parameters.Clear()
comm.CommandText = "ADD_DOCUMENT_PROC"
comm.CommandType = CommandType.StoredProcedure

' Add the BLOB object parameter
comm.Parameters.Add("IN_DOC_BLOB", OracleType.Blob).Value = tempLob

comm.Parameters.Add("IN_PARENT_ID", OracleType.Number).Value = parentId
comm.Parameters.Add("IN_DOC_TYPE", OracleType.VarChar).Value = docType

comm.ExecuteNonQuery()

' You want to explicitly destroy the LOB obj
tempLob.Dispose()

tx.Commit()
comm.Dispose()

Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
conn = Nothing
End Try

End Sub

1 comment:

Unknown said...
This comment has been removed by the author.