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