Insert CLOB into Oracle using ADO.NET

[Jeff Washburn]

Hi All,

I have the following code. It is pretty simple and doesn't work. All
I am trying to do is open a file and put the data into an oracle clob
using ADO.NET and the Microsoft Oracle Data Provider for .NET.

Can anyone see what is wrong, or have a simple sample?

Thanks!!
Jeff

strSQL = "INSERT INTO VENDOR_TRANSACTIONS (ORDER_DATA) "
strSQL &= "VALUES ORDER_DATA) "

Dim fs As New IO.FileStream(fiTemp.FullName,
IO.FileMode.Open, IO.FileAccess.Read)
Dim tempBuff(fs.Length) As Byte
fs.Read(tempBuff, 0, fs.Length)
fs.Close()

Dim parmData As New OracleParameter
With parmData
.Direction = ParameterDirection.Input
.OracleType = OracleType.Clob
.ParameterName = "ORDER_DATA"
.Value = tempBuff
End With

cm.Parameters.Add(parmData)
cm.CommandText = strSQL
cm.ExecuteNonQuery()

 

[Frans Bouma [C# MVP]]

Hi All,

I have the following code. It is pretty simple and doesn't work. All
I am trying to do is open a file and put the data into an oracle clob
using ADO.NET and the Microsoft Oracle Data Provider for .NET.

Can anyone see what is wrong, or have a simple sample?

Thanks!!
Jeff

strSQL = "INSERT INTO VENDOR_TRANSACTIONS (ORDER_DATA) "
strSQL &= "VALUES ORDER_DATA) "

Dim fs As New IO.FileStream(fiTemp.FullName,
IO.FileMode.Open, IO.FileAccess.Read)
Dim tempBuff(fs.Length) As Byte
fs.Read(tempBuff, 0, fs.Length)
fs.Close()

Dim parmData As New OracleParameter
With parmData
.Direction = ParameterDirection.Input
.OracleType = OracleType.Clob
.ParameterName = "ORDER_DATA"
.Value = tempBuff
End With

cm.Parameters.Add(parmData)
cm.CommandText = strSQL
cm.ExecuteNonQuery()

CLOB's are character lob's. You therefore need to send a string. Use a
StreamReader to read the file's text, read it into a string variable and pass
that to the .Value property of the parameter. Also set the length of the
CLOB, if you get errors that the string is too long (however this should be
done by default).

It's often wise to mention which error you get, now we can only guess what's
wrong.

Frans.

 

[Mohamed Sharaf]

Hi Jeff,
In addition to France please note that When working with LOB data, Oracle
requires that you first check to see if the LOB
column is Null. If so, then you must first either insert dummy data into to
the
column or use the empty_blob() or empty_clob() function instead. You cannot
write

to a LOB column that has not been initialized in one of these ways.
I hope that helps

Mohamed Sharaf
MEA Developer Support Center
ITWorx on behalf Microsoft EMEA GTSC

 

[Miha Markic [MVP C#]]

Hi Jeff,
In addition to *France* please note that

hehe

 

[Frans Bouma [C# MVP]]

Hi Jeff,
In addition to France please note that When working with LOB data, Oracle

FranS, not the country

requires that you first check to see if the LOB
column is Null. If so, then you must first either insert dummy data into to
the
column or use the empty_blob() or empty_clob() function instead. You cannot
write
to a LOB column that has not been initialized in one of these ways.
I hope that helps

I think this is only true for older oracle db's, as you can insert NULL in a
CLOB field in new row without problems on 9i/10g.

Frans.

 

[Jeff Washburn]

Thanks all for your help. Here is the revised and working code in
case anyone else want it:

strSQL = "INSERT INTO VENDOR_TRANSACTIONS (ORDER_DATA) "
strSQL &= "VALUES ORDER_DATA) "

Dim sr As New IO.StreamReader(fiTemp.FullName)
Dim tempBuff As String = sr.ReadToEnd
sr.Close()

Dim parmData As New OracleParameter
With parmData
.Direction = ParameterDirection.Input
.OracleType = OracleType.Clob
.ParameterName = "ORDER_DATA"
.Value = tempBuff
End With

cm.Parameters.Add(parmData)
cm.CommandText = strSQL
cm.ExecuteNonQuery()

 

[parvizb]

u can use Oracle.ManagedDataAccess.Types.OracleClob as a variable ex:
Oracle.ManagedDataAccess.Types.OracleClob oracleClob = new Oracle.ManagedDataAccess.Types.OracleClob(cn);
oracleClob.Write(rtfText.ToCharArray(), 0, rtfText.Length);
OracleParameter text = cmd.Parameters.Add(“TEXT”, OracleDbType.Clob);
text.Value = oracleClob;