Convert oracle clob parameter to string freezes in asp.net

Question

Please find this code. It works correctly on my local machine. It was copied to a Windows 2008 server (64 bit). He worked perfectly for many days. But now it hangs and takes 20 minutes. The same code runs fast on my machine. If I convert clob to varchar, it will work, but it will not support more than 32 K. I updated the oracle client, now it also hangs.

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 

Answer 1

I use Oracle Clob and it is very simple and there is no need to do type conversion, just assume this is a string in asp.net, and here is an example:

Dim sql = "select xmlagg (XMLElement (" user ", xmlattributes (user_id" user_id "))). GetClobVal () from users" is just an example of returning the value of clob

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 the data variable you have the result