Stored Procedure Oracle + Clob + Vb.Net

  • Thread starter Thread starter jbolanos
  • Start date Start date
J

jbolanos

I want to call a stored procedure with input parameter of Clob type
from Vb.net, I have had many problems and looks for in google and
locate a solution that Tomas Westhed gave in 2002-05-28. I cosay that
it did was:

Dim tx As OracleTransaction
tx = cn.BeginTransaction()

Dim cmd As New OracleCommand()
cmd = cn.CreateCommand()

cmd.Transaction = tx

cmd.CommandText = "declare xx Clob; begin " & _
"dbms_lob.createtemporary(xx, false, 0); :tempClob := xx;
end;"
cmd.Parameters.Add(New OracleParameter("tempClob",
OracleType.Clob)).Direction = ParameterDirection.Output
Try
cmd.ExecuteNonQuery()

Dim tempLob As OracleLob
tempLob = cmd.Parameters(0).Value

clobstring = <Any XML String>

cmd.Parameters.Clear()
cmd.CommandText = "dbms_lob.write"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("lob_loc",
OracleType.Clob)).Direction = ParameterDirection.Input
cmd.Parameters("lob_loc").Value = tempLob
cmd.Parameters.Add(New OracleParameter("amount",
OracleType.Byte)).Direction = ParameterDirection.Input
cmd.Parameters("amount").Value = clobstring.Length
cmd.Parameters.Add(New OracleParameter("offset",
OracleType.Number)).Direction = ParameterDirection.Input
cmd.Parameters("offset").Value = 1
cmd.Parameters.Add(New OracleParameter("buffer",
OracleType.VarChar)).Direction = ParameterDirection.Input
cmd.Parameters("buffer").Value = clobstring

We then use cmd.Parameters(0).Value as a in-parameter for our stored
procedure.

I have done the same but I do not understand like calling the stored
procedure that is like using cmd.Parameters(0).Value.

I thank for any aid
 
Back
Top