How do i Read Write Oracle CLOB data in ASP.NET or VB.NET ?

Posted by mauliksoni on July 24, 2008 · 12 Comments 

[KEYWORDS: ASP.NET READ WRITE CLOB DATA, VB.NET READ WRITE ORACLE CLOB DATA, ORACLE CLOB, CLOB, ORACLE CLOB READ WRITE]
Oracle has Varchar2 with max length of 4000 chars. If you need to sore more than that you have to use CLOB datatype
CLOB stores 4gb of chars. But remember, .NET Only supports string worth 2gb. so you need to handle the scenario
if you want to store 2+gb string into oracle.

---

Que: How do i Read Write Oracle CLOB data in ASP.NET or VB.NET ?
Ans: It took me 2 days to come up with the simpletes method to read/write ORACLE CLOB data.
And I would like to share that with all you guys.

Step 1: Add a reference – Oracle.Dataaccess.dll ( found in ODP.NET )

Step 2: Imports following namespaces

Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

Step 3: Create a connection string

Public ReadOnly connectionstring = "data source = oradb;user id = rmsoni;password=rmsoni99"</code>

Step 4: Create Following Public Methods

Public Sub ReadLOBData()
Dim con As New OracleConnection(connectionstring)
con.Open()</code></div>
<div><code>Dim sql As String = "select CLOBTEXTFIELD from TestCLOB where ID=1"
Dim cmd As OracleCommand = New OracleCommand(sql, con)
Dim dr As OracleDataReader = cmd.ExecuteReader()
dr.Read()
Dim blob As OracleClob = dr.GetOracleClob(0)</code></div>
<div><code>txtOutput.Text = blob.Value()</code></div>
<code>blob.Close()
dr.Close()
con.Close()
End Sub

Complete Source Code –

Public Sub WriteLOBData()
Dim connection As New OracleConnection(connectionstring)
connection.Open()

Dim strSQL As String = "INSERT INTO TestCLOB (ID,CLOBTEXTFIELD) VALUES (1,:TEXT_DATA) "
'Dim strsql As String = "UPDATE TestCLOB SET CLOBTEXTFIELD=:TEXTDATA where testid=1"
Dim paramData As New OracleParameter
paramData.Direction = ParameterDirection.Input
paramData.OracleDbType = OracleDbType.Clob
paramData.ParameterName = "TEXT_DATA"
paramData.Value = txtInput.Text

Dim cmd As New OracleCommand
cmd.Connection = connection
cmd.Parameters.Add(paramData)
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

paramData = Nothing
cmd = Nothing
connection.Close()
End Sub

That’s all you need for CLOB.
Enjoy.

Filed under .NET · Tagged with ASP.NET CLOB, Oracle