¤ I am running the following code based on a microsoft article :
¤
http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
¤
¤ Dim conn As New OracleConnection("server=;Uid=;pwd=")
¤ Dim filePath As String
¤
¤ filePath = "C:\temp\test.txt" 'Add the path to the file you want
¤ to insert
¤ If Not File.Exists(filePath) Then
¤ ' handle error
¤ End If
¤
¤ Dim fs As New FileStream(filePath, FileMode.OpenOrCreate,
¤ FileAccess.Read)
¤
¤ '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 clob; begin
¤ dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;"
¤ cmd.Parameters.Add(New OracleParameter("tempclob",
¤ OracleType.Clob)).Direction = ParameterDirection.Output
¤ cmd.ExecuteNonQuery()
¤
¤ Try
¤
¤ 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 = "InsertClob.TestClobInsert"
¤ cmd.CommandType = CommandType.StoredProcedure
¤ cmd.Parameters.Add(New OracleParameter("ClobParam",
¤ OracleType.Clob)).Value = tempLob
¤
¤ cmd.ExecuteNonQuery()
¤ Catch myex As Exception
¤ MsgBox(myex.Message)
¤ End Try
¤ tx.Commit()
¤
¤
¤ my test file contains the following string : abcdefghi
¤ the code runs ok, but I get "¿¿¿¿i" in the database????
I think you're encountering a unicode conversion issue by doing a read/write to the tempLob. If
you're going to use a stored procedure, just do a straight Insert using an Oracle.Clob parameter
type.
Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)