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

[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.

About these ads
Comments
12 Responses to “How do i Read Write Oracle CLOB data in ASP.NET or VB.NET ?”
  1. MAulik Soni says:

    [KEYWORDS: ASP.NET READ WRITE CLOB DATA, VB.NET READ WRITE ORACLE CLOB DATA, ORACLE CLOB, CLOB, ORACLE CLOB READ WRITE, ODP.NET, ORACLE AND .NET]
    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.

  2. 4xThank’s for greate post.6h I compleatly agree with last post. zwk
    паркет и ламинат 7h

  3. Ohad says:

    8*tnx for the info
    saved me a lot of hours .
    good and simple.

  4. Actually I am getting an error with that code

  5. Imran says:

    Good to know that I’m not the only one who is getting the error with this code.
    The error is on the following line:
    Dim blob As OracleClob = dr.GetOracleClob(0)
    and the error message is:
    Specified cast is not valid

  6. kuragari says:

    Hi!

    I’m developing in Visual C#2005, Crystal Reports 2008 and Oracle 10g.

    I’m trying to put a Blob Field (img) in a report and, it works perfectly if I execute it in the Crystal Report Environment but, when I try to execute it in the ASP interface it crashes.

    I linked the report with the DDBB using OLEDB driver because Oralce’s driver crashes on he server…

    Can you help me?

  7. Filippo says:

    Hi with this code I’m getting an error as : Invalid parameter association- Parameter name TEXT_DATA
    Thx

  8. Lily says:

    Cool. Thanks a lot!

  9. mauliksoni says:

    I have take some time to properly format the code. Have fun.

  10. Aleks says:

    Thank you! You are great man

Trackbacks
Check out what others are saying...


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: