4

Please find this code. It is properly working on my local machine. It was copied to windows server 2008 (64bit). It was working fine for many days. But now, it is hanging and taking 20 minutes. Same code is working in my machine fast. If I convert clob to varchar, it will work, but it will not support more than 32 K. I updated oracle client, now also it is hanging.

    Dim cn As New OracleConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
    Dim cmd As New OracleCommand
    cn.Open()


    cmd.Connection = cn
    cmd.CommandType = Data.CommandType.StoredProcedure

        cmd.CommandText = "Inet_Pkg_Menu.TopMenu"


    cmd.Parameters.Add("pBrCode", OracleDbType.Int32).Direction = Data.ParameterDirection.Input
    cmd.Parameters.Add("pRes", OracleDbType.Clob).Direction = Data.ParameterDirection.Output

    cmd.Parameters(0).Value = Session("user_code")

    cmd.ExecuteNonQuery()

    Dim s As String
    Dim olob As OracleClob


    olob = CType(cmd.Parameters("pRes").Value, OracleClob)

        s = System.Convert.ToString(olob.Value) 'Hanged line
Dave Clemmer
  • 3,741
  • 12
  • 49
  • 72
user1676704
  • 41
  • 2
  • 1
    Looks similar to this question,please check: http://stackoverflow.com/questions/8838115/poor-performance-getting-clob-field-from-oracle-in-net – Pratik Mar 11 '13 at 17:14
  • What is the size of your clob? Did you also try to enable odp tarcing (http://docs.oracle.com/html/E10927_01/featConfig.htm)? – evgenyl Apr 14 '13 at 05:27

1 Answers1

0

I'm using Oracle Clob and it is very easy and there is no need to make type conversion, just suppose it as a string in asp.net, and here is an example:

Dim sql = "select xmlagg(XMLElement("user",xmlattributes(user_id "user_id"))).getClobVal() from users" 'this is just example of returning clob value

Dim adp As New Data.OleDb.OleDbDataAdapter(sql, MyConnectionString)

Dim dt As New DataTable

adp.Fill(dt)

Dim data As String = dt.Rows[0][0].toString()

and now in data variable you have the result

Firas Nizam
  • 1,392
  • 12
  • 7