clob into oracle

[ewen mcdonald]

Can any one give me an example of using the microsoft .net
managed provider for oracle to insert a clob into the
database.
We have a stored procedure which takes a clob as a
parameter, but are struggling to populate a clob type
parameter with the text we want to save.

 

[Paul Clement]

¤ Can any one give me an example of using the microsoft .net
¤ managed provider for oracle to insert a clob into the
¤ database.
¤ We have a stored procedure which takes a clob as a
¤ parameter, but are struggling to populate a clob type
¤ parameter with the text we want to save.

What kind of problems are you having? Keep in mind that Oracle stored procedure parameters are
limited to 32k.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Ewen McDonald]

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????

 

[David Browne]

Can any one give me an example of using the microsoft .net
managed provider for oracle to insert a clob into the
database.
We have a stored procedure which takes a clob as a
parameter, but are struggling to populate a clob type
parameter with the text we want to save.

Oracle has an example using ODP.NET:

http://otn.oracle.com/sample_code/tech/windows/odpnet/updatelob/UpdLob.html

And in general I would recommend using ODP.NET over the Microsoft managed
provider.
It's faster, better supported and exposes much more Oracle functionality.

David

 

[Paul Clement]

¤ 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)