Insert CLOB into Oracle using ADO.NET

  • Thread starter Thread starter Jeff Washburn
  • Start date Start date
J

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()
 
Jeff said:
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()
Click to expand...

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.
 
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
 
Mohamed Sharaf said:
Hi Jeff,
In addition to France please note that When working with LOB data, Oracle
Click to expand...

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
Click to expand...

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.
 
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()
 
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;
 
Back
Top