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()
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:
Post a Comment